Passing TOO MANY parameters to Stored procedures??

I have a table with about 80 columns in it, and 90% of them are bits. I need to make a where statement based off of checkboxes on a webpage. If they check the box, then I'll have to add that column in the where clause to be true: 'where checkedbox=1'.

I have a stored procedure that does other stuff too, so I'll need a SP for this. But is sending 80 parameters to the stored procedure a little much? This page will be A LOT of traffic. Will this be a large strain on the server? I'd like to set it up so if they DID NOT check the box, then its not even apart of the where clause, only if they did check the box.

This is what I have:
-------------------------------------------
SELECT ListingID, ROW_NUMBER() OVER (ORDER BY @OrderBy) as Row
INTO #TempListings FROM Listings;
--need to add extensive WHERE clause

SELECT @TotalListings=Count(ListingID) FROM #TempListings;

SELECT Listings.ListingID, CurrentPrice, StreetAddress1, CityAddress, StateAddress, RowColor, PropTypeTitle, LotSizeTitle
FROM #TempListings
INNER JOIN Listings ON #TempListings.ListingID = Listings.ListingID
WHERE Row between @startRowIndex and @startRowIndex+@NumRows-1;

DROP TABLE #TempListings;
--------------------------------------------

In the first SELECT statement, I need to add an extensive WHERE and AND criteria. One thought is to pass the where clause as a varchar parameter. But when I do that and base the first select statement off of a exec('') string, then the 3rd select statement doesn't recognize #TempListings. Or to pass all 80 some parameters and build the where clause in the stored procedure. Plus this may require 80 IF statements. There has to be an easier way.

I need some advise on how I should build the where clause. Please help! Thanks

80 parameters is nothing,
if anything, you will be performance limited by the use of the temp table
a dynamic sql will also be very expensive relative to simply passing in 80 parameters

also note, this should have been filed in the sql 2005 section

now i will assume what you meant is the following
if @P1 = true, return only col1 = true,
if @P1 = false, return all, as opposed to return only false

then write
WHERE primary sarg = xx
AND (@P1=false OR col1=true)
AND (@P2=false OR col2=true)

warning:
never use this techniques unless there are hard set primary search arguments to narrow the rowset, only use this for further filtering

consider using CTE to eliminate the temp table

What is CTE? I previously had my code in a:
WITH TempTable ( Select * Table )

Then I narrowed down the TempTable as before, but I wasn't able to figure out a way to get the total count of the original table prior to using the ROW_NUMBER function to chop it down.

So we've established I should go with the 80 parameters. Basically I'm creating an Advanced search page. They can search the listings by location, or go to the advanced search page and search by location and the 80 other possibilities. Should I use the same SP for both basic and advanced search, but just go off of default values for the 80 parameters during the search? But should every single search, even the ones where I ONLY search for city, also go through the 80 advanced search parameters by defaults?

I really didn't follow:

quote:WHERE primary sarg = xx
AND (@P1=false OR col1=true)
AND (@P2=false OR col2=true)

warning:
never use this techniques unless there are hard set primary search arguments to narrow the rowset, only use this for further filtering

Thanks

If the parameters are too many for YOU, then they are too many. Consider putting those 72 individual parameters (90% of 80) into an int bitfield, since all you will be doing with them is a test against the checkbox = 1 anyway.

Second, to make the query future extensible, and to allow for not having 80 if statements, consider a loop with the names in an array.



WHILE @@iLoop < @@loopMax
BEGIN
IF (bitfield & power(2,@@iLoop-1))
SQLQuery = SQLQuery + ' AND ' + @@FieldName(@@iLoop) + ' = 1 ';
@@iLoop = @@iLoop + 1;
CONTINUE
END


I hope this helps,

Aaron.

the int bit field is a very solution for C/C++ code,
it is not good for SQL because sql does not generate true binary code, for which the c code is very efficient.

also, the dynamic sql will cripple your system, do not do this, or be prepared to get a new job that does not require a previous employment check

have 2 separate queries, one for the general, the second checking each special search parameter

OK.

suppose your extra input parameters are:
@P1 for has washing machine
@P2 for allows pets
@P3 for whatever

the bit columns in your table for washing machine is col1, for pets is col2
then use my example above

if this is still not clear,
then post your full current query for all the search cond. and your full table DDL (generate script)

