Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Roger Oliveira |
Mon, Sep 10 2007 10:28 AM | Permanent Link |
Brazil | 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! |
Mon, Sep 10 2007 10:49 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Sep 10 2007 10:58 AM | Permanent Link |
Brazil | 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 ================================================================================ |
Mon, Sep 10 2007 12:20 PM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Tue, Sep 11 2007 2:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, May 7, 2024 at 02:40 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |