October 24, 2003 at 7:15 am
Hi There,
We have an ASP application that has one function that heavily calls a large number of sql procs.
The kickoff is here:
<%'get account that has an active status
SQLAllRCs = "Execute SP_GetAccountNumbersForCalc"
Set objCalcAllRCs = Database.ExecuteQuery(SQLAllRCs)
if not objCalcAllRCs.EOF then
do until objCalcAllRCs.EOF
'this sets the Account number for the other stored procedures and
Session("intMasterAcctID") = objCalcAllRCs("intMasterAcctID")
Server.Execute("CalculateOneRC.asp")
objCalcAllRCs.MoveNext'to the next record in the recordset
loop
end If
%>
CalculateOneRC goes thru all of our relevant tables and recalculates various parts of our system. Normally for 1 record it takes anywhere from 3-5seconds to process. It goes thru maybe 30 procs, depending on the record.
Now the kicker seems to be that performance degrades over time. On a system with 17500 records (2GB database running on a dual 1.4ghz 512mb machine, Win2003) the first 2000 records process in 42minutes. 6000 takes 4hrs,20min, and I started a full run on the 17500 last night a 4pm and its processed maybe 11000 (9am).
Besides the simple answer of 'this method sucks' can anyone shed any ideas on what might be causing the performance degrade. The physical machine itself is hardly breaking a sweat processing.
I'm not sure if its a T-SQL issue, a SQL server issue, something with IIS, etc.
Thanks,
Chris
October 24, 2003 at 8:32 am
It *could* be that
"CalculateOneRC.asp" is not properly cleaning up after itself (i.e. destroying the objects it creates) ... I notice that in the code you've posted for the outer loop that the Recordset object is not explictly closed or set to Nothing. Creating loads of database connections and not closing them down will hurt you particularly bad.
This is just one possible cause of a gradual decline in performance. You might be able to see this happening live in Task Manager -- look if a process is gobbling up memory over time.
October 24, 2003 at 10:44 am
I would also redesig the process to run both steps in only one procedure (if it is feasible) it will cut down the number of round trips to the server
* Noel
October 24, 2003 at 11:58 am
alot of the processing in calculateonerc.asp is dependant upon conditional scripts. Its seemingly easier to do a loop thru records in vbscript than to do cursors within SQL (possibly)
I actually did a full count thru the profiler, of all of the TSQL/Procs that are called. 90... of which 38 actually generate a duration >0ms
the objects are being closed/set = nothing within the pages.
I did a few runthrus and its about 2750ms(avg) to go thru all of the scripting.
Working on a redesign to recalculate records as they become dirty (dirty in reference to our product) which should take the pain away until they have to do the entire system.
Just need to do a code review on the 38 and see where time can be shaved.
Thanks,
Chris
Edited by - ctklein on 10/24/2003 12:01:36 PM
October 27, 2003 at 9:34 am
I have seen performance degradation like this before in data intensive processes. 2 tecniques I would recommend: 1) either at the beginning or end of the process, reindex relevant tables and recompute statistics. 2) after the reindex, call sp_recompile on the relevant tables. This will ensure that all sprocs that reference them get recompiled on next run, which will cause the optimized to recompute the ptimal query plan.
The reason all of this works is that for data intensive operations like this, the query plans are dependent on the distribution of the data. Over time some indexess may become less selective, and others more selective... however stored proicedures retain the original execution plan, which man no longer be optimal.
Good luck.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply