SQL Server Performance Forum – Threads Archive

Performing multi criteria search

Hello there! The problem I am facing is probably a very common one but I haven’t found any elegant solution out there so I decided to post this. PROBLEM ** I need to implement a multi criteria search across multiple tables (~4). ** Search routine is implemented as a stored procedure that takes search criteria as input parameters ** Any number of search criteria can be NULL, thus we are looking for dynamic queries that can adjust accordingly ** The task is performance critical ** We run SQL Server 2005 IDEAS The first approach I took is a very basic SELECT statement with tons of INNER JOINS. The problem with NULL search criteria is solved by using COALESCE statement like this <pre id="code"> –inside one of the joins INNER JOIN SomeTable st ON st.id = basic_table.id AND st.field_name = COALESCE(@search_param,st.field_name) </pre id="code"> Now this works all right. Kind of <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. I have two problems with this. First of, users that match multiple search criteria get duplicated in the result set. Last but not least, I think there’s something really wrong with this whole approach since I have a total of 6 joins in my SELECT to get the dataset I need and perform the search. I am considering performing some tricky filtering that can give me good performance. I was looking into Common table expressions hoping to build a nested WITH that will filter records and return the desired dataset in the outermost SELECT, but as it turned out, you cannot use nested WITH so I am stuck for right now. Could anyone provide any ideas concernig this kind of task?

Move the filter criteria into a WHERE clause. This keeps the JOINs tidy. If the filter criteria are for tables from which you are not returning columns, then remove those tables from the FROM clause and turn each JOIN into a subquery like this: WHERE ((@search_param IS NULL) OR (basic_table.id IN (SELECT st.id FROM SomeTable st WHERE st.field_name = @search_param)))

@Adriaan Thank you. This is exactly what I’ve been thinking about for the last 5 minutes. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I am going to try this right now.

quote:Originally posted by Adriaan Move the filter criteria into a WHERE clause. This keeps the JOINs tidy. If the filter criteria are for tables from which you are not returning columns, then remove those tables from the FROM clause and turn each JOIN into a subquery like this: WHERE ((@search_param IS NULL) OR (basic_table.id IN (SELECT st.id FROM SomeTable st WHERE st.field_name = @search_param)))

Why do you ommit using COALESCE instead of checking for a NULL value? How about WHERE (basic_table.id IN (SELECT st.id FROM SomeTable st WHERE st.field_name = COALESCE(@search_param,st.field_name)))

If you have an index on st.field_name, the COALESCE call causes SQL Server to do a table scan, which you want to avoid. Drop the COALESCE call, and SQL Server can do an index seek, which is what you want for best performance.

Another question here… Would using a view be a good alternative to a super huge query with tons of joins?
Thanx

SQL Server doesn’t mind super huge queries. If you’re using the same complex query over and over again, then a view might make your life as a developer easier. Whatever you’re comfortable with.

there’s a good essay on dynamic search conditions here:
http://www.sommarskog.se/dyn-search.html
www.elsasoft.org

@Adriaan, @jezemine Thank you guys for prompt replies. I am really liking this forum for this. I am sure SQL Server doesn’t mind huge queries. My concern is the productivity.

]]>