Why this SQL is very slow , when I select old dates?

 THIS SQL RUN IN: 0,60 SECONDS:

    SELECT VALUE FROM OPCDR3 WHERE AREA_ID = 2 AND DTH >= '2007-09-10 06:00:00' AND DTH <= '2007-09-10 07:00:00'

THIS SQL RUN IN: 2 MINUTES:
    (the difference in this sql is date = february and the other sql is september.

    SELECT VALUE FROM OPCDR3 WHERE AREA_ID = 2 AND DTH >= '2007-02-10 06:00:00' AND DTH <= '2007-02-10 07:00:00'

OBS: I Have index in DTH and AREA_ID

THANKS!

Roger,

<< Why this SQL is very slow , when I select old dates? >>

Could you post the query execution plan for the slow query ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

OBS: the TABLE OPCDR3.dat = 186 MB and OPCDR3.IDX = 469 MB


================================================================================
SQL statement (Executed with 4.25 Build 4)
================================================================================

SELECT VALUE FROM OPCDR3 WHERE AREA_ID = 2 AND DTH >= '2007-02-10 06:00:00' AND
DTH <= '2007-02-10 07:00:00'

Tables Involved
---------------

OPCDR3 (OPCDR3) table opened shared, has 1440793 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

The expression:

AREA_ID = 2 AND DTH >= '2007-02-10 06:00:00' AND DTH <= '2007-02-10 07:00:00'

has been rewritten and is PARTIALLY-OPTIMIZED, covers 129636 rows or index keys,
costs 19263909 bytes, and will be applied to the OPCDR3 table (OPCDR3) before
any joins

================================================================================
>>>>> 4 rows affected in 10,125 seconds
================================================================================

Roger

This difference is really weird. But my suggestion will be use between
instead of >= and <=. I´ve changed my "style" of build SQL statement with
dates and it really speeds up the things.

SELECT VALUE FROM OPCDR3 WHERE AREA_ID = 2 AND DTH BETWEEN '2007-02-10
06:00:00' AND
'2007-02-10 07:00:00'

Um abraço....(ops. Regards)

Eduardo

Roger,

<< The expression:

AREA_ID = 2 AND DTH >= '2007-02-10 06:00:00' AND DTH <= '2007-02-10
07:00:00'

has been rewritten and is PARTIALLY-OPTIMIZED, covers 129636 rows or index
keys, costs 19263909 bytes, and will be applied to the OPCDR3 table (OPCDR3)
before any joins >>

Try using parentheses around the two expressions.  It looks like the ANDs
without them is confusing the optimizer, which processes the expressions in
a left-to-right manner.

(AREA_ID = 2) AND ((DTH >= '2007-02-10 06:00:00') AND (DTH <= '2007-02-10
07:00:00'))

--
Tim Young
Elevate Software
www.elevatesoft.com