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
Solved

SP with Cursor all of sudden very slow

Posted on 2014-04-09
7
363 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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