Re: Tuning SQL statements

Mary E. Hunt (mary_at_mitra.com) wrote:
: Can someone please point me to a good reference that has guidelines to
: write SQL statements. I know for instance that you order the tables
: from biggest to smallest in the FROM part of the statement.
 

: Does it make a difference the order of the conditions in the where
: clause? What should go first etc... If someone could either post
: something or tell me where to find info about this I would appreciate
: it.
 

: I would like to give my programmers these guidelines for when they
: write SQL statements in their applications.
 

: Thanks in advance.
 

: Mary Hunt

Its hard to generalize how to optimize all queries but heres some things to look for.

The Query parser operates in reverse order, that is, the last items are executed first. So a good idea is to place the most restrictive elements of your where cluse at the bottom. If you have several where conditions, then the one that will 'weed out' the most records should go last, etc.

I haven't found that hte order of the tables makes much difference, for this you should check the EXPLAIN PLAN table for order. If you get the same table being scanned multiple times, then you should juggle the orders, or more probably, restructure the whole statement, and see if this makes a difference.

The other thing to do with EXPLAIN PLAN is to check that the tables in your query are being accessed efficiently... That is try to eliminate FULL TABLE SCANs and get everything down to ROW ACCESS or INDEXED access. This can be tricky but worth it.

Hope it helps...
Randy

--
    ..uu.                                     ----------------------
  .?$" '?i     .                              I  Randy DeWoolfson  I
 .T^M  ._at_"    d9    .     f   ,.un.  b,    i  I--------------------I
 "  Z :#"    M `8   U    <  .dP"``"# `M   _at_"  I  randyd_at_cais.com   I
    &H?`    Xl _R   $5.  $  ?*    _at_   'P,#"   I--------------------I
  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I        ,\//.       I
    &  'M ,P    `E  M   "$  Mux~      n!`     I        |o o|       I
   dk   `h"       ' j     " y"       *~       I====oOO==(_)==Ooo===I