CHARINDEX in WHERE clause?

Question

CHARINDEX in WHERE clause?

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
November 15, 2010 at 10:06 am

#229319

Hi,
I was having a discussing with a collegue. I believe it is not wise to put a CHARINDEX in a WHERE clause because of degrading performance. He believes that the performance will suffer so little that it shouldn't be a problem.
So, who is right?
Example of the query:
UPDATE campagnes_mailmerge SET estimatedToBeSent = estimatedToBeSent + 1
FROM campagnes_mailmerge cm WITH (NOLOCK)
WHERE cm.campagnePID = @campagnePID AND cm.pid=pid
AND CHARINDEX(';' + CAST(@resultcode AS VARCHAR(20)) + ';', cm.rclist) > 0

Viewing 8 posts - 1 through 7 (of 7 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Well it is Non-Sargable

http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx"> http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx

so performance would not be optimal.

Why don't you try both ways and then compare the execution plan?

sturner SSC-Insane Points: 22411 More actions · Answer 2

It is a concept that should be avoided whenever possible, and certainly shouldn't be a part of a design where high performance is required. It is better to use LIKE if you must search on the occurrence of a character in a where clause.

The probability of survival is inversely proportional to the angle of arrival.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

r.vanlaake-1086273 (11/15/2010)
I was having a discussing with a collegue. I believe it is not wise to put a CHARINDEX in a WHERE clause because of degrading performance. He believes that the performance will suffer so little that it shouldn't be a problem.
So, who is right?

Probably both of you. How much data are we talking here? Perfect code sometimes takes manipulations that make it harder to maintain and upkeep. A few 100k in rows, this shouldn't matter.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ben Miller SSCrazy Points: 2995 More actions · Answer 4

And it may be worth creating a variable above and putting the ';' + text + ';%' together in the variable and then using it in a like.

Ben Miller
Microsoft Certified Master: SQL Server, SQL MVP
@DBAduck - http://dbaduck.com

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 5

Thanks to all for your input! Especially SARGABLE has given us new insights!!

Raymond

Paul White SSC Guru Points: 150467 More actions · Answer 6

r.vanlaake-1086273 (11/15/2010)
I was having a discussing with a collegue. I believe it is not wise to put a CHARINDEX in a WHERE clause because of degrading performance. He believes that the performance will suffer so little that it shouldn't be a problem. So, who is right?
Example of the query:
UPDATE campagnes_mailmerge SET estimatedToBeSent = estimatedToBeSent + 1
FROM campagnes_mailmerge cm WITH (NOLOCK)
WHERE cm.campagnePID = @campagnePID AND cm.pid=pid
AND CHARINDEX(';' + CAST(@resultcode AS VARCHAR(20)) + ';', cm.rclist) > 0

It is usually bad to use CHARINDEX if the number of rows to be tested is large. If there are other conditions that result in the CHARINDEX only touching a small number of rows, it is hard to see a general objection.

That said, I have to say I think the issue would be better avoided altogether. A more relational design (rather than storing a delimited list of codes in a single column) might be beneficial.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 7

steveb. (11/15/2010)
Well it is Non-Sargable
http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx"> http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx
so performance would not be optimal.

That article is nearly four years old now, and much of the specific advice given is out of date (if was ever correct).

For example, all the following use an index seek:

CREATE TABLE #T (a INT NULL);

CREATE INDEX nc1 ON #T (a);

INSERT #T VALUES (5);

GO

-- All produce a plan with an index seek on SQL Server 2005+

SELECT a FROM #T WHERE NOT a > 5;

SELECT a FROM #T WHERE a IS NULL;

SELECT a FROM #T WHERE a <> 5;

SELECT a FROM #T WHERE a != 5;

SELECT a FROM #T WHERE a !> 5;

SELECT a FROM #T WHERE a !< 5;

GO

-- SQL Server 2008+ only

SELECT a FROM #T WITH (FORCESEEK) WHERE a NOT IN (1, 2, 3, 4);

GO

DROP TABLE #T;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi