Solved

SP with Cursor all of sudden very slow

Posted on 2014-04-09
7
373 Views
Last Modified: 2014-05-04
I have a SP which loads about 1.7 million records into a temp table, and then fetches each of those records and performs few checks to update records in physical tables

The part which loads records in temp table is quick - 1 min. however the remaining part is taking very long all of sudden.

Previously, entire SP used to query records in 3 mins. Now it takes 4-5 hours.

Any idea what could be causing this and what should I look for to get this sorted?

Thanks
0
Comment
Question by:cynx
[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
7 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39988816
Impossible to know without seeing query plans.

But you may be able to avoid the cursor and give yourself vastly better speed overall anyway.  Can't say for sure w/o seeing the code.
0
 
LVL 1

Author Comment

by:cynx
ID: 39990625
could there be issues with memory on the server?
0
 
LVL 1

Author Comment

by:cynx
ID: 39990628
the code is too huge to be posted.
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39990636
Like Scott said, it's impossible to know what the problem is without seeing your queries and plans.
The code "being too huge to be posted" is never a good sign... Can you save it to file and attach it?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 39992211
You'll have to review at least the estimated query plan (and much more preferably the actual execution plan) to determine where the performance issues are.


>> could there be issues with memory on the server? <<

Most likely is some type of I/O issue, but there "could" be ANY issue on the server.  Can't tell at all w/o a query plan.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 300 total points
ID: 39992558
If it worked fine before look at what else has changed in the environment as well.

Is a new server connected to the same SAN?

Are you running low on disk space? Even for the tempdb?

When was the last reboot?

Has there been a jump in data?

Has a new column like a varbinary(max) been added even if it is not used?

Just some things to look at.
0
 
LVL 7

Expert Comment

by:Docteur_Z
ID: 40016966
Has an index "disappear" ?
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is GIS method of Geometry data type? 6 36
SQL Server Agent "Access Denied" Error 3 38
Sql server query 8 21
Total count in section based report in SSRS 10 17
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

752 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