how to improve performance of update query in sql?

Question

I want to improve the performance of this update query because when it take lots of time to execute:

Qry = "update tab1 set cDate=GetDate() where right('000000000000'+in_num,12)='" 
& InvNo.PadLeft(12, "0") & "' and (Total-Amount)<>Balance and cDate is null"

Answer 1

Why are you force-padding in_num and InvNo with leading 0s with:

right('000000000000'+in_num,12) = InvNo.PadLeft(12, "0")

This will prevent any indexes on in_num being used.

Answer 2

You don't mention which database you are using, but if it supports computed columns, then you can improve the query speed by creating computed columns for the padded rows, which you can then create an index over.

Of course, this is assuming you can't use simple integer types for the invoice numbers. If you can use in_no as it is, then make sure you have an index on that column.