Optimal search queries

Question

Following on from my last question http://stackoverflow.com/questions/2788082/sql-server-query-performance, and discovering that my method of allowing optional parameters in a search query is sub optimal, does anyone have guidelines on how to approach this?

For example, say I have an application table, a customer table and a contact details table, and I want to create an SP which allows searching on some, none or all of surname, homephone, mobile and app ID, I may use something like the following:

select *
from application a inner join customer c on a.customerid = a.id
    left join contact hp on (c.id = hp.customerid and hp.contacttype = 'homephone')
    left join contact mob on (c.id = mob.customerid and mob.contacttype = 'mobile')
where (a.ID = @ID or @ID is null)
    and (c.Surname = @Surname or @Surname is null)
    and (HP.phonenumber = @Homphone or @Homephone is null)
    and (MOB.phonenumber = @Mobile or @Mobile is null)

The schema used above isn't real, and I wouldn't be using select * in a real world scenario, it is the construction of the where clause I am interested in. Is there a better approach, either dynamic sql or an alternative which can achieve the same result, without the need for many nested conditionals. Some SPs may have 10 - 15 criteria used in this way

Answer 1

Ok, here we go

OPTION (RECOMPILE)

is a must - otherwise the first query plan is being reused, regardless how the parameters match. Sorry, no real way to do that better.

Besides that - no, sorry. Dynamic SQL can get more efficient (by avoiding the IS NULL alternatives), but you basically have it nailed down if that is not possible.

With Dynamic SQL you basically do not have aline for HP.phonenumber if the HomePhone variable is null ;)

Answer 2

There is no "one size fits all" query approach for this, there are subtle performance implications in how you do this. If you would like to go beyond just making the query return the proper answer, no matter how slow it is, look at this article: Dynamic Search Conditions in T-SQL by Erland Sommarskog. It covers every method and gives PROs and Cons of each method in great detail.

If you can determine a min and a max possible range for your search column, and the search column is NOT NULL, then you can do better than the (@Search IS NULL OR Col=@Search), see this area of the above linked article. However you should read the entire article, there are so many variations that depend on your situation, you really need to learn multiple approaches and when to use them.

Also see this other recent answer: http://stackoverflow.com/questions/2773232/sql-server-2008-conditional-query/2773268#2773268

Answer 3

In your case, the different queries would use the different indexes.

You should define a set of indexes you want to use and write an individual query for each set, replacing OR on indexed fields with UNION ALL:

SELECT  *
FROM    tables
WHERE   A = @ID
        AND (c.Surname = @Surname or @Surname IS NULL)
        AND (HP.phonenumber = @Homphone or @Homephone IS NULL)
        AND (MOB.phonenumber = @Mobile or @Mobile IS NULL)
UNION ALL
SELECT  *
FROM    tables
WHERE   @ID IS NULL
        AND c.Surname = @Surname
        AND (HP.phonenumber = @Homphone or @Homephone IS NULL)
        AND (MOB.phonenumber = @Mobile or @Mobile IS NULL)
UNION ALL
SELECT  *
FROM    tables
WHERE   @ID IS NULL
        AND @Surname IS NULL
        AND (HP.phonenumber = @Homphone or @Homephone IS NULL)
        AND (MOB.phonenumber = @Mobile or @Mobile IS NULL)