SQL Server Performance Forum – Threads Archive

Extremely Complex Cursor (For Me)

Hi there everybody, I have been given a task to optimize a 3000 odd line Stored Proc made up almost entirely of cursors. This proc runs for approximately 17 hours before it completes. Unfortunately, the script has cursors nested within cursors, and once again nested within another cursor. I’ve been reading an article posted by David VanDeSompele to try and sort out this nested cursor thing, however I’m struggling to apply what he says about nested cursors to my situation.
http://www.sql-server-performance.com/dp_no_cursors.asp# Can anyone give me pointers on how to utilize temp tables instead of using cursors, as the inner most of the first 3 cursors creates it’s data from the Second Cursors data lines, which in-turn created it’s data from the first cursors’s data lines? One thing to note is that the first two cursors’ data stays constant, and the 3rd cursors’s data chages once it has run through all of the lines in it’s data set. I have already pre-created and populated the first 2 of the 3 tables the cursor replacements would read from, however as indicated, I cannot pre-populate the 3rd table as it data will change every time the data set is re-created.
I know I sound unclear on this, however I’m trying to understand it myself. Your help would be appreciated. Colin.

This is one scenario that you will often find in procedures written by people who don’t know how to approach data in tables … open cursor1
read a number of values open cursor2 filtering on the values from cursor1
read a number of values from cursor2 open cursor3 filtering on the values from cursor1 and/or cursor2 If this is the scenario, then it’s just a clumsy workaround for JOINs. You should be able to rework this as a single query with JOINs between all the tables on the columns from which the filter values are taken.

Hi Adriaan, Thanks for the quick reply. The scenario I’m encountering is slightly different from the example you gave above, however it is quite similar in many respects… ———————————————-
Declare date variables at beginning of script Declare Cursor 1
from table "A"
where date column in table "A" complies with date variables set in beginning of script open cursor 1
read a number of values
Declare Cursor 2
from table "A"
where date column in table "A" complies with date variables set in beginning of script
and where columns in table "A" are filtered on 1st line variables from cursor 1 open cursor2
read a number of values insert various lines of data into table "B"
FROM Table "A"
where date column in table "A" complies with previously declared date
and data is filtered on 1st line on cursor 2 declare cursor 3
from table "B"
where date column in table "B" complies with previously declared date and is filtered on 1st line used in cursor 2 open cursor 3
read a number of values Update table "B"
from Table "B"
where data is filtered on 1st line from cursor 2 and on 1st line in cursor 3 and on date set earlier in the script fetch next line from cursor 3 Re-Update table "B" using same filters from cursor 2 and from date variable set, however now it uses 2nd line from cursor 3 Continue this process untill all lines in cursor 3 have been used. End cursor 3
Close and deallocate cursor 3 insert data into table "B"
filtering on declared date, and on 1st line in cursor 2 fetch next from cursor 2 Entire process repeated untill all lines in cursor 2 have been used end cursor 2
Close and deallocate cursor 2 insert data into table "B"
filtering on last line inserted into memory before cursor 2 was closed and deallocated declare cursor 4
using same variables declared for cursor 3 and filtering on date previously declared. Also filtering using last line inserted into memory before cursor 2 was closed and deallocated
FROM Table "B" Update Table "B"
from Table "B"
where data is filtered on last line from cursor 2 and on 1st line in cursor 3 and on date set earlier in the script fetch next line from cursor 4 Re-Update table "B" using same filters used in the text directly above, however now it uses 2nd line from cursor 4
Continue this process untill all lines in cursor 4 have been used. End cursor 4
Close and deallocate cursor 4 insert data into table "B"
filtering on last line in memory from cursor 2 and on date previously set fetch next line from cursor 1
———————————————- From my knowledge, the entire process described above is repeated as soon as the next line from cursor 1 is fetched, however I could be wrong. Thanks again for your help Adriaan. Hope to hear from you soon.

Well, I didn’t read past cursor 2 – but at that point it most definitely is the scenario that I was describing. Just make sure that you add the filter criteria for all tables involved in the SELECT.

Sorry for sounding so ignorant, but how would I go around implementing the Joins on the columns used in Cursor 1 and Cursor 2? I’m still very new to SQL, so I’m not too sure how to do what your advising me to do. Thanks
Colin.

Let me give you an example … Cursor 1 is:
SELECT column1, column2 FROM table1
WHERE column3 BETWEEN @date1 AND @date2 Cursor 2 is:
SELECT columnX, columnY FROM table2
WHERE column3 BETWEEN @date1 AND @date2
AND column1 = <value from table1.column1> and column2 = <value from table1.column2> You can combine these two into a single query: SELECT table1.column1, table1.column2, table2.columnX, table2.columnY
FROM table1 INNER JOIN table2
ON table1.column1 = table2.column and table1.column2 = table2.column2
WHERE table1.column3 BETWEEN @date1 AND @date2
AND table2.column3 BETWEEN @date1 AND @date2 All the other processing needs reviewing in detail, so good luck.

Thanks Adriaan, I’ll see what I can get done using the example you gave me. Have a good day. Colin.

Colin, By the way.. great job in knowing to avoid cursors rather than improve them. Junk they are. Smart you become! (old yoda saying)<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Michael B Sr. DBA "The fear of the Lord is the beginning of knowledge, but fools despise wisdom and instruction." Proverbs 1:7

Thanks Michael, I’ve been working with SQL now for about 2 months, so it was a mission to actually understand exactly what this script did, nevertheless actually finding a way to get it sorted. I think it will still be a while before the "force" is strong with me <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> I must say that this is an awesome site, and the people here are very helpful. A good day you must have, Michael.

]]>