?
Solved

cursor in stored procedure takes forever

Posted on 2011-09-17
3
Medium Priority
?
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Accepted Solution

by:
akku101 earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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