Solved

cursor in stored procedure takes forever

Posted on 2011-09-17
3
241 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

756 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