Path: utzoo!attcan!uunet!tektronix!tekcae!moiram
From: moiram@tekcae.CAX.TEK.COM (Moira Mallison)
Newsgroups: comp.databases
Subject: Re: Is order important in SQL?
Keywords: SQL Unify performance join-query
Message-ID: <2209@tekcae.CAX.TEK.COM>
Date: 9 Nov 88 17:12:53 GMT
References: <344@sunny.UUCP> <229@daitc.daitc.mil>
Reply-To: moiram@tekcae.CAX.TEK.COM (Moira Mallison)
Distribution: na
Organization: Tektronix, Inc., Beaverton,  OR.
Lines: 24

>In article <344@sunny.UUCP>, poage@sunny (Tom Poage) writes:
>>	Is the order of entries in the "select", "from", and 
>>	"where" clauses important in terms of performance?

Jon Krueger responds with a good explanation of QEPs.  And what
he states about order not making a difference is right most of
the time (> 95%), but we had a documented case in which the
ordering of operands made a significant difference as measured
in elapsed time to run the query.

As I recall, the query was a complex join on multiple relations.
Our "naive" user didn't know that ordering makes no difference (:-)
and switched the operands on either side of the equal sign.  That
is,  changed "where a = b" to "where b = a".  We carefully analyzed
the resulting QEPs and could find no clues.

The QEPs are good performance analysis tools and will most frequently
point our where the DBA can make improvements in storage structures
or data base design.  But if the query optimizer is making a really
bad choice, it wouldn't hurt to change the order of operands.  

Moira Mallison
CAX Data Management
Tektronix, Inc