| |
|
|
Maximizing Performance with Integra for Notes
The performance of exports or imports of data to and from Lotus Notes to Word, Excel, Text and XML files will vary depending on a number of factors including:
- the method used for exporting data (see Advanced Tab of an Integra Excel profile) - the Normal option is the fastest while the Advanced COM option offers the most functionality and the XML option is only available for exports to Excel XP)
- whether data is being exported using a Selection Formula in the Integra profile rather than the Filtering capabilities newly available in Integra for Notes 4.1. The filtering capabilities which leverage the full-text index of a database offer much better performance.
- whether the data to be exported is already available in a view or folder rather than having to select/filter the data to meet your data requirements
- the options utilized in the Integra profile
- usage of Word Mailmerge in conjunction with the activate option - profiles which run an export to Word using Integra's Mailmerge option to create a report (collecting all the exported Notes documents into one Word document with multiple pages, then this can expected to be a slow process in particular when the resulting Word document is growing to a size that is not very well managed by Word itself. e.g. creating an Integra profile which uses a Word Mailmerge and is set to Activate Word on launch will operate much slower than when just printing the Mailmerge documents.
- the performance of the network and connectivity between your workstation and the Integra database as well as the main database you are exporting/importing to or from.
- whether the data is being accessed on the same server as that on which the Integra database is stored
- whether data is being accessed from multiple databases
- whether any/many look-ups to other views are included in the Integra profile and the settings of these look-ups
- whether look-ups to 3rd party databases have been implemented using the ODBC version of the @DbLookup function or using the LSX Domino Connector for the appropriate database (e.g. Oracle, MS-SQL, DB2 etc).
- the general intensity of any processing being done in the Integra profile, particularly in the Advanced Callback options.
- long formulas and advanced script as these have to be evaluated or compiled at runtime numerous times.
Below you will find some tips regarding the use of Export Method and the efficiency of the @DbLookup function.
The Export Method option
 The Export Method option is only available in Integra for Notes 4.1 and is relevant for Integra profiles which export to Excel. No equivalent option is available currently for importing or for Word or Text profiles.
The option is found in the Advanced Options sub-tab of the Advanced Tab of an Excel profile. This option has 3 settings:
- Normal - this method uses a temporary CSV file prior to getting the data into Excel.
- Advanced COM - this method uses the Microsoft COM technology to write data directly into the Excel spreadsheet
- XML - this method uses XML to write data into Excel XP. This option is only available to users of Excel XP.
The fastest method is Normal and this is the default setting for profiles created with Integra for Notes 4.1. However, older profiles created with earlier releases would use the Advanced COM method which is significantly slower than the new Normal method. However the functionality available to the profile creator when using the Normal method is less than that offered by the Advanced COM method. These limitations are:
- Field Sequencing - The sequence of fields as listed in the Selected Field List of the Integra profile has to match the sequence of columns in the Excel template attached to the Integra profile.
- Some Events & Macros - Because data is exported to a temporary CSV file and then, only when the export is completed, converted to Excel, some of the options available in the Advanced Callback which manipulates the data in Excel line by line will not be available. This is particularly an issue with the use of the CB_AFTERWRITECOM script callback event as well as the facility available in Integra to execute an Excel macro before or after exporting a particular Notes document in the respective events.
So, if you have a Excel export where you do not intend using the CB_AfterWriteCom event or where the field sequence of the profile can be set to match the columns of the Excel template, then we would recommend utilization of the Normal method. Integra Export to Excel Profiles created in versions prior to Integra 4.1 can be modified to use this option event retrospectively following which they should be tested.
The Efficiency of @DbLookups
A major benefit of Integra for Notes is that it allows data to be collected from different views, even across different databases which could be on the same or a different server. This can be achieved both from Notes databases as well as 3rd party databases such as Oracle, MS-SQL, DB2 etc. (More info FAQ: How Can I integrate Notes data with non-Notes databases). This can be achieved either using the Notes formula language and the @DbLookup command or the Advanced Script Callback options which allow execution of LotusScript and thus the use of the LSX Connectors for 3rd party.
While the @DbLookup function can be optimised using the Cache and NoCache options, a profile which uses multiple look-ups can also be intelligently implemented by reducing the number of formula look-ups. Let us take as an example a profile which uses 3 look-ups, it is possible to have only the first of the three lookup formula fields actually having a lookup formula. The returned column could contain a multi- value containing the values for all three formula fields in one go. Advanced script can then be used to split up the returned multi value to its respective output columns/fields. This way you can reduce the number look-ups per exported document from three down to a single lookup. That should again provide a significant performance improvement.
Using the CustomCode script library:
Integra allows a profile developer to place almost any Lotus Script into the Script Callback field in the Advanced tab of the Integra profile. However, there are a number of reasons where the use of the CustomCode script library provided by Integra is the better choice or the only choice respectively.
If any advanced script code placed into the Callback Script field (Advanced tab) that exceeds a certain size, this might for ones create a runtime error because of limitations of the Notes runtime compiler, but more importantly any code needs time to be compiled. At runtime Intega creates several events of which some are created per exported document. Everytime an event is executed, the advanced script code needs to be compiled. It is easy to see that this can cause performance problems.
Please refer to the following link to learn how to move your advanced script into the Integra CustomCode script library:
How can I use Integra's CustomCode script library?
Another reason why the use of the CustomCode library could be required is if (for performance reasons) used ODBC lookup formula is to be replaced by script using the ODBC LSX or any of the native drivers for external datasources, such as the Oracle LSX. Please refer to the following FAQ for more details:
How can I integrate Notes data with non-Notes databases? |
. |
|
FAQ# 0081
|
|
|
| |