Perfomance of Update Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Perfomance of Update Query

Hello All, I Have Update Statment with join statement and this statemnet have more then 50000000 records in the Detination table and when i fire this Query at that time My SQL Server take more then 6 hour to execute this Query.and its look like Hung.
Here is the Update Statement UPDATE
d
SET
Field1 = s.Field1
FROM
Destination d inner join Source s on
d.Field2 = s.Field2 and d.Field3 = s.Field3
Here no any Index there in any Table Col.
Jatin Purohit
(M)- +919426025391
An index on the joined fields should help speed up the process. Consider also batch updates.

Also copy SQL into SQL Analyzer and see execution plan.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
…and update stats on the table before running this query. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forums This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
When i see the execution plan and check that most of time it will take for Join Table.
Also i allay index but i can not see any changes on this. Jatin Purohit
(M)- +919426025391

Hi there, try creating an index on the source table starting with the most selective column in the where clause through to the least selective followed by the columns in the set clause. and also an index on the destination table starting with the most selective column in the where clause through to the least selective (NOTE do not add the columns in the set clause So in your example above it might be create index ix_s on s( field2, field3, field1 )
create index ix_d on d( field2, field3 ) assuming field2 is more selective than field3 (i.e. has more unique values in the table) Cheers
Twan PS how many rows in the source table?
i will Test Script after impliment the Index as per above.
there will be not more then 5000 rows in Source Table Jatin Purohit
(M)- +919426025391
Hi Jatin, Just ask asvforce, as he can help u by putting your query on www.prakashinfotech.com Regards,
Ashish
]]>