Solved

cursor in stored procedure takes forever

Posted on 2011-09-17
3
229 Views
Last Modified: 2012-05-12
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?

0
Comment
Question by:jmarkfoley
3 Comments
 
LVL 2

Accepted Solution

by:
akku101 earned 500 total points
ID: 36555025
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
 
LVL 13

Expert Comment

by:Wizilling
ID: 36563867
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
 
LVL 1

Author Closing Comment

by:jmarkfoley
ID: 36709995
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question