Which one to compare 1'st string or integer in a where clause for better performance

Question

Post reply

Which one to compare 1'st string or integer in a where clause for better performance

K Kumar

SSCommitted

Points: 1662
More actions
January 8, 2008 at 9:05 am

#180243

Which one to compare 1'st string or integer in a where clause for better performance
I have a query which does validation before inserting/updating which is like
SELECT TOP(1) @result_category_id = category_id
FROM category_table
WHERE language_id =
AND
...Kumar

Viewing 10 posts - 1 through 9 (of 9 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>Which one to compare 1'st string or integer in a where clause for better performance

It doesn't make any difference. The optimizer makes a decision based on indexes, statistics etc.

Also, what you're doing can be expressed as a single, set-based operation. You don't need to declare variables and SELECT TOP 1 to determine if something exists prior to INSERT.

INSERT INTO YourTable

WHERE NOT EXISTS ()

K Kumar SSCommitted Points: 1662 More actions · Answer 2

Thank you. I will try to implement if not exists. Another one which one is faster IF EXISTS or IF NOT EXISTS...

...Kumar

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

>>Another one which one is faster IF EXISTS or IF NOT EXISTS...

There is no correct answer. As always ... "it depends".

Both of these contructs execute a query, and whichever is fastest depends on what exactly is being queried, indexes, statistics etc.

For a simple single table test, it is generally faster to check IF EXISTS, because the check can bail out on the first item found that proves existence=true.

K Kumar SSCommitted Points: 1662 More actions · Answer 4

Thanks a lot for the fast reply. I was checking in the Performance - Object Execution Statistics Report in SQL 2005, & as you said IF Exists was doing better .

...Kumar

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Actually the fastest way would be to use UPSERT.

Update

set col2 = @par2

where Col1 = @par1

if @@rowcount = 0

insert into (Col1,Col2)

values

(@par1,@par2)

When you do a If Exist, it does an additional read to the table to find out if the record exist before you can update.

When you do Upsert, you reduce one table read IF THE RECORD EXIST.

If not, it will have the two reads as that off IF Exist

-Roy

K Kumar SSCommitted Points: 1662 More actions · Answer 6

Sounds Kool... Thanks i would definetely try using this

...Kumar

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

It's great code! But only if you're inserting 1 row at a time from the GUI. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

Yes, when you are trying to update with the key it is useful.

-Roy

tfifield SSCrazy Eights Points: 9655 More actions · Answer 9

I normally use a set based solution with 2 passes for an UPSERT.

Something like:

UPDATE Tbl1

SET Tbl1.SomeData = Tbl2.SomeData

FROM Tbl1

INNER JOIN Tbl2 ON

Tbl1.PK = Tbl2.PK

INSERT INTO Tbl1

(PK, SomeData)

SELECT Tbl2.PK, Tbl2.SomeData

FROM Tbl2

LEFT JOIN Tbl1 ON

Tbl2.PK = Tbl1.PK

WHERE

Tbl1.PK IS NULL

Todd Fifield