July 2, 2002 at 12:26 pm
I'm using the UPDATE command (T-SQL) to change one field with another field condition. The table has an index field(PRPR_ID) but how can I use this to increase the performance of this update?
UPDATE CMC_PRPR_PROV
SET PRPR_STS = ' '
WHERE PRPR_STS = 'NP'
Table: CMC_PRPR_PROV
Record count: 100,000
Thanks ahead for the HELP
Leonard
July 2, 2002 at 12:59 pm
Have you checked the query plan?
An alternative is to "batch" the updates:
http://www.sqlservercentral.com/columnists/sjones/batching.asp
Steve Jones
July 5, 2002 at 9:06 am
Also, it looks like you said that your index is on the column: PRPR_ID. Your query, however, does not involve that column; therefore, the query optimizer is very unlikely to use your index, and any changes to your query to force the use of the index would most likely lead to performance degredation.
If you want to use an index for this statement, then you need an index that is useful to the statement: you need an index on PRPR_STS column. Unfortunately, you may still see some performance loss, since - in this statement - you are updating the PRPR_STS column, which means that SQL Server will also have to update any index that is built on that column. Nevertheless, you may still see better performance than whatever you are currently experiencing, so it may be worth it to build an index on PRPR_STS.
Matthew Burr
July 10, 2002 at 11:59 am
quote:
Also, it looks like you said that your index is on the column: PRPR_ID. Your query, however, does not involve that column; therefore, the query optimizer is very unlikely to use your index, and any changes to your query to force the use of the index would most likely lead to performance degredation.If you want to use an index for this statement, then you need an index that is useful to the statement: you need an index on PRPR_STS column. Unfortunately, you may still see some performance loss, since - in this statement - you are updating the PRPR_STS column, which means that SQL Server will also have to update any index that is built on that column. Nevertheless, you may still see better performance than whatever you are currently experiencing, so it may be worth it to build an index on PRPR_STS.
Matthew Burr
July 11, 2002 at 4:08 pm
Thanks Matthew Burr and Steve Jones for your suggestions !
July 11, 2002 at 8:28 pm
You are welcome. Keep coming back.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply