CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
KentZhou
KentZhou

Reputation: 25573

How to improve performance for query with CHARINDEX?

Suppose I have a table 

MyTab (ID int, MyNo varchar(50),.....)

There is a index on the MyNo column.

Then I have following SQL query:

declare @Nos varchar(100000)
set  @Nos = ',001,002,003,004,'

Select * 
from MyTab
Where CHARINDEX(',' + MyNo + ', @Nos) > 0

It is fine when the string @Nos is short. But if @Nos is larger, say length is 10000, the query performance is pretty bad.

How to improve the performance for this case? Or better solution to replace the query?

Upvotes: 0

Views: 1970

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56755

I posted this approach as a tongue-in-cheek answer to this question. Unfortunately, too many people take it seriously. Though it's OK for ad-hoc querying, it's really not appropriate for production. The accepted answer at that question is much better and can address your performance problem also.

Upvotes: 2

Related Questions