May 12, 2010 at 5:31 pm
We have a situation.
UPDATE stagingtable
SET DeleDate = @A1, IncrementLoadCd = @A2
WHERE TransactionId = @A3 and RangnDsTx = @A4
is taking too much long to run. This stagingtable contains 8 million rows.
And there is one clustered, unique, primary key located on PRIMARY and it is on
4 index keys two which are TransactionId and RangnDsTx.
Please suggest.
M&M
May 12, 2010 at 5:37 pm
mohammed moinudheen (5/12/2010)
We have a situation.UPDATE stagingtable
SET DeleDate = @A1, IncrementLoadCd = @A2
WHERE TransactionId = @A3 and RangnDsTx = @A4
is taking too much long to run. This stagingtable contains 8 million rows.
And there is one clustered, unique, primary key located on PRIMARY and it is on
4 index keys two which are TransactionId and RangnDsTx.
Please suggest.
Your Clustered Index is across four columns? Two of which are TransactionID and RangnDsTx?
How long is the query taking?
Can you alter your update statement so it includes all four columns from the clustered index?
Have you checked your index fragmentation level?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 5:42 pm
Also where in the index are these 2 columns? If they aren't the first 2 columns you are doing a table or clustered index SCAN, of 8 million rows.
Send us the index deffinition.
How many rows do you expect it to be updating?
Have you done a "show query plan"?
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 12, 2010 at 6:00 pm
Leo.Miller (5/12/2010)
Also where in the index are these 2 columns? If they aren't the first 2 columns you are doing a table or clustered index SCAN, of 8 million rows.Send us the index deffinition.
How many rows do you expect it to be updating?
Have you done a "show query plan"?
Leo
Here is the order of the indexes
Index Description: clustered, unique, primary key located on PRIMARY
Column Order: Balancepool, WestsideAccount, TransactionId, RangnDsTx
Kindly elaborate on " If they aren't the first 2 columns you are doing a table or clustered index SCAN".
Here, they are not the first two columns, that means this operation would lead to table scan right?
I am not sure, how may rows it would be updating.
On checking query plan, I can see there is 99% effort on clustered index scan
M&M
May 12, 2010 at 6:03 pm
CirquedeSQLeil (5/12/2010)
mohammed moinudheen (5/12/2010)
We have a situation.UPDATE stagingtable
SET DeleDate = @A1, IncrementLoadCd = @A2
WHERE TransactionId = @A3 and RangnDsTx = @A4
is taking too much long to run. This stagingtable contains 8 million rows.
And there is one clustered, unique, primary key located on PRIMARY and it is on
4 index keys two which are TransactionId and RangnDsTx.
Please suggest.
Your Clustered Index is across four columns? Two of which are TransactionID and RangnDsTx?
How long is the query taking?
Can you alter your update statement so it includes all four columns from the clustered index?
Have you checked your index fragmentation level?
Your Clustered Index is across four columns? Two of which are TransactionID and RangnDsTx?
Yes, correct
How long is the query taking?
CPUTime is 14940135 and DiskIO is 215397 and it is still running
Can you alter your update statement so it includes all four columns from the clustered index?
Do you want me to alter the where condition to use all the four columns which are used in clustered index
Have you checked your index fragmentation level?
No
M&M
May 12, 2010 at 6:21 pm
mohammed moinudheen (5/12/2010)
Leo.Miller (5/12/2010)
Also where in the index are these 2 columns? If they aren't the first 2 columns you are doing a table or clustered index SCAN, of 8 million rows.Send us the index deffinition.
How many rows do you expect it to be updating?
Have you done a "show query plan"?
Leo
Here is the order of the indexes
Index Description: clustered, unique, primary key located on PRIMARY
Column Order: Balancepool, WestsideAccount, TransactionId, RangnDsTx
Kindly elaborate on " If they aren't the first 2 columns you are doing a table or clustered index SCAN".
Here, they are not the first two columns, that means this operation would lead to table scan right?
I am not sure, how may rows it would be updating.
On checking query plan, I can see there is 99% effort on clustered index scan
That CI scan is due to the two columns being used. The TransactionId and RangnDsTx columns are the last two indexes in the clustered index so you are getting a scan. If you were using Balancepool and WestsideAccount, you would likely see better performance.
In this case a CI scan is just the same as doing a table scan.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 8:33 pm
CirquedeSQLeil (5/12/2010)
mohammed moinudheen (5/12/2010)
Leo.Miller (5/12/2010)
Also where in the index are these 2 columns? If they aren't the first 2 columns you are doing a table or clustered index SCAN, of 8 million rows.Send us the index deffinition.
How many rows do you expect it to be updating?
Have you done a "show query plan"?
Leo
Here is the order of the indexes
Index Description: clustered, unique, primary key located on PRIMARY
Column Order: Balancepool, WestsideAccount, TransactionId, RangnDsTx
Kindly elaborate on " If they aren't the first 2 columns you are doing a table or clustered index SCAN".
Here, they are not the first two columns, that means this operation would lead to table scan right?
I am not sure, how may rows it would be updating.
On checking query plan, I can see there is 99% effort on clustered index scan
That CI scan is due to the two columns being used. The TransactionId and RangnDsTx columns are the last two indexes in the clustered index so you are getting a scan. If you were using Balancepool and WestsideAccount, you would likely see better performance.
In this case a CI scan is just the same as doing a table scan.
How about creating a non-clustered index on either TransactionId or RangnDsTx column.
That should help right?
M&M
May 12, 2010 at 8:44 pm
You would need it on both columns not just a single column.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 8:47 pm
Yes, creating a non-clusterd index on the two columns would help, you probably want to do it after hours as it's going to take some time and cause blocking. Unless you have Enterprise edition and can use WITH (ONLINE = ON)
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 12, 2010 at 10:27 pm
Another question is, creating indices would generally help select statements. But in this case as it is a update, would it have any negative impact ?
M&M
May 12, 2010 at 10:39 pm
.. creating indices would generally help select statements. But in this case as it is a update, would it have any negative impact ? ..
Only if you are also updating one or more of the index values, as this will force SQL to move the index within the index structure (B-Tree). This will force SQL to lock the page until the update is finished. But you aren't doing this, so no effect.
It will also affect later inserts as SQL now has to maintain another set of indexes, but if the clustered index, and this index are the only two indexes on your table the affect should be minimal.
After you have created the index, you may want to run the following
SELECT COUNT(*) from stagingtable with (nolock)
WHERE TransactionId = @A3 and RangnDsTx = @A4
This will let you know approximately how many records are going to be updated. I say approximately because the count could be changing as it's being read.
Leo
Striving to provide a better service 😀
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply