April 6, 2011 at 6:53 am
As most know, I'm not a dba though doing more sql development then I'm used to doing.
I have a web form that has 2 drop down boxes, the user can select 1 or both and click go, and results well be returned from the sql table based on their selection.
Right now my query is like;
select company, description from tblResellers where location like '%dropdown1selection%' or type like '%dropdown2selection%'
this query is returning results, however is this the best way? Should a different query run if the user selected something in both dropdowns?
Should I use a case statement in my query?
my dropdowns have these values
Please select a company
microsoft
Dell
HP
dropdown 2
please select company type
software
consulting
hardware
what would be the best way to create my search query based off my 2 dropdowns and at least one has to be selected
April 6, 2011 at 7:06 am
You're bound to get a scan unless you're willing to move away from LIKE '%<search>%'
Now the real question is how fast do you need this to go and how fast is this going now?
April 6, 2011 at 7:08 am
remove like isn't an problem, my first crack at is had { = }, as for performance, quicker the better. 🙂
April 6, 2011 at 7:13 am
Well the real question is do you use string litterals or do you have ids for all the values in both dropdowns?
I haven't done any testing on that type of query recently, but here's what I'd test :
- Index1 Location, Type Index2, Type, Location. That way you cover all bases.
Then I'd test the or query you have now VS doing 2 queries + union all (1 condition in each).
April 6, 2011 at 7:32 am
strings, no id's being passed
April 6, 2011 at 7:35 am
Why don't you tables for those 2 columns? The design doesn't seem to make much sense at the moment.
Can you post the tables definitions (create table script)?
April 6, 2011 at 7:38 am
those values aren't used from a table, they are entered into a table on the dataentry screen.
This is for search only against one table, I have to pass 1 or both values into my details table
April 6, 2011 at 7:41 am
Same problem. The design of the UI is wrong, table design is wrong and there's not much you can do to tune this except maybe full text indexing.
Again my 2 covering indexes might help but you'll need to test 'em out.
April 6, 2011 at 7:46 am
so what your saying, due to I need to search against 1 table for 2 values won't work?
April 6, 2011 at 7:48 am
It'll work, but it'll never be very fast and there's not much we can do to help.
April 6, 2011 at 7:50 am
again, performance isn't an issue right now, the table will be at most 50 records at any given time
April 6, 2011 at 7:52 am
In that case a scan will never be a problem.
April 6, 2011 at 8:05 am
I know that, i'm looking on how to correctly create my proc for this scenario instead of having several inline queries in my code
April 6, 2011 at 8:10 am
CREATE PROCEDURE dbo.SearchResellers
@Location VARCHAR(100)--100??
, @Type VARCHAR(100)--100?
AS
SET NOCOUNT ON
SELECT
company
, description
FROM
dbo.tblResellers
WHERE
location LIKE '%' + @Location + '%'
OR type LIKE '%' + @Type + '%'
--ORDER BY ??
SET NOCOUNT OFF
GO
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply