SQL Server Performance

Performing multi criteria search

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by proach, Feb 14, 2007.

  1. proach New Member

    Hello there!<br /><br />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.<br /><br />PROBLEM<br /><br />** I need to implement a multi criteria search across multiple tables (~4).<br />** Search routine is implemented as a stored procedure that takes search criteria as input parameters <br />** Any number of search criteria can be NULL, thus we are looking for dynamic queries that can adjust accordingly<br />** The task is performance critical<br />** We run SQL Server 2005<br /><br />IDEAS<br /><br /> 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<br /><br /> <pre id="code"><font face="courier" size="2" id="code"><br /> --inside one of the joins<br /> INNER JOIN SomeTable st ON st.id = basic_table.id AND<br /> st.field_name = COALESCE(@search_param,st.field_name)<br /> </font id="code"></pre id="code"><br /> <br /> 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. <br /><br /> 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.<br /><br />Could anyone provide any ideas concernig this kind of task?
  2. Adriaan New Member

    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)))
  3. proach New Member

    @Adriaan<br /><br />Thank you. This is exactly what I've been thinking about for the last 5 minutes. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />I am going to try this right now.
  4. proach New Member

    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)))

  5. Adriaan New Member

    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.
  6. proach New Member

    Another question here...

    Would using a view be a good alternative to a super huge query with tons of joins?


    Thanx
  7. Adriaan New Member

    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.
  8. jezemine New Member

  9. proach New Member

    @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.

Share This Page