cursor in stored procedure takes forever

I have basically identical stored procedures in two different databases. the SP creates a temp table, then creates a cursor using the temp table and does a FETCH loop to do calculations on each row of the table. In the original database, the whole procedure, including the cursor fetch, takes 19 seconds. In the new database the cursor alone takes over an hour to run. The contents of the cursors in the respective databases are exactly the same. What's up?

Since the only real difference I can identify is the databases. Everything else is the same. I am led to believe that there must be something different about the new database than the old database, index space? temp space? something ...

Any ideas?

LVL 1
jmarkfoleyAsked:
Who is Participating?
 
akku101Connect With a Mentor Commented:
Add a new datetime column in the temp table and update the column in each fetch. can find out which iteration ( fetch execution) is taking long time. you can execute the bit separately to drill down and find the
 solution.
0
 
WizillingCommented:
Whats is the difference with the database? Size, Rowcount, etc?

>>The contents of the cursors in the respective databases are exactly the same<<
The contents of the cursor might be the same - but are you comparing querying 1000 rows over 1 million rows.

Have you rebuild indexes / statistics on the newer one?

Are the differences between the resources allocated to the servers?


0
 
jmarkfoleyAuthor Commented:
The two procedures are querying into exactly the same data, but the new (slow) query accesses one of the tables via a join (which I did check for table indexing etc.) Akku101's suggestion to check the timing inside the cursor fetch revealed the clue I needed. That view was being joined inside the fetch loop whereas this was a table join in the faster query. When I removed this join and fetched the necessary data into a previously built temp table, the run time dropped from over an hour to just under a minute. That runtime will probably drop down to the same as the original query once I replace the offending view with a real table.

Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.