• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

SP with Cursor all of sudden very slow

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
cynx
Asked:
cynx
2 Solutions
 
Scott PletcherSenior DBACommented:
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
 
cynxAuthor Commented:
could there be issues with memory on the server?
0
 
cynxAuthor Commented:
the code is too huge to be posted.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Jim P.Commented:
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
 
Docteur_ZCommented:
Has an index "disappear" ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now