What about COALESCE? You should be able to write a WHERE clause using COALESCE that limits your search criteria only if the user passes a value (checks a box)



WHERE column_name = COALESCE(@firstColumnChecked, column_name)
AND column_name2 = COALESCE(@secndColumnChecked, column_name2)


if you pass a value for @firstColumnChecked, it will put it in the WHERE, otherwise (if you pass a NULL value,) it'll substitute whatever is after the comma, in this case column_name, then your WHERE reads, in effect


WHERE column_name = column_name

Of course, this is always true, so it won't limit your results by this column. But, if you pass in true for @secondColumnChecked, then it will add this to your WHERE clause:


AND column_name2 = true

Hope this helps.



v/r

Gooser

quote:Originally posted by joechang

the int bit field is a very solution for C/C++ code,
it is not good for SQL because sql does not generate true binary code, for which the c code is very efficient.

I should have clarified. Since abradley81 is attempting to reduce the parameters to their Stored Procedure, without losing information in the proces, I figured that they could merge the parameters prior to getting to the SP. I am not attempting to generate binary code, just a few integers (I guess three at this point) to represent all the checkboxes. I guess the same could be done with a string of ones and zeroes, to avoid any binary issues, and that would keep the parameters for that down to one. The loop would be the same except the if condition would look like this:


IF (SUBSTR(@checkboxString,iLoop-1,1) = '1')

That might be more readable, and it is done by some companies for credit card transactions where alot of flags are sent with data speed as a top issue.

quote:
also, the dynamic sql will cripple your system, do not do this, or be prepared to get a new job that does not require a previous employment check

I am not questioning your expertise, just curious why this loop would be a dismissable offense. Many locations I have worked at generated a dynamic query within stored procedures without burdening the system, in part because they had no choice. Other than the loss of cacheing the results, which I can understand, what would cause the dynamic SP to cripple the system?

on 1, there is nearly zero load on extra parameters,
there is much more load on substring parsing, so why bother,
atleast the parameters can be given meaningful names

on 2.
a hard coded select statement might have a one time cost of 50K cpu-cycles plus 1-2k per additional row,
the cost of a dynamic sql compile might be 5-100M cycles.
given the 3GHz systems, it is not discernable until you run a load test.
this is why it very important to understand the cost structure of sql ops,
the importance of designing your db to not need spoon bending

also, the dynamic sql with 80 optional parameters could cause flooding of the procedure cache


for straight select queries, you might do 10K selects/sec with hard code, but only 500-1000/sec with dynamic, so it seems ok in a single thread test
mostly likely you do not need to do 1000 cc tx/sec, so it works for your need

in this particular case, it might not be noticeable because of the overhead of the temp table
also, he is probably getting a recompile anyways when he inserts more than 6 rows.

but potentially, search queries in other apps could hit 1000's/sec, but not likely for apartments

joechang, do you see any problems with Gooser's idea of using COALESCE?

WHERE column_name = COALESCE(@firstColumnChecked, column_name)
AND column_name2 = COALESCE(@secndColumnChecked, column_name2)

Would there be any performance issues with it? I'm thinking the route seems to go with passing 80 parameters and using COALESCE in the hard coded select statement. Good to go??

Thanks for all the help guys!

With larger numbers of rows to be processed, you might find that a nice CASE construct takes less time than COALESCE.

Another idea, I've used before in much smaller lists of checkboxes is to number the checkboxes by squares of two, then add the values of the boxes checked, pass a single integer, then use a case statement that checks that integer to determine which boxes were checked.

So, you have boxes 2, 4, 8, 16, 32, 64 any combination of checked boxes creates a unique sum.

i.e. First and Third = 10, while no other combination could yield 10.

The problem with this--for what you are doing--however is that 2^80 is a really flippin' big number. (1,208,925,819,614,629,174,706,176) So, it may not be practical in your case, but it can work in similar cases. Just thought I would throw that out there for the other people reading this.



v/r

Gooser

This is the max URL what would be pass via querystring:

view.aspx?ars=0,0,0,0&npr=0&xpr=10000001&prt=2&lvn=1&dng=1&fam=1&den=1&rec=1&hth=1&ffl=1
&eik=1&mbt=1&wlk=1&air=1&gfa=1&frp=1&gsr=1&ref=1&dsw=1&wdr=1&wsf=1&ctt=1&wdf=1
&dkp=1&htb=1&pol=1&lsc=1&lwn=1&bsf=1&gwl=1&ufx=1&alm=1&wrt=1&pbt=1&ptr=1&prt=1
&cac=1&wft=1&wvw=1&nlf=1&rst=1&shp=1&clt=1&ftp=1&gfl=1&tns=1&ftc=1&hed=1&ply=1
&asl=1&rtm=1&whl=1&ast=1

