Post reply

Writing efficient queries

  • August 17, 2006 at 12:21 pm

    #114205

    I have to look at some queries and make sure they are as efficient as possible.   What kinds of things do I look for, besides indexes?  Is there something about indexes that I should look at?  What about the number of joins?  Can that be a problem?  If so, what happens when the data you are retrieving needs to come from 5 or 6 different tables?  How do you get around that?

    Any help would be greatly appreciated here.

     

    Thanks,

    Barry Ferrell

  • August 17, 2006 at 12:48 pm

    #655521

    If you want to start by looking at the SQL, then some warning signs to look for:

    - Use of sub-SELECTS within the main SELECT. Creates cursor-like sluggishness

    - Use of user-defined functions (UDF's) where the UDF performs an additional SELECT within the function. Again, cursor like.

    - Use of IN() or NOT IN() sub-queries that could be better expressed as EXISTS/NOT EXISTS

    - Use of functions in any columns involved in the JOIN or WHERE parts - function use may prevent index usage

    - use of UNION on mutually exclusive resultsets, where UNION ALL should be used

    - use of DISTINCT where it's not required, or where it's been used to hide an issue with duplicate rows.

     

  • August 18, 2006 at 10:45 am

    #655708

    whilst i appreciate your request, and as I often say everyone has to start somewhere, if you're asking this question you're probably not the best person to do the job!

    The main question is why do you consider the queries to be poor? Don't try to change code which is working well or to try to prove you can do better for the sake of it.

    As a first stop .. I run the query in QA to capture i/o stats and examine the query plan looking for table/index scans and excessive scanning in loops. I also time the proc running from cache and creating a new plan( clearing the cache first ). Only if the proc has excessive i/o, scans, duration do I start looking at the code.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply