Need advice about using sql queries in lotusscript

HCL Domino Domino Forum
1

Hi

I have an application with an lotusscript agent that has to retrieve data from an sql database.

I wonder which option is faster. Doing 1 sql select that has a resultset of about 37,861 records, and then read that resultset in Lotusscript? Or doing a seperat selects for each key( about 1962). Which option has the best performance?

Kind Regards

2

Subject: Need advice about using sql queries in lotusscript

I have a very similar situation. I was doing individual ODBC calls, about 6,000 per night, along with other processing per record. My agent took 3 to 4 hours to run per night. I changed the architecture to make ODBC calls for large amounts of similar data, i.e. Insurance Records, Dependents Records, and Beneficiay Records. I went from thousands of ODBC calls to 5 ODBC calls. My processing time went from hours to 30 minutes!

Hope this helps,

Jeff

3

Subject: Need advice about using sql queries in lotusscript

Without being able to offer any evidence, I believe that reducing the number of hits to the database would lead to better performance. The actual record retrieval for a forward read through the resulting record set should be pretty quick in LotusScript.

4

Subject: Need advice about using sql queries in lotusscript

I have heavy experience with what your doing.

Running a single SQL statement(32k records) is signficantly faster(at least 10 fold) than running 2000 seperate queries. Running any database query is signficantly slower than running LotusScript processing code against a large result set.