0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KentZhou
  • 24,805
  • 41
  • 134
  • 200
  • 4
    Better solution would be to change DB design. Instead of keeping comma separated list of numbers in a column, create another table that would list one number per row and foreign key pointing to ID in MyTab – Yuriy Galanter Sep 12 '13 at 16:15
  • possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) – Aaron Bertrand Sep 12 '13 at 16:52
  • 1
    As far as I know, a `VARCHAR` cannot be larger than 8000 characters..... – marc_s Sep 12 '13 at 17:16

1 Answers1

2

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.

Community
  • 1
  • 1
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137