Is there a way to parameterize this or something? 1

[silverspecv]

I have a really slow query on a web page that I'd like to speed up. Basically, this search page accepts a list of product numbers and has to search a table where that value could be present in any of 8 fields, so the more products in the list, it gets very expensive, because it's using OR on 8 fields then 16 fields, then 24 fields, etc.. it is not uncoming to have 10 items in the list Right now I have it doing something like this.. Would this be a case for a stored procedure where I just pass it a list like ('asdf','qwer','zxcv') and it can do all the below faster?

select count(*)
from sales
where (1=0 or prod1='asdf' or prod2='asdf' or prod3='asdf')
or (1=0 or prod1='qwer' or prod2='qwer' or prod3='qwer')
or (1=0 or prod1='zxcv' or prod2='zxcv' or prod3='zxcv')

I know it's ugly, but it's what I've got.. I've never used a stored procedure, so if that's the answer I'll learn it, but if there's some kind of case statement or some slick parameterization, that would be great.

 

[vongrunt]

1=0 indicate "lazy" dynamic SQL (damn, what went wrong with join/split/implode/explode functions?).
prod1 - prodN indicate lazy database design. Here we can talk about pros and cons of 1NF and blah but you didn't asked for that so...

This query is functionally equal to:

select count(*)
from sales
where prod1 IN ('asdf','qwer','zxcv') 
	or prod2 IN ('asdf','qwer','zxcv') 
	or prod3 IN ('asdf','qwer','zxcv')

A little bit easier to build statement, nothing else.

Suppose that first 8-column search returned no results. Does next 16-column query include these 8 columns? With WHERE condition you posted (all conditions are OR), this would be a waste. Repeat next query only over columns 9-16.

What is the purpose of SELECT COUNT(*) anyway? Using query to tell user "45 rows found" - and repeating query with actual data after that - is not good. IMO there are many similar tinywiny things web developers aren't aware of.

You can use server-side dynamic SQL (exec/sp_executesql), though there is a great chance this won't help much.

Of course, it is possible to move entire logic into stored procedure. This sproc should run first query, check for @@ROWCOUNT, proceed with second if necessary etc. If you have search variations (AND vs OR), code can bloat a bit.

Finally: if prod1-prodN are string columns, consider full-text indexing.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[silverspecv]

1=0 indicate "lazy" dynamic SQL

You hit the nail on the head with that one, but it is a useful construct

damn, what went wrong with join/split/implode/explode functions?

What does this mean?

This query is functionally equal to:

Your code block is much faster than mine.. I use "in" all the time, so I can't believe I didn't notice it could be used there. What I was hoping for was a construct something like:

where (prod1 or prod2 or prod3) in ('asdf','qwer','zxcf')

Does something like that exist?

What is the purpose of SELECT COUNT(*) anyway?

It's so that I don't accidentally read 100,000 records while I'm developing The real query selects fields into a recordset

IMO there are many similar tinywiny things web developers aren't aware of.

More harsh truth, but we do what we can.. I probably barely use 1% of what sql server is capable of

Finally: if prod1-prodN are string columns, consider full-text indexing.

Yep, indexing on prod1-8 plus the above "in" operation has the query down to a svelt 4-5 seconds, wholly acceptable..

Thanks!

 

[vongrunt]

>> damn, what went wrong with join/split/implode/explode functions?
> What does this mean?

Something like this (VB/ASP):

Dim aWhere: aWhere = Array( "foo=1", "bar=2" )
response.write Join( aWhere, " OR ")

Looks like overkill, though with some functions/classes developed for building dynamic SQL statements this becomes very handy.


> where (prod1 or prod2 or prod3) in ('asdf','qwer','zxcf')
> Does something like that exist?

AFAIK no . That's one of problems with repeatable columns (prod1-N).


> It's so that I don't accidentally read 100,000 records while I'm developing

Problem is that COUNT(*) itself can be heavy... not always and definitely not like actual SELECT but still measurable. Here TOP clause and SET ROWCOUNT may help.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]