DBLOOKUP how to increase performance on large result set
- This topic has 1 reply, 2 voices, and was last updated 5 years, 3 months ago by
.
-
Topic
-
We recently rewrote a stored procedure called through a Cloverleaf DBLOOKUP and increased its performance. Now, when using SQL Server Management Studio to run this stored procedure, it takes about 2 seconds to complete, when processing 90 days worth of data, instead of the previous time of over 1 minute.
When calling the same stored procedure using a DBLOOKUP for the same 90-day window, it takes about 6 minutes to run and return the result set. I’m guessing that the part taking the longest is the JDBC function that gathers the results and passes them back to the caller, not the execution of the stor proc itself. But I don’t really know what is going on under the covers.
Here is a small chart of various window sizes and completion times (including the results returned):
window (days) / rows returned / elapsed time
10 / 1822 / 8 seconds
20 / 3515 / 21 seconds
30 / 5260 / 44 seconds
40 / 6928 / 76 seconds
50 / 8572 / 112 seconds
60 / 10324 / 165 seconds
So it seems that efficiency is increasing with larger result sets (when you divide the rows returned by the number of seconds it took to get them).
We changed the -maxrow setting from no value to 20000, thinking that better sizing of the space allocation would increase efficiency, and maybe it did, but I was looking for something better than 100 times slower than the SSMS tool.
This is the call:
set result [dblookup -maxrow 20000 dbl_prPRL_ReconciliationReport_Gottlieb “$daysback” ]
Each row can have up to 300 bytes of data in 12 columns.
Is there anything we can do to increase the performance of the call and time for the TCL to receive the results from the call?
Peter
Peter Heggie
- You must be logged in to reply to this topic.
