October 4, 2013 at 8:06 am
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?
October 4, 2013 at 8:12 am
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/
October 4, 2013 at 9:23 am
I have over 30,000 customer IDs. Reading the split, it looks like it can handle 8,000 characters.
October 4, 2013 at 10:49 am
adams.squared (10/4/2013)
I have over 30,000 customer IDs. Reading the split, it looks like it can handle 8,000 characters.
Maybe you need a couple of queries then. If they want all the customers there really is no need to split anything, just return them all.
Something like this. You might even want to split each of the IF conditions to its own stored proc depending on how this performs
If @CustNums is null
--Query to return ALL customers
select *
from YourTable
else
--Query that parses them
select *
from YourTable
cross apply dbo.DelimitedSplit8K(@CustNums, ',')
_______________________________________________________________
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/
October 4, 2013 at 10:52 am
That makes sense. I'll see if I can get that to work.
Thanks
AA
October 4, 2013 at 11:19 am
Worked like a charm
October 4, 2013 at 11:32 am
adams.squared (10/4/2013)
Worked like a charm
Sweet glad to hear it.
_______________________________________________________________
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/
October 4, 2013 at 12:45 pm
I found this also. Seems to work. Takes less code.
SELECT * FROM TABLE
WHERE (@CUST='' OR CUSTNMBR IN (SELECT LTRIM(item) value FROM dbo.DelimitedSplit8K(@cust,',')))
October 4, 2013 at 12:53 pm
adams.squared (10/4/2013)
I found this also. Seems to work. Takes less code.SELECT * FROM TABLE
WHERE (@CUST='' OR CUSTNMBR IN (SELECT LTRIM(item) value FROM dbo.DelimitedSplit8K(@cust,',')))
Do be careful if you take this approach. Less code does not always mean the most efficient. It looks excellent at first but you can end up getting some really bad execution plans. Take a look at Gail's article here. Your execution plans will suffer the same challenge.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
And you can follow along on her blog here for an explanation of how to avoid this problem.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
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/
October 4, 2013 at 1:05 pm
great thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply