March 7, 2012 at 11:35 am
I have a group of tables that represent people and various profile information. I am working on a multi parameter, multi-value search criteria. I need to come up with a way in order to filter City, State, and Zip such that:
1) The user can provide more than one entry (of city/state/zip)
2) There only needs to be at least one data point filled out in each entry
3) Anything that is blank should not be filtered on (e.g. if state is blank, should not have a filter on state).
The best thing I could come up with in T-SQL is a where/join on clause kind of like:
select P.Person
from Person P
JOIN @VariableTable VT
ON IsNull(TV.City,P.City) = P.City AND IsNull(TV.State,P.State) = P.State
AND IsNull(TV.Zip,P.Zip) = P.Zip
With the following indexes (State is a char(2), Zip is a char(10), and City is a varchar(50)):
CREATE INDEX X1 ON Person (State,City)
CREATE INDEX X2 ON Person (Zip)
As you can imagine this isn't utilizing the indexes correctly. If I provide State, or City & State it works fine. However if I include any combination with Zip it takes forever. I even tried OPTION (RECOMPILE), clearing proc cache, etc. However when I take out the IsNull, it works as expected - except it's no longer meeting business requriement #3.
Is there any logic that I can utilized that would get me the performance of not using IsNull but still meet business requirements? Breaking this out into IF statements isn't an option.
March 7, 2012 at 12:03 pm
This sounds like a classic "catch all query". Read Gail's blog about here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 12:21 pm
I've read Gail's post but it only deals single parameter variables for each value. In this case, there are multiple inputs. Even if I code this in dynamic sql, it still won't address the index issue unless I explicitly list out each city/state/zip combination in the dynamic sql
March 7, 2012 at 12:24 pm
How about some ddl and sample data to work with?
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 12:32 pm
I wonder why the ISNULL doesn't prevent State and City joins from using an index, but ISNULL on the Zip does?
If you remove the State and City joins, and provide a Zip in the table variable, does the query use the Zip index?
Is the data type of Zip in the table variable exactly the same as the data type of Zip in the Person table?
March 7, 2012 at 1:14 pm
Provide us a table definition (so we can create a table similar to yours) plus a script to fill it with some test data. That way we can see for ourselves what is going on and do some testing too.
A remark on your approach even without being able to see your tables/data: if @VariableTable is multivalue, then you can not use an inner join in your query to combine it with the Person table. You'll get duplicate results, i.e. it's functionally incorrect, apart from any performace issues. Instead try a select p.person from Person p and add a where exists () clause to add the filtering.
March 7, 2012 at 3:34 pm
Sorry guys I appreciate your help, but I actually extremely simplified the query quite a bit. Due to confidentiality and privacy concerns I can't give out the exact dml, so I've had to simplify it. Plus this table has about 20 million rows. I've tried to recreate the issue with "pretend" code/objects but I haven't had any luck.
I'll reply back and let you know what I come up with. Thanks Rozema for pointing out the flaw in the logic.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply