SQL Server Performance

Where clause help please

Discussion in 'Performance Tuning for DBAs' started by kpayne, Nov 9, 2006.

  1. kpayne New Member

    Hi. I have a lot of procedures that use optional parameters. With the optional parameters, the where clause usually ends up looking like:


    where
    ((@MemberID is null) or (h.Member_ID = @MemberID))

    The problem is that the query processor chokes on this logic and I end up getting a sort operation that consumes ~70% of the query time and the query runs for 2 minutes.

    If I replace the where clause with @MemberID = 1111111, the query returns in < 1 second.

    I can post some code if you need a to see it, but I was more concerned with how to structure similar where clauses in many different queries so the query processor interprets it better.

    Thanks

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  2. mmarovic Active Member

    Better create separate procedure for each case (when you want to search by memberId and when you don't). If it is generic search procedure with many optional parameters then you may consider dynamic sql. In company I worked for template runner was developed that transformed such request into big number of stored procedure and at the same time generated vb code for calling them. Now they probably generate c# code. Anyway, that may be too much hasle for you, so better search for Erland Somarskog article about dynamic sql.
  3. kpayne New Member

    quote:Originally posted by mmarovic

    Better create separate procedure for each case (when you want to search by memberId and when you don't). If it is generic search procedure with many optional parameters then you may consider dynamic sql. In company I worked for template runner was developed that transformed such request into big number of stored procedure and at the same time generated vb code for calling them. Now they probably generate c# code. Anyway, that may be too much hasle for you, so better search for Erland Somarskog article about dynamic sql.

    Thanks. I ended up splitting it up into multiple if..else blocks. I know that this is not optimal, but it was good enough to get the procedures 'out the door'. For the next revision I will either create separate procedures or maybe table-valued functions.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  4. Roji. P. Thomas New Member

  5. kpayne New Member

    quote:Originally posted by Roji. P. Thomas

    Have a look at this article.
    http://www.sommarskog.se/dyn-search.html

    Roji. P. Thomas
    http://toponewithties.blogspot.com


    I wish I could Roji but our imaginary network admin has the worlds largest database of firewalled addresses. If you could copy and paste a sentence or two I might be able to read the article out of google's cache.

    Thanks!

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  6. FrankKalis Moderator

    Here you go...<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Introduction<br />A very common requirement in an information system is to have a function (or several functions) where the users are able to search the data by selecting freely among many possible criterias. This is a tough challenge, because not only must you produce the desired output, but you must also keep the response time with in acceptable limits, at least for common searches. And on top of all, the code must be maintainable, so it can meet new needs and requirements.<br /><br />In this text I will look at various techniques to solve this problem. There are two main tracks: dynamic SQL and static SQL. As soon as the number of possible search conditions is more than just a handful, dynamic SQL is the most effective solution, in terms of performance, development and maintenance. However, on SQL 2000 and earlier, you need to give the users direct SELECT permissions to the involved tables, and this is far from always permissible. In such case, you must stick to static SQL, or use a hybrid solution based on a view or a user-defined function. On SQL 2005, there are possibilities to circumvent the permissions issue.<br /><br />I will first look at using dynamic SQL, and try to point out some traps that you should avoid. I will then cover techniques for static SQL to give you a set of methods and tricks that you can combine to implement the search functions you have been assigned. Finally, I will present a hybrid solution that uses both dynamic and static SQL.<br /><br />This text applies to all versions of SQL Server from SQL 7 and on. <br /><br />Here is a table of contents:<br /><br /> Introduction<br /> The Case Study: Searching Orders<br /> The Northgale Database<br /> Dynamic SQL<br /> Introduction<br /> Using sp_executesql<br /> Using the CLR<br /> Using EXEC()<br /> Static SQL<br /> Introduction<br /> x = @x OR @x IS NULL<br /> Using IF statements<br /> Umachandar's Bag of Tricks<br /> Using Temp Tables<br /> x = @x AND @x IS NOT NULL<br /> Handling Complex Conditions<br /> A Hybrid Solution – Using both Static and Dynamic SQL<br /> Conclusion<br /> Feedback and Acknowledgements<br /> Revision History<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hey, your admin is even better than ours. Good luck! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page