which is faster and why

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi there,
can anyone please help me out here? Which of those queries would execute
quicker and why

select ord_id from orders where datepart(year,date_ordered) = 2000

or

select ord_id from orders where date_ordered > '31/12/1999' and
date_ordered < '1/1/2001'

I would think it is the second query, since it does not need to go through
the whole record set to find 2000. The search is already limited to a
specific date therefore it performs faster.

Is that correct?
Thanks for your help
Chris
 
The second example would be able to use any index that existed on the
date_ordered field, so I would expect it to be significantly faster if such
an index existed. In the absence of an index, it might still be faster,
because I would expect a comparison to be faster than a function call, but
the difference would not be as great.
 
* The DatePart should be: DatePart("yyyy", [date_ordered])

* Sure, the second one will be much more efficient for the Table with large
number of Records since in the first SQL, the DatePart() function needs to
be execute for each Record before JET can decide whether to select the
Record or not. In particular, if you set an Index on the [date_ordered], it
is likely that JET will be able to use the Index to select the Records which
will make it much faster.

If you don't have the time component (i.e. zero time) for the [date_ordered]
Field, use:

SELECT ord_id
FROM orders
WHERE date_ordered BETWEEN #01/01/2000# AND #12/31/2000#

which looks more logical and does not involve type-casting of string to date
value.
 
Chris said:
Hi there,
can anyone please help me out here? Which of those queries would
execute quicker and why

select ord_id from orders where datepart(year,date_ordered) = 2000

or

select ord_id from orders where date_ordered > '31/12/1999' and
date_ordered < '1/1/2001'

I would think it is the second query, since it does not need to go
through the whole record set to find 2000. The search is already
limited to a specific date therefore it performs faster.

Is that correct?
Click to expand...

Whenever possible the operand on the left side of a comparison should be a
field name, not any sort of expression. The reason is that the database
engine will only be able to utilize an index for the search when this is the
case.

If date_ordered has an index on it your second example will be able to
locate those records MUCH faster than if it has to scan the entire table
checking each value. As soon as you have an expresson on the left side of
the comparison you are guaranteed that a table scan will be required. Of
course, if the fields being searched don't have indexes on them in the first
place it likely won't make any difference. In the case of large tables some
database engines would create an index on the fly in these cases so the
search based on a field name only would likely still be the better way to
go.

Testing for the results of an expression is often the easiest to create and
understand, but it is seldom the best way to write the query.
 
Back
Top