SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi Parameter Issue


Multi Parameter Issue

Author
Message
adams.squared
adams.squared
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 441
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25807 Visits: 17509
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.

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)
adams.squared
adams.squared
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 441
I have over 30,000 customer IDs. Reading the split, it looks like it can handle 8,000 characters.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25807 Visits: 17509
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.

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)
adams.squared
adams.squared
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 441
That makes sense. I'll see if I can get that to work.

Thanks
AA
adams.squared
adams.squared
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 441
Worked like a charm
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25807 Visits: 17509
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.

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)
adams.squared
adams.squared
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 441
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,',')))
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25807 Visits: 17509
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 Modens 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)
adams.squared
adams.squared
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 441
great thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search