May 12, 2011 at 8:37 am
Ninja's_RGR'us (5/12/2011)
chandan_jha18 (5/12/2011)
Ninja's_RGR'us (5/12/2011)
chandan_jha18 (5/12/2011)
Ninja's_RGR'us (5/12/2011)
There's no way in hell we can rewrite this for you from here. It would take us most likely up to 1 week to completely rewrite this and tune it.What you can do is run the code for 1-2 items. Then optimize all the queries 1 by 1. Make sure all the where condition are correctly indexed, maybe even covering.
I'd start by the most often ran queries from inside the loop and then the query itself for the cursor. Unless this is a fast-forward cursor you need to make that baby fly as fast as you can.
I've tuned processes like this without rewrite just by adding indexes. By the time I was done it was descent (wasn't flying, but much more tolerable).
You can probably get this to run 5-100 times faster with the correct indexes.
We can help there but I don't see how we can fully rewrite the code for you.
If you choose that route we need all the tables definitions including the keys and indexes and then the actual execution plans for at least 1-2 loops which includes all the queries.
I was asked to show the procedure so i posted it. Sorry if i was misunderstood. I mentioned the real problem in my question. it occurs when the big table is updated inside a cursor. 'update products'
Yes you did what we asked. Then we saw the extent of the problem.
You can add the required index on that table and it should help. But it won't solve the whole problem.
That guy wrote a statement like
Update dbo.Transactions Set
TractorPPG = @Price,
TractorCost = @TotalCost
Where Current OF OPIS_Cursor
do you think any index will help here as this update statement is without where clause. i think he is trying to update the whole table there each time the cursor runs.
Please post the actual plan of that update... it'll be very educational ;-).
Oh yes. sure 🙂
May 12, 2011 at 8:40 am
That guy wrote a statement like
Update dbo.Transactions Set
TractorPPG = @Price,
TractorCost = @TotalCost
Where Current OF OPIS_Cursor
do you think any index will help here as this update statement is without where clause. i think he is trying to update the whole table there each time the cursor runs.
It's not updating the whole table each time. It is using where Current of Cursor which, according to MSDN, means
"Positioned updates use the CURRENT OF clause to specify a cursor. The update operation occurs at the current position of the cursor."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 8:42 am
That's the estimated plan. I need the actual plan.
Ideally I need the whole plan for at least 1 full loop which includes the update. Ideally a couple loops that hit all the if/else statements as well.
May 12, 2011 at 8:45 am
Mike01 (5/12/2011)
That guy wrote a statement like
Update dbo.Transactions Set
TractorPPG = @Price,
TractorCost = @TotalCost
Where Current OF OPIS_Cursor
do you think any index will help here as this update statement is without where clause. i think he is trying to update the whole table there each time the cursor runs.
It's not updating the whole table each time. It is using where Current of Cursor which, according to MSDN, means
"Positioned updates use the CURRENT OF clause to specify a cursor. The update operation occurs at the current position of the cursor."
I'm 99% sure of my answer to that, hence I still need to see the actual plan.
This is an cursor that sees the updates. AFAIK, it needs to refresh itself after each updates to the table >>> means rerunning a very expansive query (20+ tables with a couple scans & RID/Key lookups).
May 12, 2011 at 12:59 pm
Here are a few steps I would take to kill that *cough* c.u.r.s.o.r.:
1) store the result of Declare OPIS_Cursor Cursor ... in a temp table since it'll be used in more than one update statement
2) Merge the statements to assign @OPISTerminal, @OPISProductCode, @MOC_Adder_code and all the other ones referencing ProductAddersDescription into one statement using the CrossTab method (might be worth to move it into a separate iTvF(inline-table vlaued function) allowing to use it with CROSS APPLY)
3) figure out what sandbox.dbo.sp_GetOPIS3 does and if it can be replaced by an iTvF
4) change the IF conditions into CASE
5) remove all select statements that are just there to display what the *cough* c.u.r.s.o.r. is doing at the moment
As a side note:IIRC, the FOR UPDATE c.u.... (I can't even type it any more) is even one of the worst performing c.'s (I don't have the link for it available since I don't care about that option at all 😉 ) Whenever I do have to use a *cough**cough* c, I use a LOCAL STATIC FAST FORWARD.
Using the "Divide'n'Conquer" approach together with a set based solution and the right indexes on the related tables should help to speed it up by magnitudes.
If we'd had table defs and ready to use sampel data together with the expected result I'm sure there'd be a few of us willing to kill that *cough* ...
May 13, 2011 at 12:05 am
Ninja's_RGR'us (5/12/2011)
Mike01 (5/12/2011)
That guy wrote a statement like
Update dbo.Transactions Set
TractorPPG = @Price,
TractorCost = @TotalCost
Where Current OF OPIS_Cursor
do you think any index will help here as this update statement is without where clause. i think he is trying to update the whole table there each time the cursor runs.
It's not updating the whole table each time. It is using where Current of Cursor which, according to MSDN, means
"Positioned updates use the CURRENT OF clause to specify a cursor. The update operation occurs at the current position of the cursor."
I'm 99% sure of my answer to that, hence I still need to see the actual plan.
This is an cursor that sees the updates. AFAIK, it needs to refresh itself after each updates to the table >>> means rerunning a very expansive query (20+ tables with a couple scans & RID/Key lookups).
I have attached the actual plan saved in the memory cache which i found using DMVs.
May 13, 2011 at 2:50 am
chandan_jha18 (5/13/2011)
Ninja's_RGR'us (5/12/2011)
Mike01 (5/12/2011)
That guy wrote a statement like
Update dbo.Transactions Set
TractorPPG = @Price,
TractorCost = @TotalCost
Where Current OF OPIS_Cursor
do you think any index will help here as this update statement is without where clause. i think he is trying to update the whole table there each time the cursor runs.
It's not updating the whole table each time. It is using where Current of Cursor which, according to MSDN, means
"Positioned updates use the CURRENT OF clause to specify a cursor. The update operation occurs at the current position of the cursor."
I'm 99% sure of my answer to that, hence I still need to see the actual plan.
This is an cursor that sees the updates. AFAIK, it needs to refresh itself after each updates to the table >>> means rerunning a very expansive query (20+ tables with a couple scans & RID/Key lookups).
I have attached the actual plan saved in the memory cache which i found using DMVs.
Just a hint for now. Open up that plan with ssms 2008+ and you'll see 50+ warnings about missing indexes :w00t:.
Now don't put them all on, but why don't you try to figure out the missing ones and will adjust if you missed something...
May 13, 2011 at 3:51 am
Ninja's_RGR'us (5/13/2011)
chandan_jha18 (5/13/2011)
Ninja's_RGR'us (5/12/2011)
Mike01 (5/12/2011)
That guy wrote a statement like
Update dbo.Transactions Set
TractorPPG = @Price,
TractorCost = @TotalCost
Where Current OF OPIS_Cursor
do you think any index will help here as this update statement is without where clause. i think he is trying to update the whole table there each time the cursor runs.
It's not updating the whole table each time. It is using where Current of Cursor which, according to MSDN, means
"Positioned updates use the CURRENT OF clause to specify a cursor. The update operation occurs at the current position of the cursor."
I'm 99% sure of my answer to that, hence I still need to see the actual plan.
This is an cursor that sees the updates. AFAIK, it needs to refresh itself after each updates to the table >>> means rerunning a very expansive query (20+ tables with a couple scans & RID/Key lookups).
I have attached the actual plan saved in the memory cache which i found using DMVs.
Just a hint for now. Open up that plan with ssms 2008+ and you'll see 50+ warnings about missing indexes :w00t:.
Now don't put them all on, but why don't you try to figure out the missing ones and will adjust if you missed something...
There is a peculiar thing about the missing index suggestion. It is just displaying one missing index in its execution plan and till i create this one it will not show the other ones. howcome you saw 50+ different indexes here?
May 13, 2011 at 5:18 am
I really do not think that from DBA end anything can be done here. when its a poorly performing cursor, i am just suffocated.not sure where to go from here.
May 13, 2011 at 5:20 am
I had 30 seconds to download / analyse the plan.
You are right. Once you create that index, the optimizer will then use that one and possibly suggest a better one or other missing indexes.
This is the kind of back and forth that will undoubtably happen in a project like this... hence better to have direct access to the server and cut down on the time required to fix the issue.... once you get someone in you also get to learn from him and become better at your job and closer to senior DBA. I can't stress enough that this is by far the best option for you right now.
May 13, 2011 at 5:29 am
Ninja's_RGR'us (5/13/2011)
I had 30 seconds to download / analyse the plan.You are right. Once you create that index, the optimizer will then use that one and possibly suggest a better one or other missing indexes.
This is the kind of back and forth that will undoubtably happen in a project like this... hence better to have direct access to the server and cut down on the time required to fix the issue.... once you get someone in you also get to learn from him and become better at your job and closer to senior DBA. I can't stress enough that this is by far the best option for you right now.
Thanks. I started with backup and restores and then moved on high end DBA tasks. Gradually i started to read and understand t-sql but not on a level yet where i can think of an alternate logic.
Today I saw another stored procedure where a temp table is created and then a while loop runs to do all the work for all entries in the temp table. And yes, temp table has 100000 rows so this code runs for 1 hour even on my big daddy server.Do not know where I am going with all such stored procedures.
May 13, 2011 at 5:44 am
chandan_jha18 (5/13/2011)
Ninja's_RGR'us (5/13/2011)
I had 30 seconds to download / analyse the plan.You are right. Once you create that index, the optimizer will then use that one and possibly suggest a better one or other missing indexes.
This is the kind of back and forth that will undoubtably happen in a project like this... hence better to have direct access to the server and cut down on the time required to fix the issue.... once you get someone in you also get to learn from him and become better at your job and closer to senior DBA. I can't stress enough that this is by far the best option for you right now.
Thanks. I started with backup and restores and then moved on high end DBA tasks. Gradually i started to read and understand t-sql but not on a level yet where i can think of an alternate logic.
Today I saw another stored procedure where a temp table is created and then a while loop runs to do all the work for all entries in the temp table. And yes, temp table has 100000 rows so this code runs for 1 hour even on my big daddy server.Do not know where I am going with all such stored procedures.
As I said, you need training and you need to get this working asap.. Call in help, this will be way more instructive than anything we can put on this thread. Even on a call you could get 20-50 answers within 1 hour. On this thread it would likeky take 1 week to do the same.
May 13, 2011 at 11:55 am
chandan_jha18 (5/13/2011)
...Thanks. I started with backup and restores and then moved on high end DBA tasks. Gradually i started to read and understand t-sql but not on a level yet where i can think of an alternate logic.
Today I saw another stored procedure where a temp table is created and then a while loop runs to do all the work for all entries in the temp table. And yes, temp table has 100000 rows so this code runs for 1 hour even on my big daddy server.Do not know where I am going with all such stored procedures.
I strongly vote against jumping in and out of stored procedures.
You found the first one. So change it to perform better instead of leaving the job half done (at most).
I made a few recommendations that should help you to get started. Or you could post the information I requested and we can try to help you.
But to simply mention the next bad performing sproc in the very same thread without even trying to improve the first one makes me "wondering"...
There are four basic scenarios on how to deal with numerous bad performing procs:
1) rewrite the whole app since it might not only be an issue of poorly written sprocs but also a bad db design in general
2) tackle one sproc at a time starting with the worst performing (using e.g. sys.dm_exec_query_stats, column total_worker_time or a server side trace)
3) leave it as it is as long as the duration is acceptable ("don't fix it unlesse it's broken")
4) get a consultant in to help
Based on what I know so far I'd vote for option 4.
May 13, 2011 at 1:03 pm
Ninja's_RGR'us (5/13/2011)
chandan_jha18 (5/13/2011)
Ninja's_RGR'us (5/13/2011)
I had 30 seconds to download / analyse the plan.You are right. Once you create that index, the optimizer will then use that one and possibly suggest a better one or other missing indexes.
This is the kind of back and forth that will undoubtably happen in a project like this... hence better to have direct access to the server and cut down on the time required to fix the issue.... once you get someone in you also get to learn from him and become better at your job and closer to senior DBA. I can't stress enough that this is by far the best option for you right now.
Thanks. I started with backup and restores and then moved on high end DBA tasks. Gradually i started to read and understand t-sql but not on a level yet where i can think of an alternate logic.
Today I saw another stored procedure where a temp table is created and then a while loop runs to do all the work for all entries in the temp table. And yes, temp table has 100000 rows so this code runs for 1 hour even on my big daddy server.Do not know where I am going with all such stored procedures.
As I said, you need training and you need to get this working asap.. Call in help, this will be way more instructive than anything we can put on this thread. Even on a call you could get 20-50 answers within 1 hour. On this thread it would likeky take 1 week to do the same.
I know you are showing me the correct path. I do need training. I am a 4 year exp. guy who is an accidental DBA and all I learnt from web where guys like you post your research. Reading all this has made me enable to match my senior (10+) exp DBAs and sometimes I outshine them too when i tell them the reason based on the SQL Server internals.
My organisation does not provide any training. i learnt adminstration work from web but can't do the same for development. when you guys suggest me to write a set based solution instead of a cursor, honestly speaking i appreciate your suggestion from my soul but it just does not make sense to me as i never spoke t-sql language. I really appreciate you guys taking time and writing for me.
May 13, 2011 at 1:06 pm
LutzM (5/13/2011)
chandan_jha18 (5/13/2011)
...Thanks. I started with backup and restores and then moved on high end DBA tasks. Gradually i started to read and understand t-sql but not on a level yet where i can think of an alternate logic.
Today I saw another stored procedure where a temp table is created and then a while loop runs to do all the work for all entries in the temp table. And yes, temp table has 100000 rows so this code runs for 1 hour even on my big daddy server.Do not know where I am going with all such stored procedures.
I strongly vote against jumping in and out of stored procedures.
You found the first one. So change it to perform better instead of leaving the job half done (at most).
I made a few recommendations that should help you to get started. Or you could post the information I requested and we can try to help you.
But to simply mention the next bad performing sproc in the very same thread without even trying to improve the first one makes me "wondering"...
There are four basic scenarios on how to deal with numerous bad performing procs:
1) rewrite the whole app since it might not only be an issue of poorly written sprocs but also a bad db design in general
2) tackle one sproc at a time starting with the worst performing (using e.g. sys.dm_exec_query_stats, column total_worker_time or a server side trace)
3) leave it as it is as long as the duration is acceptable ("don't fix it unlesse it's broken")
4) get a consultant in to help
Based on what I know so far I'd vote for option 4.
I am really sorry if i confused you guys. i did not want to mix two questions but day after day i am seeing stored procedures running for 30-40 minutes, with cursors doing even 1 million rounds in a code.
I have put the execution plan in the thread. I will post the ddl and some data in the table. Thanks for following up and giving suggestions.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply