Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multi Parameter Issue Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 8:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:39 PM
Points: 147, Visits: 424
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?


Post #1501614
Posted Friday, October 4, 2013 8:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1501615
Posted Friday, October 4, 2013 9:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:39 PM
Points: 147, Visits: 424
I have over 30,000 customer IDs. Reading the split, it looks like it can handle 8,000 characters.
Post #1501640
Posted Friday, October 4, 2013 10:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1501683
Posted Friday, October 4, 2013 10:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:39 PM
Points: 147, Visits: 424
That makes sense. I'll see if I can get that to work.

Thanks
AA
Post #1501685
Posted Friday, October 4, 2013 11:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:39 PM
Points: 147, Visits: 424
Worked like a charm
Post #1501702
Posted Friday, October 4, 2013 11:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1501709
Posted Friday, October 4, 2013 12:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:39 PM
Points: 147, Visits: 424
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,',')))
Post #1501735
Posted Friday, October 4, 2013 12:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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/

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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1501737
Posted Friday, October 4, 2013 1:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:39 PM
Points: 147, Visits: 424
great thanks.
Post #1501743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse