kevin_nikolai (11/25/2014)
--Step 2:/*
With 2 cursors in 2nd proc (original), if I select 9 campuses, query finishes in 4 minutes 10 seconds.
After removal of 2 cursors from 2nd proc (step 1 and step 2 was 50% of it), if I select 9 campuses, query finishes in 3 minutes 47 seconds.
After replacement of while loop with cursor in 1st proc (which calls 2nd proc), query finishes in 3 minutes 43 seconds.
So from 250 minutes to 223 minutes = 27 minutes reduced (i.e. query runs now 11% faster).
So after all the effort, no real gain.
Will see if I can incorporate 2nd proc into 1st proc, compare duration.
*/
11% as a first attempt is not bad. Performance tuning usually involves many incremental steps.
What determines the campuses?? This isn't referred to in any of the documentation provided. Is it the ID?
Look up Jeff Moden's splitter function.
If I understand what you need to do, there may be a whole list of ID's required. So, using this code: AND ID IN (1,2,3,4) , you would replace it with AND EXISTS(SELECT X.ID FROM DelimitedSplit8K('1, 2, 3, 4', ',') X WHERE X.id = StudentUID)
That makes multiple selects into a single select, assuming that is what you need to do.
The function ADV_rptage is kind of over kill. Do a cross apply
CROSS APPLY CROSS APPLY [ADV_rptage](PaymentDueDate,@AgingDate) as Dates
The select would then be:
Dates.YEARS,
Dates.Months,
And so forth.
Is "Select Distinct" causing a table scan? Did you look at the execution plan? You populated the table #part1 by using the DISTINCT, the rest of the queries do not need them.
This is probably not very efficient
UPDATE #Part1
SET @PaymentDueDate = PaymentDueDate WHERE ID = 1
What about:
SELECT @PaymentDueDate = PaymentDueDate FROM #Part1 WHERE ID = 1
This may not make much of a difference, but it looks better!
Lastly, you really do not need temp tables.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/