May 12, 2011 at 4:52 am
I have a nightly job on my production database. This takes 30-40 minutes to execute. I looked into the code and the execution plan and found that there are two update statements inside the cursor loop and these tables have 20 million+ rows in it. So each time the cursor runs, the tables are scanned and data is updated. Is there a way to deal this more efficiently?
Inside the cursor OPIS_Cursor, there is a statement as:
Update dbo.Transactions Set
TractorPPG = @Price,
TractorCost = @TotalCost
Where Current OF OPIS_Cursor
and this keeps getting executed all the time when cursor is in action. How to avaoid this problem
May 12, 2011 at 5:11 am
Convert the cursor into a set-based solution.
With the current information from your post, that's the best answer I can give. If you want a better answer, I'd need you to provide ddl and the current code and hopefully the business logic that required the cursor in the first place.
May 12, 2011 at 5:12 am
...rewrite the update as a set based operation rather than a cursor. The performance of cursors for this type of work is unnecessary and dog slow.
We can't tell you how to rewrite it without seeing the whole of the cursor, but I'm betting it can be easily converted to a set-based method.
May 12, 2011 at 6:13 am
HowardW (5/12/2011)
...rewrite the update as a set based operation rather than a cursor. The performance of cursors for this type of work is unnecessary and dog slow.We can't tell you how to rewrite it without seeing the whole of the cursor, but I'm betting it can be easily converted to a set-based method.
i'm with Howard on this; I've never...i mean never...seen a cursor that is touching data for update/insert/delete that cannot be replaced with a set based solution;
now I've seen situations where cursors are faster, because they lock one row at a time and avoid contention, and any time you are generating statements from metadata like sys.tables, i think cursors are fine, but the work they do can be replaced with a set based operation.
Lowell
May 12, 2011 at 6:18 am
skcadavre (5/12/2011)
Convert the cursor into a set-based solution.With the current information from your post, that's the best answer I can give. If you want a better answer, I'd need you to provide ddl and the current code and hopefully the business logic that required the cursor in the first place.
I am attaching the code of the procedure for the review. Want to tell you that the tables Transactions and Products are very big. Thanks! Let me know if any DDLs are required
May 12, 2011 at 6:26 am
Lowell (5/12/2011)
HowardW (5/12/2011)
...rewrite the update as a set based operation rather than a cursor. The performance of cursors for this type of work is unnecessary and dog slow.We can't tell you how to rewrite it without seeing the whole of the cursor, but I'm betting it can be easily converted to a set-based method.
i'm with Howard on this; I've never...i mean never...seen a cursor that is touching data for update/insert/delete that cannot be replaced with a set based solution;
now I've seen situations where cursors are faster, because they lock one row at a time and avoid contention, and any time you are generating statements from metadata like sys.tables, i think cursors are fine, but the work they do can be replaced with a set based operation.
I agree with you guys. I have uploaded the cursor query and in the execution plan for each update inside the cursor, it does a scan of monster tables.
May 12, 2011 at 6:50 am
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.
May 12, 2011 at 7:47 am
Just a question. What is this code for? I will also echo what has already be said.
select distinct
t.TransactionDate ,
pd.productDescription as 'Non Fuel Pricing',
P.ProductPrice ,
p.OrigPrice ,
p.ProductCost ,
p.OrigCost
from CardData.dbo.Company c
join CardData.dbo.Transactions t
on t.Company_Link = c.Company_Link
join CardData.dbo.Products p
on p.RecordKey = t.RecordKey
join CardData.dbo.ProductDescription pd
on pd.PD_Link = p.PD_Link
where c.CorporateCode = @CorporateCode
and t.Source = @source
and t.PostedTransactionDate >= @startPricingDate
and pd.ProductCategory = 'O'
and (isnull(P.ProductPrice , 0) = 0
or ISNULL(p.ProductCost , 0) = 0)
Order by 1, 2, 3
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 7:52 am
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'
May 12, 2011 at 7:53 am
Mike01 (5/12/2011)
Just a question. What is this code for? I will also echo what has already be said.
select distinctt.TransactionDate ,
pd.productDescription as 'Non Fuel Pricing',
P.ProductPrice ,
p.OrigPrice ,
p.ProductCost ,
p.OrigCost
from CardData.dbo.Company c
join CardData.dbo.Transactions t
on t.Company_Link = c.Company_Link
join CardData.dbo.Products p
on p.RecordKey = t.RecordKey
join CardData.dbo.ProductDescription pd
on pd.PD_Link = p.PD_Link
where c.CorporateCode = @CorporateCode
and t.Source = @source
and t.PostedTransactionDate >= @startPricingDate
and pd.ProductCategory = 'O'
and (isnull(P.ProductPrice , 0) = 0
or ISNULL(p.ProductCost , 0) = 0)
Order by 1, 2, 3
The developer who wrote it is working here no more and i am a new junior DBA. This code I checked for its efficiency, but looks good with just index seeks.
May 12, 2011 at 7:56 am
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.
May 12, 2011 at 8:08 am
chandan_jha18 (5/12/2011)
Mike01 (5/12/2011)
Just a question. What is this code for? I will also echo what has already be said.
select distinctt.TransactionDate ,
pd.productDescription as 'Non Fuel Pricing',
P.ProductPrice ,
p.OrigPrice ,
p.ProductCost ,
p.OrigCost
from CardData.dbo.Company c
join CardData.dbo.Transactions t
on t.Company_Link = c.Company_Link
join CardData.dbo.Products p
on p.RecordKey = t.RecordKey
join CardData.dbo.ProductDescription pd
on pd.PD_Link = p.PD_Link
where c.CorporateCode = @CorporateCode
and t.Source = @source
and t.PostedTransactionDate >= @startPricingDate
and pd.ProductCategory = 'O'
and (isnull(P.ProductPrice , 0) = 0
or ISNULL(p.ProductCost , 0) = 0)
Order by 1, 2, 3
The developer who wrote it is working here no more and i am a new junior DBA. This code I checked for its efficiency, but looks good with just index seeks.
The question I had on this code was that it doesn't do anything. It's not inserting into a table, so I was wondering if it was relevant. Is there an application that calls this proc and is expecting a result set?
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:20 am
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.
May 12, 2011 at 8:21 am
Mike01 (5/12/2011)
chandan_jha18 (5/12/2011)
Mike01 (5/12/2011)
Just a question. What is this code for? I will also echo what has already be said.
select distinctt.TransactionDate ,
pd.productDescription as 'Non Fuel Pricing',
P.ProductPrice ,
p.OrigPrice ,
p.ProductCost ,
p.OrigCost
from CardData.dbo.Company c
join CardData.dbo.Transactions t
on t.Company_Link = c.Company_Link
join CardData.dbo.Products p
on p.RecordKey = t.RecordKey
join CardData.dbo.ProductDescription pd
on pd.PD_Link = p.PD_Link
where c.CorporateCode = @CorporateCode
and t.Source = @source
and t.PostedTransactionDate >= @startPricingDate
and pd.ProductCategory = 'O'
and (isnull(P.ProductPrice , 0) = 0
or ISNULL(p.ProductCost , 0) = 0)
Order by 1, 2, 3
The developer who wrote it is working here no more and i am a new junior DBA. This code I checked for its efficiency, but looks good with just index seeks.
The question I had on this code was that it doesn't do anything. It's not inserting into a table, so I was wondering if it was relevant. Is there an application that calls this proc and is expecting a result set?
you are right here. This is a simple sql agent job which means that this portion of the code is just not required.
May 12, 2011 at 8:27 am
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 ;-).
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply