Optimization for Update statement

Question

Optimization for Update statement

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
January 20, 2010 at 12:02 pm

#219728

:-D:-D Hello all! Here's the statement that I'd like to optimize and make more efficient.
UPDATE BatchTrans
SET CustCode =
CASE
WHEN EXISTS
(SELECT NULL FROM ForeignBins WHERE CONVERT(INT, SUBSTRING(CardNum, 1, 9)) BETWEEN TopRange AND BottomRange)
THEN 'Y'
ELSE 'N'
END
/*Other updates*/
WHERE TransId BETWEEN @firstTransId AND @lastTransId
Note: The table ForeignBins has 7594 records and the table BatchTrans can have 400K to 500K, CardNum usually stores a string with 16 digits number and is encrypted.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Well, I would typically use a JOIN for this, but it's realy hard to give you any advice when we have no idea how these tables are related.

Can you post your table DDL for BatchTrans and ForeignBins?

The link in my signature gives good advice on how to post your example DDL and data.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

john.arnott SSChampion Points: 11882 More actions · Answer 2

You'll probably get much more advice on this once you've posted the DDL and some examples (test data and expected results). But, until then, one thing that I'd question is doing the CONVERT of values in the database to int in the WHERE clause. Asking SQL to run a function, even one as simple as CONVERT, in the WHERE clause can slow queries significantly for a number of reasons, not least of which is that it leaves the optimizer no choice but to do table scans rather than use an index.

Probably more important would be to consider whether you really want to update all rows within your transID range in BatchTrans to a 'Y' or 'N' based on whether there are ANY entries in ForeignBins that match your top/bottom range criteria. The criteria in the CASE doesn't appear to be correlated to the update itself -- it just asks if any such rows exist in ForeignBins. The Update then uses the result ("Y" or "N") on all rows meeting the range criteria in the WHERE clause at the end.

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 3

All,

Attached is the DDL for the tables as you suggested.

BatchTrans row count - 160 million+ rows

ForeignBins row count- 7500 rows

Thanks for the assistance everybody! 🙂

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

How large are the BottomRange and TopRange intervals?

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

UPDATEbt

SETbt.CustCode =CASE

WHEN x.LowerNum IS NULL THEN 'N'

ELSE 'Y'

END

FROMdbo.BatchTrans AS bt

LEFT JOIN(

SELECTLEFT(BottomRange + REPLICATE('0', 50), 50) AS LowerNum,

LEFT(TopRange + REPLICATE('9', 50), 50) AS HigherNum

FROMdbo.ForeignBins

) AS x ON x.LowerNum <= bt.CardNum

AND x.HigherNum >= bt.CardNum

N 56°04'39.16"
E 12°55'05.25"