locked
Simple methods to optimise SQL queries RRS feed

  • Question

  • Hi,  please excuse me if this seems like a daft or badly formed question.  

    A colleage mentioned that when constructing the where clause in a query that it was important what order the criteria were entered.  They weren't sure themsleves but the idea was that starting from the bottom of the query you should add the criteria that will have the effect of reducing the data the most and then work 'up' the query back to the from clause. 

    As a dodgy example, if you have a staff table and you want to run a  specific query on all staff over 75 that are male, then:

    where gender = "male"
    and age >- 75

    The reasoning being that the age criteria would reduce the size of the data being worked on more than the gender criteria.

    It's been a while since I've had to look at intermediate tables etc but i'm pretty sure this suggestion will not make any difference to the performance of a query.

    Thanks,

    Matt 

    Monday, September 18, 2006 9:03 AM

All replies

  • You are right, your colleague is wrong. It's the optimiser that makes the decision on what to filter by - you can't "force" it by specifying criteria (or joins) in a certain order.

     

    Monday, September 18, 2006 10:56 AM
  • Thanks Paulo999.

    Monday, September 18, 2006 2:49 PM