Solved

cursor in stored procedure takes forever

Posted on 2011-09-17
3
253 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 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

636 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