Is this too long? Its 346 characters. This would be if every single field got checked on the search page, which realistically will never happen (but it could). And I would like to keep it a querystring so that they could make the search results a favorite if they wanted to.

it depends on how you want to interpet the parameter value false,

if you want only matches to false, then ok
if false means don't search, then the coalesce does not return correct results, so you should input NULL instead of false.

if assumed correctly on the meaning of false, then use my suggestion

I also suggest this bedtime reading:

http://www.sommarskog.se/index.html

The papers on Arrays and Dynamic Search Conditions. It might not be a magic bullet, but it's good information, and can save you testing techniques that have already been tried before.

quote:Originally posted by abradley81

This is the max URL what would be pass via querystring:

view.aspx?ars=0,0,0,0&npr=0&xpr=10000001&prt=2&lvn=1&dng=1&fam=1&den=1&rec=1&hth=1&ffl=1
&eik=1&mbt=1&wlk=1&air=1&gfa=1&frp=1&gsr=1&ref=1&dsw=1&wdr=1&wsf=1&ctt=1&wdf=1
&dkp=1&htb=1&pol=1&lsc=1&lwn=1&bsf=1&gwl=1&ufx=1&alm=1&wrt=1&pbt=1&ptr=1&prt=1
&cac=1&wft=1&wvw=1&nlf=1&rst=1&shp=1&clt=1&ftp=1&gfl=1&tns=1&ftc=1&hed=1&ply=1
&asl=1&rtm=1&whl=1&ast=1

Is this too long? Its 346 characters. This would be if every single field got checked on the search page, which realistically will never happen (but it could). And I would like to keep it a querystring so that they could make the search results a favorite if they wanted to.

Sounds like you have 45 checkboxes, not 72. Again, the answer would be it is too long if you feel it is too long. Even going back to IE4.0, you can go over 1000 characters in the querystring, and even in the favorites.

For fun, add this to your favorites and to your address bar (after removing the carraige returns I added for formatting in the forum). It won't work, but it gets stored.

a01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901234567890123456789
012345678901234567890123456789012345678901234567890123456789a

That is 1102 characters, and they even add the http:\ and the at the front and end for you.

So, if your concern is the length of the querystring, then I suggest you live with the long list and the static but large stored procedure.

Good Luck!

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by abradley81</i><br /><br />This is the max URL what would be pass via querystring:<br /><br />view.aspx?ars=0,0,0,0&npr=0&xpr=10000001&prt=2&lvn=1&dng=1&fam=1&den=1&rec=1&hth=1&ffl=1<br />&eik=1&mbt=1&wlk=1&air=1&gfa=1&frp=1&gsr=1&ref=1&dsw=1&wdr=1&wsf=1&ctt=1&wdf=1<br />&dkp=1&htb=1&pol=1&lsc=1&lwn=1&bsf=1&gwl=1&ufx=1&alm=1&wrt=1&pbt=1&ptr=1&prt=1<br />&cac=1&wft=1&wvw=1&nlf=1&rst=1&shp=1&clt=1&ftp=1&gfl=1&tns=1&ftc=1&hed=1&ply=1<br />&asl=1&rtm=1&whl=1&ast=1 <br /><br />Is this too long? Its 346 characters. This would be if every single field got checked on the search page, which realistically will never happen (but it could). And I would like to keep it a querystring so that they could make the search results a favorite if they wanted to.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Why not use form fields? That's some evil-looking URL <img src='/community/emoticons/emotion-1.gif' alt='' />

I'd like to go with the querystring so they can bookmark it. Realistically they are not going to check all 50 checkboxes, probably just a hand full.

joechange,

According to:
http://www.sommarskog.se/dyn-search.html#dynintro andhttp://www.amazon.com/gp/product/00...774880/ref=sr_1_1/102-7821700-3296955?ie=UTF8 the sp_executesql route seems like a feasible method. Being that it reuses execution plans, would this be a better idea than always querying 50 fields?

Would this actually criple my server?