I expose a simple example of what I'm trying to figure it out:
table1:
====
field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
Daniel E. Alvarez
IMS Soluciones Tecnolgicas S.A.
quilate@.kropol.com.ar
Daniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====
> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnolgicas S.A.
> quilate@.kropol.com.ar
>
sql
Friday, March 30, 2012
Optimizing SQL Filtering - Conceptual question
Labels:
conceptual,
database,
example,
expose,
field,
figure,
filtering,
indexedfield2,
indexedfield3,
microsoft,
mysql,
numeric,
optimizing,
oracle,
outtable1field1,
server,
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment