Hi Eric,

Thank your for your help and comments.

Yes, I metioned above this update takes 45 seconds, reason why this unacceptable is because this is one query of 11 I need to run to the same table and total execution time for 11 querys is 7 minutes.

Each Query substring field "Dialednumber" from RepSheet0_ from left to right and the result is compared with field Prefix on VendorBreakout that has the same lenght of the substring result. Example:

Dialednumber = "1234567890123"

First Update

LEFT(Dialednumber,11) = "12345678901"

"12345678901" = VendorBreakout.prefix AND VendorBreakout.prefixlen = 11 AND RepSheet0_.Cost = 0

Second Update

LEFT(Dialednumber,10) = "1234567890"

"1234567890" = VendorBreakout.prefix AND VendorBreakout.prefixlen = 10AND RepSheet0_.Cost = 0

Third Update

LEFT(Dialednumber,9) = "123456789"

"123456789" = VendorBreakout.prefix AND VendorBreakout.prefixlen = 9 AND RepSheet0_.Cost = 0

And continue until 1

I adjust your technique and the result is:

set nocount on;

declare @batch_rows int = 0, @total_rows int = 0;

while 1 = 1

begin

-- to minimize transaction log growth, checkpointing and pausing

-- each batch will help re-use space:

waitfor delay '00:00:05';

checkpoint;

-- update batch of rows:

update top (1000000) dbo.RepSheet0_

set dbo.RepSheet0_.Cost=dbo.VendorBreakout.Cost,

dbo.RepSheet0_.VendorPrefix=dbo.VendorBreakout.Prefix

FROM dbo.RepSheet0_ INNER JOIN

dbo.VendorBreakout ON LEFT(dbo.RepSheet0_.DialedNumber, 6) = dbo.VendorBreakout.Prefix

AND dbo.RepSheet0_.Vendor = CDRDB.dbo.VendorBreakout.VendorName

WHERE (dbo.RepSheet0_.Cost = 0) AND (dbo.VendorBreakout.lenprefix= 6)

select @batch_rows = @@rowcount;

select @total_rows = @total_rows + @batch_rows;

-- print status message:

raiserror('Rows affected: %d', 0, 1, @total_rows) with nowait;

-- if no rows were deleted, then break from loop:

if @batch_rows = 0 break;

end;

Execution time was 1:19 seconds instead of 1:17 seconds with the actual method.

Expected Target would be 2 minutes for the 11 querys.

Thank you!

Regards