Christopher L Bobbitt 13.Sep.05 11:03 AM a Web browser LC LSX LC LSX - All Releases; LS:DO - All Releases Windows 2003
For the last 7 years we've been developing a large Lotusscript Domino application that is mainly designed to provide web-based access to a SQL Server 2000 database. While we've pondered migrating it to J2EE, doing so fails every cost/benefit analysis - the application is huge, and generally both reliable and feature-rich.
Regardless, as you can imagine from something so old, we use LS:DO to access the RDBMS (because that's all we had for a long time). A few years ago we experimented with using ADO directly, but found that performance was actually worse.
Recently, as another test, we tried converting one agent from LS:DO to LC LSX based on advice posted here and elsewhere in Lotusland. Anyway, for an agent that is designed to retrieve and tabular format data from a joined SQL query against a SQL Server 2000 database, we obtained the following results:
75 Records - .3 seconds
2847 Records - 6.97 seconds
LC LSX -
75 Records - .3 seconds
2847 Records - 7.53 seconds
The small sample was actually a baseline, which demonstrates the overhead associated with setting up a connection and going through each record. The larger sample is more representative of a typical query.
With connection pooling turned on, the results of the 2847 record retrieval were 7.56 seconds for LC LSX. Note that the server we were testing this on is a production machine, and is typically running at about 20% utilization. Also, please note that we read everything we could find about optimizing performance of LC LSX. The code seems to be sound, but one bit (which is in the main loop that retrieves data from individual records) seems a bit clunky:
Set field = records.GetField(1)
myvalue = field.Value(0)
So... my questions are:
1. Am I misreading these results.. is LC LSX actually slower in some circumstances? Would it have an advantage if a lot of people were hitting the system at the same time? Doesn't the ODBC driver referenced by LS:DO automatically do connection pooling anyway?
2. Is there a more efficient way to code retrieval of data from individual fields? We have been spoiled by the resultset GetValue method in LS:DO.
3. Is there a native connector for SQL Server 2000 under LC LSX? Perhaps the problem is that we are using the "sqloledb" provider?
4. Is there a good way to get the count of records in a result set? With LS:DO, you can just to the last record and then return the number of records using the CurrentRow method. Is there anything comparable with LC LSX?