adams.squared (10/4/2013)
I'm at a client and they would like to be able to run their reports by typing in a few customer numbers, or have all of the customers automatically selected. There are over 30,000 customers.I've tried two things, but I run into problems.
Declare @Cust varchar(max)
Select Data
From Tables
1. Where CustomerNumber like '%'+@Cust+'%' If they type in a customer or none at all, this works fine. If they type in two or more, then it breaks. It's a LIKE statement, not an IN statement.
2. Where CustomerNumber in (Select Value from Splitfuntion (@Cust,',')) This works fine if they type in one or more customers, but if they want all customers, then that's 30k splits for my IN statement. That kills the query.
Any ideas?
The problem is your SplitFunction. If it is xml or contains a while or a cursor it will be very slow. Take a look at the link in my signature about splitting strings. In there you will find a super fast splitter.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/