I have the below update query, its taking around 3 minutes to execute, I have around 1.5 million records present in "Data" table and 100000 records present in "Report" table, can someone help me in optimizing similar kind of update queries.

Update [Data] set [Invoice Number]=S.[invoice No],
[Invoice Date]=S.[Invoice date],[From]=S.[From],
from [Data] A inner join 
[Report] S on RTRIM(LTRIM(A.[Order ID]))=RTRIM(LTRIM(S.[Order ID])) and S.UniqueID=' ABC'
where A.[Final Status]='Valid' and A.UniqueID='ABC'

Applying functions to columns in WHERE and JOIN clause predicates prevents indexes from being used efficiently.

RTRIM is unnecessary since trailing spaces are ignored during string comparison so that can be removed.

Update the underlying data to remove leading [Order ID] spaces so the LTRIM is unnecessary in queries. You can then modify the JOIN clause can specify A.[Order ID] = S.[Order ID]. This may improve query performance significantly, assuming you also have appropriate indexes in place.

  • A great answer for not so great question. We not sure what types are "Order ID". What's more have no idea about indexes. Having indexes on 'Final Status' and 'UniqueId' helps too. – Darko Martinovic Sep 23 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.