Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    May 2009
    Posts
    56

    Unanswered: using BETWEEN keyword on dates can sometimes be slow

    Hey guys, I am using the below query and trying to figure out why it is running slow.

    Select svcDate, DrugName, sum(RxCount)
    from drugtable
    where svcDate between to_date('2010-12-01', 'YYYY-MM-DD') and to_date('2011-01-02', 'YYYY-MM-DD')
    and DrugName in ('NASACORT','FLONASE')
    group by svcDate, DrugName

    The above query takes 10 minutes to run.

    Select svcDate, DrugName, sum(RxCount)
    from drugtable
    where svcDate between to_date('2010-12-01', 'YYYY-MM-DD') and to_date('2010-12-10', 'YYYY-MM-DD')
    and DrugName in ('NASACORT','FLONASE')
    group by svcDate, DrugName

    The above query takes 30 seconds to run.

    Select svcDate, DrugName, sum(RxCount)
    from drugtable
    where svcDate in (to_date('2010-12-01', 'YYYY-MM-DD'),
    to_date('2010-12-02', 'YYYY-MM-DD')... to_date('2011-01-02', 'YYYY-MM-DD')
    and DrugName in ('NASACORT','FLONASE')
    group by svcDate, DrugName

    The dates are all date from 12-01-2010 thru 01-02-2011

    The above query takes 1 minute to run.

    It appears to me that the top query isn't using the index on the date field, but I don't why or the best way to fix it. Any help is appreciated. Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post FORMATTED EXPLAIN PLAN for all 3 SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from following 4 SQL

    Select count(*)
    from drugtable
    where svcDate between to_date('2010-12-01', 'YYYY-MM-DD') and to_date('2011-01-02', 'YYYY-MM-DD')
    and DrugName in ('NASACORT','FLONASE')
    group by svcDate, DrugName;

    Select count(*)
    from drugtable
    where svcDate between to_date('2010-12-01', 'YYYY-MM-DD') and to_date('2010-12-10', 'YYYY-MM-DD')
    and DrugName in ('NASACORT','FLONASE')
    group by svcDate, DrugName;

    Select count(*)
    from drugtable
    where svcDate in (to_date('2010-12-01', 'YYYY-MM-DD'),
    to_date('2010-12-02', 'YYYY-MM-DD')... to_date('2011-01-02', 'YYYY-MM-DD')
    and DrugName in ('NASACORT','FLONASE')
    group by svcDate, DrugName;

    Select count(*) from drugtable;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    are you running rule based or cost based and is there an index on drugname?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2009
    Posts
    56
    There is an index on drugname. I don't if it is rule based or cost based. I'll get in touch with our DBA and ask him.

    I'll post the PLANS later today.

  6. #6
    Join Date
    May 2009
    Posts
    56
    Ok, the SQL I posted earlier wasn't exactly what the SQL was. Below is the Real SQL and PLANS. I hope the PLAN is formatted well enough.

    This Query Takes 1 minute to run.

    select a11.DATE_OF_SVC DATE_OF_SVC,
    a12.DRUG_NAME DRUG_NAME,
    sum(a11.TRANSACTION_CNT) RxCount
    from T_NDM_DATA a11
    join T_DRUG_NDC_WK a12
    on (a11.DRUG_GID = a12.DRUG_GID)
    where (a11.DATE_OF_SVC in (To_Date('2011-01-02 00:00:00', 'YYYY-MM-DD HH24:MIS'), To_Date('2011-01-01 00:00:00', 'YYYY-MM-DD HH24:MIS'), ...nTo_Date('2010-12-01 00:00:00', 'YYYY-MM-DD HH24:MIS'))
    and a12.DRUG_NAME in ('NASACORT', 'FLONASE'))
    group by a11.DATE_OF_SVC,
    a12.DRUG_NAME

    Code:
    ---------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name             | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
    ---------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                  |    47 |  1739 |    49 |       |       |        |      |            |
    |   1 |  PX COORDINATOR                       |                  |       |       |       |       |       |        |      |            |
    |   2 |   PX SEND QC (RANDOM)                 | :TQ10001         |    47 |  1739 |    49 |       |       |  Q1,01 | P->S | QC (RAND)  |
    |   3 |    HASH GROUP BY                      |                  |    47 |  1739 |    49 |       |       |  Q1,01 | PCWP |            |
    |   4 |     TABLE ACCESS BY LOCAL INDEX ROWID | T_NDM_DATA       |    33 |   561 |    48 |       |       |  Q1,01 | PCWC |            |
    |   5 |      NESTED LOOPS                     |                  |   783 | 28971 |    48 |       |       |  Q1,01 | PCWP |            |
    |   6 |       BUFFER SORT                     |                  |       |       |       |       |       |  Q1,01 | PCWC |            |
    |   7 |        PX RECEIVE                     |                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |   8 |         PX SEND BROADCAST             | :TQ10000         |       |       |       |       |       |        | S->P | BROADCAST  |
    |   9 |          INLIST ITERATOR              |                  |       |       |       |       |       |        |      |            |
    |  10 |           TABLE ACCESS BY INDEX ROWID | T_DRUG_NDC_WK    |    23 |   460 |     2 |       |       |        |      |            |
    |  11 |            BITMAP CONVERSION TO ROWIDS|                  |       |       |       |       |       |        |      |            |
    |  12 |             BITMAP INDEX SINGLE VALUE | X2_T_DRUG_NDC_WK |       |       |       |       |       |        |      |            |
    |  13 |       PX PARTITION RANGE INLIST       |                  |       |       |       |KEY(I) |KEY(I) |  Q1,01 | PCWC |            |
    |  14 |        BITMAP CONVERSION TO ROWIDS    |                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |  15 |         BITMAP INDEX SINGLE VALUE     | X3_T_NDM_DATA_2  |       |       |       |KEY(I) |KEY(I) |  Q1,01 | PCWP |            |
    ---------------------------------------------------------------------------------------------------------------------------------------
    Last edited by coder_t2; 01-06-11 at 22:07.

  7. #7
    Join Date
    May 2009
    Posts
    56
    This query takes 10 minutes.

    select a11.DATE_OF_SVC DATE_OF_SVC,
    a12.DRUG_NAME DRUG_NAME,
    sum(a11.TRANSACTION_CNT) RxCount
    from T_NDM_DATA a11
    join T_DRUG_NDC_WK a12
    on (a11.DRUG_GID = a12.DRUG_GID)
    where (a11.DATE_OF_SVC between To_Date('2010-12-01', 'YYYY-MM-DD') and To_Date('2011-01-02', 'YYYY-MM-DD')
    and a12.DRUG_NAME in ('NASACORT', 'FLONASE'))
    group by a11.DATE_OF_SVC,
    a12.DRUG_NAME

    Code:
    ------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                | Name             | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                  |     2 |    74 |    46 |       |       |        |      |            |
    |   1 |  PX COORDINATOR                          |                  |       |       |       |       |       |        |      |            |
    |   2 |   PX SEND QC (RANDOM)                    | :TQ10002         |     2 |    74 |    46 |       |       |  Q1,02 | P->S | QC (RAND)  |
    |   3 |    HASH GROUP BY                         |                  |     2 |    74 |    46 |       |       |  Q1,02 | PCWP |            |
    |   4 |     PX RECEIVE                           |                  |     2 |    74 |    46 |       |       |  Q1,02 | PCWP |            |
    |   5 |      PX SEND HASH                        | :TQ10001         |     2 |    74 |    46 |       |       |  Q1,01 | P->P | HASH       |
    |   6 |       HASH GROUP BY                      |                  |     2 |    74 |    46 |       |       |  Q1,01 | PCWP |            |
    |   7 |        TABLE ACCESS BY LOCAL INDEX ROWID | T_NDM_DATA       |     2 |    34 |    45 |       |       |  Q1,01 | PCWC |            |
    |   8 |         NESTED LOOPS                     |                  |    40 |  1480 |    45 |       |       |  Q1,01 | PCWP |            |
    |   9 |          BUFFER SORT                     |                  |       |       |       |       |       |  Q1,01 | PCWC |            |
    |  10 |           PX RECEIVE                     |                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |  11 |            PX SEND BROADCAST             | :TQ10000         |       |       |       |       |       |        | S->P | BROADCAST  |
    |  12 |             INLIST ITERATOR              |                  |       |       |       |       |       |        |      |            |
    |  13 |              TABLE ACCESS BY INDEX ROWID | T_DRUG_NDC_WK    |    23 |   460 |     2 |       |       |        |      |            |
    |  14 |               BITMAP CONVERSION TO ROWIDS|                  |       |       |       |       |       |        |      |            |
    |  15 |                BITMAP INDEX SINGLE VALUE | X2_T_DRUG_NDC_WK |       |       |       |       |       |        |      |            |
    |  16 |          PX PARTITION RANGE ITERATOR     |                  |       |       |       |   366 |   371 |  Q1,01 | PCWC |            |
    |  17 |           BITMAP CONVERSION TO ROWIDS    |                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |  18 |            BITMAP AND                    |                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |  19 |             BITMAP INDEX SINGLE VALUE    | X3_T_NDM_DATA_2  |       |       |       |   366 |   371 |  Q1,01 | PCWP |            |
    |  20 |             BITMAP CONVERSION FROM ROWIDS|                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |  21 |              SORT ORDER BY               |                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |  22 |               INDEX RANGE SCAN           | X5_T_NDM_DATA_2  | 42498 |       |     1 |   366 |   371 |  Q1,01 | PCWP |            |
    ------------------------------------------------------------------------------------------------------------------------------------------
    Last edited by coder_t2; 01-06-11 at 22:07.

  8. #8
    Join Date
    May 2009
    Posts
    56
    This query takes about 30 seconds.


    select a11.DATE_OF_SVC DATE_OF_SVC,
    a12.DRUG_NAME DRUG_NAME,
    sum(a11.TRANSACTION_CNT) RxCount
    from T_NDM_DATA a11
    join T_DRUG_NDC_WK a12
    on (a11.DRUG_GID = a12.DRUG_GID)
    where (a11.DATE_OF_SVC between To_Date('2010-12-01', 'YYYY-MM-DD') and To_Date('2010-12-10', 'YYYY-MM-DD')
    and a12.DRUG_NAME in ('NASACORT', 'FLONASE'))
    group by a11.DATE_OF_SVC,
    a12.DRUG_NAME




    Code:
     
    ------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                | Name             | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                  |     2 |    74 |    29 |       |       |        |      |            |
    |   1 |  PX COORDINATOR                          |                  |       |       |       |       |       |        |      |            |
    |   2 |   PX SEND QC (RANDOM)                    | :TQ10002         |     2 |    74 |    29 |       |       |  Q1,02 | P->S | QC (RAND)  |
    |   3 |    HASH GROUP BY                         |                  |     2 |    74 |    29 |       |       |  Q1,02 | PCWP |            |
    |   4 |     PX RECEIVE                           |                  |     2 |    74 |    29 |       |       |  Q1,02 | PCWP |            |
    |   5 |      PX SEND HASH                        | :TQ10001         |     2 |    74 |    29 |       |       |  Q1,01 | P->P | HASH       |
    |   6 |       HASH GROUP BY                      |                  |     2 |    74 |    29 |       |       |  Q1,01 | PCWP |            |
    |   7 |        TABLE ACCESS BY LOCAL INDEX ROWID | T_NDM_DATA       |     2 |    34 |    28 |       |       |  Q1,01 | PCWC |            |
    |   8 |         NESTED LOOPS                     |                  |    40 |  1480 |    28 |       |       |  Q1,01 | PCWP |            |
    |   9 |          BUFFER SORT                     |                  |       |       |       |       |       |  Q1,01 | PCWC |            |
    |  10 |           PX RECEIVE                     |                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |  11 |            PX SEND BROADCAST             | :TQ10000         |       |       |       |       |       |        | S->P | BROADCAST  |
    |  12 |             INLIST ITERATOR              |                  |       |       |       |       |       |        |      |            |
    |  13 |              TABLE ACCESS BY INDEX ROWID | T_DRUG_NDC_WK    |    23 |   460 |     2 |       |       |        |      |            |
    |  14 |               BITMAP CONVERSION TO ROWIDS|                  |       |       |       |       |       |        |      |            |
    |  15 |                BITMAP INDEX SINGLE VALUE | X2_T_DRUG_NDC_WK |       |       |       |       |       |        |      |            |
    |  16 |          PX PARTITION RANGE ITERATOR     |                  |       |       |       |   366 |   367 |  Q1,01 | PCWC |            |
    |  17 |           BITMAP CONVERSION TO ROWIDS    |                  |       |       |       |       |       |  Q1,01 | PCWP |            |
    |  18 |            BITMAP INDEX SINGLE VALUE     | X3_T_NDM_DATA_2  |       |       |       |   366 |   367 |  Q1,01 | PCWP |            |
    ------------------------------------------------------------------------------------------------------------------------------------------
    Last edited by coder_t2; 01-06-11 at 22:07.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please post DDL for all tables involved

    Does the elapsed time duration remain the same regardless of order in which the SQL statements are run?
    In other words, does any data caching contribute to the elapsed times reported?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    May 2009
    Posts
    56
    Yes elapsed time remains the same regardless of order.

  11. #11
    Join Date
    May 2009
    Posts
    56
    CREATE TABLE t_drug_ndc_wk (
    drug_gid NUMBER(10,0) NULL,
    drug_name VARCHAR2(60) NULL
    )


    CREATE UNIQUE INDEX x1_t_drug_ndc_wk
    ON t_drug_ndc_wk (
    drug_gid
    )


    CREATE BITMAP INDEX x2_t_drug_ndc_wk
    ON t_drug_ndc_wk (
    drug_name
    )


    CREATE TABLE t_ndm_data (
    rx_month NUMBER NULL,
    transaction_cnt NUMBER(1,0) NULL,
    svc_provider_id VARCHAR2(15) NULL,
    date_of_svc DATE NULL,
    rx_svc_ref_nbr VARCHAR2(12) NULL,
    fill_nbr NUMBER(2,0) NULL,
    drug_gid NUMBER(10,0) NULL
    )



    CREATE BITMAP INDEX x2_t_ndm_data_2
    ON t_ndm_data (
    ocr_svc_prvdr_id
    )

    CREATE BITMAP INDEX x3_t_ndm_data_2
    ON t_ndm_data (
    drug_gid
    )


    CREATE BITMAP INDEX x4_t_ndm_data_2
    ON t_ndm_data (
    rx_month
    )


    CREATE INDEX x5_t_ndm_data_2
    ON t_ndm_data (
    date_of_svc,
    ocr_svc_prvdr_id,
    rx_svc_ref_nbr,
    fill_nbr
    )

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it is hard to enforce UNIQUE when column can be NULL
    Code:
    SQL> set term on echo on
    SQL> CREATE TABLE t_drug_ndc_wk (
    drug_gid NUMBER(10,0) NULL,
    drug_name VARCHAR2(60) NULL
    );
    
    
    CREATE UNIQUE INDEX x1_t_drug_ndc_wk
    ON t_drug_ndc_wk (
    drug_gid
    );
    
    INSERT INTO t_drug_ndc_wk VALUES (NULL,1);
    INSERT INTO t_drug_ndc_wk VALUES (NULL,1);
      2    3    4  
    Table created.
    
    SQL> SQL> SQL>   2    3    4  
    Index created.
    
    SQL> SQL> 
    1 row created.
    
    SQL> 
    1 row created.
    
    SQL>
    >CREATE UNIQUE INDEX x1_t_drug_ndc_wk
    index above is NOT used which could slow things down.

    >CREATE UNIQUE INDEX x1_t_drug_ndc_wk ON t_drug_ndc_wk (drug_gid)
    >CREATE BITMAP INDEX x3_t_ndm_data_2 ON t_ndm_data (drug_gid)
    Why 2 different index type on same column?
    Why use BITMAP index ?
    Is this OLTP or DW database?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I agree about the bitmap index. Useful in a data warehouse but really slows things down when you are in a transaction system.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  14. #14
    Join Date
    May 2009
    Posts
    56
    It is a Datawarehouse. I don't know why there are two different index types on the same column. Could that be the problem?

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    there is no problem.
    SQL produces desired/correct result set.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •