query tuning


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: query tuning

  1. #1
    dhaya Guest

    query tuning


    any way to this query , its very slow.

    SELECT CMC_CLCL_CLAIM.CLCL_ID,
    CMC_SBSB_SUBSC.SBSB_ID,
    CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    CMC_CLCL_CLAIM.CLCL_CUR_STS,
    CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    CMC_CLCL_CLAIM.PDPD_ID,
    CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    CMC_CDML_CL_LINE.CDML_CHG_AMT,
    CMC_CDML_CL_LINE.CDML_ALLOW,
    CMC_CDML_CL_LINE.CDML_SEQ_NO
    into adhocdb..JP_COPAY_ERRORS
    FROM CMC_CLCL_CLAIM

    INNER JOIN CMC_SBSB_SUBSC
    ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK

    INNER JOIN CMC_CDML_CL_LINE
    ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID

    INNER JOIN CMC_CLHP_HOSP
    ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID

    WHERE

    CMC_CDML_CL_LINE.SESE_ID In
    ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    'SPRB','SRBR','SRB','SBDR','SKNU')
    AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >= '07/07/2003'

    AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    AND CMC_CLCL_CLAIM.CLCL_ID IN
    (
    SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    INNER JOIN CMC_CDML_CL_LINE B
    ON A.CLCL_ID = B.CLCL_ID
    WHERE
    A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    )


    The last inner query IN is to get from a detail table rows which have different
    copay amount

    example
    111 will be selected in below case

    claim_id sequence_no copay
    111 1 10.0
    111 2 20.0
    222 1 100.0
    222 2 100.0

    Thank you!
    dhaya


  2. #2
    Rune Bivrin Guest

    Re: query tuning

    "dhaya" <shivdayal@hotmail.com> wrote in
    news:3f416734$1@tnews.web.devx.com:

    >
    > any way to this query , its very slow.
    >


    What are the tables involved indexed on?

    --
    Rune Bivrin
    - OOP since 1989
    - SQL Server since 1990
    - VB since 1991

  3. #3
    KevinV Guest

    Re: query tuning


    Have you ran this in Query Analyzer with 'Show Execution Plan' on?

    There is a lot to tuning queries. The query analyzer execution plan is usually
    a good place to start. Look for the step in the execution plan that has the
    highest cost and start there to look for optimizations. Generally adding
    indexes for certain columns, like columns referenced in joins and where clauses
    is what will improve the query the most. If you see steps that are doing
    table scans then that likely means you need an index. It is also possible
    that your query will just be slow depending on the number of rows of data,
    physical placement of data, log and index database files and the configuration
    of the server.


    "dhaya" <shivdayal@hotmail.com> wrote:
    >
    >any way to this query , its very slow.
    >
    >SELECT CMC_CLCL_CLAIM.CLCL_ID,
    > CMC_SBSB_SUBSC.SBSB_ID,
    > CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    > CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    > CMC_CLCL_CLAIM.CLCL_CUR_STS,
    > CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    > CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    > CMC_CLCL_CLAIM.PDPD_ID,
    > CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    > CMC_CDML_CL_LINE.CDML_CHG_AMT,
    > CMC_CDML_CL_LINE.CDML_ALLOW,
    > CMC_CDML_CL_LINE.CDML_SEQ_NO
    >into adhocdb..JP_COPAY_ERRORS
    >FROM CMC_CLCL_CLAIM
    >
    >INNER JOIN CMC_SBSB_SUBSC
    >ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >
    >INNER JOIN CMC_CDML_CL_LINE
    >ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >
    >INNER JOIN CMC_CLHP_HOSP
    >ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >
    >WHERE
    >
    >CMC_CDML_CL_LINE.SESE_ID In
    > ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    > 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    > 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    > 'SPRB','SRBR','SRB','SBDR','SKNU')
    >AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>= '07/07/2003'

    >AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >AND CMC_CLCL_CLAIM.CLCL_ID IN
    >(
    >SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >INNER JOIN CMC_CDML_CL_LINE B
    >ON A.CLCL_ID = B.CLCL_ID
    > WHERE
    >A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >)
    >
    >
    >The last inner query IN is to get from a detail table rows which have different
    >copay amount
    >
    >example
    >111 will be selected in below case
    >
    >claim_id sequence_no copay
    >111 1 10.0
    >111 2 20.0
    >222 1 100.0
    >222 2 100.0
    >
    >Thank you!
    >dhaya
    >



  4. #4
    dhaya Guest

    Re: query tuning


    I tried replacing IN with OR
    And then looked like the tables are too big so join is slow.
    I am thinking only creating temp tables and indices may work, don't know
    if anything else would work

    have the query and plan below

    SELECT CLCL.CLCL_ID,
    SBSB.SBSB_ID,
    CLCL.CLCL_TOT_CHG,
    CLCL.CLCL_TOT_PAYABLE,
    CLCL.CLCL_CUR_STS,
    CLCL.CLCL_LOW_SVC_DT,
    CLCL.CLCL_HIGH_SVC_DT,
    CLCL.PDPD_ID,
    CDML1.CDML_COPAY_AMT,
    CDML1.CDML_CHG_AMT,
    CDML1.CDML_ALLOW,
    CDML1.CDML_SEQ_NO

    FROM CMC_CLCL_CLAIM CLCL

    INNER JOIN CMC_SBSB_SUBSC SBSB
    ON CLCL.SBSB_CK = SBSB.SBSB_CK

    INNER JOIN CMC_CDML_CL_LINE CDML1
    ON CLCL.CLCL_ID = CDML1.CLCL_ID

    INNER JOIN CMC_CLHP_HOSP CLHP
    ON CLCL.CLCL_ID = CLHP.CLCL_ID

    WHERE

    (
    CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS' OR
    CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS' OR
    CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID= 'ICUS'
    OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID= 'NUR'
    OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID= 'PRBS'
    OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID= 'RB'
    OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID= 'RBD' OR
    CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'
    OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID= 'SBDR'
    OR CDML1.SESE_ID= 'SKNU'
    )

    AND CLCL.CLCL_CL_SUB_TYPE='H'

    AND CLCL.CLCL_ID IN
    (
    SELECT SUB_CDML1.CLCL_ID
    FROM CMC_CDML_CL_LINE SUB_CDML1
    INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    )

    AND CLHP.CLHP_FAC_TYPE = '1'

    AND
    (
    CLHP.CLHP_BILL_CLASS ='E' OR
    CLHP.CLHP_BILL_CLASS ='U' OR
    CLHP.CLHP_BILL_CLASS ='S' OR
    CLHP.CLHP_BILL_CLASS ='3'
    )

    ORDER BY CLCL.PDPD_ID



    ----------

    QUERY PLAN FOR STATEMENT 1 (at line 1).




    STEP 1
    The type of query is INSERT.
    The update mode is direct.
    Worktable1 created for ORDER BY.


    FROM TABLE
    CMC_CLCL_CLAIM
    CLCL
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    CMC_SBSB_SUBSC
    SBSB
    Nested iteration.
    Index : CMCX_SBSB_PRIMARY
    Forward scan.
    Positioning by key.
    Keys are:
    SBSB_CK ASC
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    CMC_CDML_CL_LINE
    CDML1
    Nested iteration.
    Index : CMCX_CDML_PRIMARY
    Forward scan.
    Positioning by key.
    Keys are:
    CLCL_ID ASC
    Using I/O Size 16 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    CMC_CLHP_HOSP
    CLHP
    Nested iteration.
    Index : CMCX_CLHP_SECOND
    Forward scan.
    Positioning by key.
    Keys are:
    CLCL_ID ASC
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    CMC_CDML_CL_LINE
    SUB_CDML1
    EXISTS TABLE : nested iteration.
    Index : CMCX_CDML_PRIMARY
    Forward scan.
    Positioning by key.
    Keys are:
    CLCL_ID ASC
    Using I/O Size 16 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    CMC_CDML_CL_LINE
    SUB_CDML2
    EXISTS TABLE : nested iteration.
    Index : CMCX_CDML_PRIMARY
    Forward scan.
    Positioning by key.
    Keys are:
    CLCL_ID ASC
    Using I/O Size 16 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    Worktable1.


    STEP 2
    The type of query is SELECT.
    This step involves sorting.


    FROM TABLE
    Worktable1.
    Using GETSORTED
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 16 Kbytes for data pages.
    With MRU Buffer Replacement Strategy for data pages.


    Parse and Compile Time 0.
    SQL Server cpu time: 0 ms.
    Server Message: Number 1562, Severity 10
    Server 'aims_dev2', Line 1:
    The sort for Worktable1 is done in Serial
    Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202 apf=0
    total=150202), physical reads: (regular=5584 apf=13331 total=18915), apf
    IOs used=13228
    Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431 apf=11
    total=233442), physical reads: (regular=4573 apf=412 total=4985), apf IOs
    used=209
    Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    apf IOs used=88751
    Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084 apf=0
    total=17084), physical reads: (regular=2117 apf=18 total=2135), apf IOs used=18

    Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170 apf=0
    total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038 apf=0
    total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15
    Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    Total writes for this command: 6

    Execution Time 144.
    SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    (1 row affected)




    "KevinV" <none@none.com> wrote:
    >
    >Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >
    >There is a lot to tuning queries. The query analyzer execution plan is usually
    >a good place to start. Look for the step in the execution plan that has

    the
    >highest cost and start there to look for optimizations. Generally adding
    >indexes for certain columns, like columns referenced in joins and where

    clauses
    >is what will improve the query the most. If you see steps that are doing
    >table scans then that likely means you need an index. It is also possible
    >that your query will just be slow depending on the number of rows of data,
    >physical placement of data, log and index database files and the configuration
    >of the server.
    >
    >
    >"dhaya" <shivdayal@hotmail.com> wrote:
    >>
    >>any way to this query , its very slow.
    >>
    >>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >> CMC_SBSB_SUBSC.SBSB_ID,
    >> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >> CMC_CLCL_CLAIM.PDPD_ID,
    >> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >> CMC_CDML_CL_LINE.CDML_ALLOW,
    >> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>into adhocdb..JP_COPAY_ERRORS
    >>FROM CMC_CLCL_CLAIM
    >>
    >>INNER JOIN CMC_SBSB_SUBSC
    >>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>
    >>INNER JOIN CMC_CDML_CL_LINE
    >>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>
    >>INNER JOIN CMC_CLHP_HOSP
    >>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>
    >>WHERE
    >>
    >>CMC_CDML_CL_LINE.SESE_ID In
    >> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>= '07/07/2003'

    >>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>(
    >>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>INNER JOIN CMC_CDML_CL_LINE B
    >>ON A.CLCL_ID = B.CLCL_ID
    >> WHERE
    >>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>)
    >>
    >>
    >>The last inner query IN is to get from a detail table rows which have different
    >>copay amount
    >>
    >>example
    >>111 will be selected in below case
    >>
    >>claim_id sequence_no copay
    >>111 1 10.0
    >>111 2 20.0
    >>222 1 100.0
    >>222 2 100.0
    >>
    >>Thank you!
    >>dhaya
    >>

    >



  5. #5
    dhaya Guest

    Re: query tuning


    By looking at the plan, i don't know how to find how to find which one is
    slow here. Looks like the CMC_CLHP_HOSP

    "dhaya" <shivdayal@yahoo.com> wrote:
    >
    >I tried replacing IN with OR
    >And then looked like the tables are too big so join is slow.
    >I am thinking only creating temp tables and indices may work, don't know
    >if anything else would work
    >
    >have the query and plan below
    >
    >SELECT CLCL.CLCL_ID,
    > SBSB.SBSB_ID,
    > CLCL.CLCL_TOT_CHG,
    > CLCL.CLCL_TOT_PAYABLE,
    > CLCL.CLCL_CUR_STS,
    > CLCL.CLCL_LOW_SVC_DT,
    > CLCL.CLCL_HIGH_SVC_DT,
    > CLCL.PDPD_ID,
    > CDML1.CDML_COPAY_AMT,
    > CDML1.CDML_CHG_AMT,
    > CDML1.CDML_ALLOW,
    > CDML1.CDML_SEQ_NO
    >
    >FROM CMC_CLCL_CLAIM CLCL
    >
    >INNER JOIN CMC_SBSB_SUBSC SBSB
    >ON CLCL.SBSB_CK = SBSB.SBSB_CK
    >
    >INNER JOIN CMC_CDML_CL_LINE CDML1
    >ON CLCL.CLCL_ID = CDML1.CLCL_ID
    >
    >INNER JOIN CMC_CLHP_HOSP CLHP
    >ON CLCL.CLCL_ID = CLHP.CLCL_ID
    >
    >WHERE
    >
    >(
    >CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS'

    OR
    >CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS' OR
    >CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    > OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID= 'ICUS'
    >OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID= 'NUR'
    >OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID= 'PRBS'
    > OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID= 'RB'
    >OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID= 'RBD'

    OR
    >CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'


    > OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID= 'SBDR'
    >OR CDML1.SESE_ID= 'SKNU'
    >)
    >
    >AND CLCL.CLCL_CL_SUB_TYPE='H'
    >
    >AND CLCL.CLCL_ID IN
    >(
    >SELECT SUB_CDML1.CLCL_ID
    >FROM CMC_CDML_CL_LINE SUB_CDML1
    >INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    >ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    >WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    >AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    >)
    >
    >AND CLHP.CLHP_FAC_TYPE = '1'
    >
    >AND
    >(
    >CLHP.CLHP_BILL_CLASS ='E' OR
    >CLHP.CLHP_BILL_CLASS ='U' OR
    >CLHP.CLHP_BILL_CLASS ='S' OR
    >CLHP.CLHP_BILL_CLASS ='3'
    >)
    >
    >ORDER BY CLCL.PDPD_ID
    >
    >
    >
    >----------
    >
    >QUERY PLAN FOR STATEMENT 1 (at line 1).
    >
    >
    >
    >
    > STEP 1
    > The type of query is INSERT.
    > The update mode is direct.
    > Worktable1 created for ORDER BY.
    >
    >
    > FROM TABLE
    > CMC_CLCL_CLAIM
    > CLCL
    > Nested iteration.
    > Table Scan.
    > Forward scan.
    > Positioning at start of table.
    > Using I/O Size 16 Kbytes for data pages.
    > With LRU Buffer Replacement Strategy for data pages.
    >
    >
    > FROM TABLE
    > CMC_SBSB_SUBSC
    > SBSB
    > Nested iteration.
    > Index : CMCX_SBSB_PRIMARY
    > Forward scan.
    > Positioning by key.
    > Keys are:
    > SBSB_CK ASC
    > Using I/O Size 2 Kbytes for index leaf pages.
    > With LRU Buffer Replacement Strategy for index leaf pages.
    > Using I/O Size 2 Kbytes for data pages.
    > With LRU Buffer Replacement Strategy for data pages.
    >
    >
    > FROM TABLE
    > CMC_CDML_CL_LINE
    > CDML1
    > Nested iteration.
    > Index : CMCX_CDML_PRIMARY
    > Forward scan.
    > Positioning by key.
    > Keys are:
    > CLCL_ID ASC
    > Using I/O Size 16 Kbytes for index leaf pages.
    > With LRU Buffer Replacement Strategy for index leaf pages.
    > Using I/O Size 2 Kbytes for data pages.
    > With LRU Buffer Replacement Strategy for data pages.
    >
    >
    > FROM TABLE
    > CMC_CLHP_HOSP
    > CLHP
    > Nested iteration.
    > Index : CMCX_CLHP_SECOND
    > Forward scan.
    > Positioning by key.
    > Keys are:
    > CLCL_ID ASC
    > Using I/O Size 2 Kbytes for index leaf pages.
    > With LRU Buffer Replacement Strategy for index leaf pages.
    > Using I/O Size 2 Kbytes for data pages.
    > With LRU Buffer Replacement Strategy for data pages.
    >
    >
    > FROM TABLE
    > CMC_CDML_CL_LINE
    > SUB_CDML1
    > EXISTS TABLE : nested iteration.
    > Index : CMCX_CDML_PRIMARY
    > Forward scan.
    > Positioning by key.
    > Keys are:
    > CLCL_ID ASC
    > Using I/O Size 16 Kbytes for index leaf pages.
    > With LRU Buffer Replacement Strategy for index leaf pages.
    > Using I/O Size 2 Kbytes for data pages.
    > With LRU Buffer Replacement Strategy for data pages.
    >
    >
    > FROM TABLE
    > CMC_CDML_CL_LINE
    > SUB_CDML2
    > EXISTS TABLE : nested iteration.
    > Index : CMCX_CDML_PRIMARY
    > Forward scan.
    > Positioning by key.
    > Keys are:
    > CLCL_ID ASC
    > Using I/O Size 16 Kbytes for index leaf pages.
    > With LRU Buffer Replacement Strategy for index leaf pages.
    > Using I/O Size 2 Kbytes for data pages.
    > With LRU Buffer Replacement Strategy for data pages.
    > TO TABLE
    > Worktable1.
    >
    >
    > STEP 2
    > The type of query is SELECT.
    > This step involves sorting.
    >
    >
    > FROM TABLE
    > Worktable1.
    > Using GETSORTED
    > Table Scan.
    > Forward scan.
    > Positioning at start of table.
    > Using I/O Size 16 Kbytes for data pages.
    > With MRU Buffer Replacement Strategy for data pages.
    >
    >
    >Parse and Compile Time 0.
    >SQL Server cpu time: 0 ms.
    >Server Message: Number 1562, Severity 10
    >Server 'aims_dev2', Line 1:
    >The sort for Worktable1 is done in Serial
    >Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202 apf=0
    >total=150202), physical reads: (regular=5584 apf=13331 total=18915), apf
    >IOs used=13228
    >Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431 apf=11
    >total=233442), physical reads: (regular=4573 apf=412 total=4985), apf IOs
    >used=209
    >Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    >apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    >apf IOs used=88751
    >Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084 apf=0
    >total=17084), physical reads: (regular=2117 apf=18 total=2135), apf IOs

    used=18
    >
    >Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170 apf=0
    >total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    >Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038 apf=0
    >total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15


    >Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    >physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    >Total writes for this command: 6
    >
    >Execution Time 144.
    >SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    >(1 row affected)
    >
    >
    >
    >
    >"KevinV" <none@none.com> wrote:
    >>
    >>Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >>
    >>There is a lot to tuning queries. The query analyzer execution plan is

    usually
    >>a good place to start. Look for the step in the execution plan that has

    >the
    >>highest cost and start there to look for optimizations. Generally adding
    >>indexes for certain columns, like columns referenced in joins and where

    >clauses
    >>is what will improve the query the most. If you see steps that are doing
    >>table scans then that likely means you need an index. It is also possible
    >>that your query will just be slow depending on the number of rows of data,
    >>physical placement of data, log and index database files and the configuration
    >>of the server.
    >>
    >>
    >>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>
    >>>any way to this query , its very slow.
    >>>
    >>>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >>> CMC_SBSB_SUBSC.SBSB_ID,
    >>> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>> CMC_CLCL_CLAIM.PDPD_ID,
    >>> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >>> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >>> CMC_CDML_CL_LINE.CDML_ALLOW,
    >>> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>>into adhocdb..JP_COPAY_ERRORS
    >>>FROM CMC_CLCL_CLAIM
    >>>
    >>>INNER JOIN CMC_SBSB_SUBSC
    >>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>
    >>>INNER JOIN CMC_CDML_CL_LINE
    >>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>
    >>>INNER JOIN CMC_CLHP_HOSP
    >>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>
    >>>WHERE
    >>>
    >>>CMC_CDML_CL_LINE.SESE_ID In
    >>> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>= '07/07/2003'
    >>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>>(
    >>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>INNER JOIN CMC_CDML_CL_LINE B
    >>>ON A.CLCL_ID = B.CLCL_ID
    >>> WHERE
    >>>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>>)
    >>>
    >>>
    >>>The last inner query IN is to get from a detail table rows which have

    different
    >>>copay amount
    >>>
    >>>example
    >>>111 will be selected in below case
    >>>
    >>>claim_id sequence_no copay
    >>>111 1 10.0
    >>>111 2 20.0
    >>>222 1 100.0
    >>>222 2 100.0
    >>>
    >>>Thank you!
    >>>dhaya
    >>>

    >>

    >



  6. #6
    KevinV Guest

    Re: query tuning


    Here's some various comments below.

    The 'Show Execution Plan' option should show you a graphical query plan.
    Each step in the plan has a cost associated with it. I don't know what version
    of sql you have and/or if there is a text only display of the plan. I don't
    see the costs in your query plan, although I don't know if there is enough
    steps anyway. The graphical plan may just show two steps which doesn't help
    much.

    I would use the INs instead of ORs. I'm not sure if it makes a difference
    performance wise but it is much more readable.

    Make sure your order by column is an index. It may still do a table scan
    anyway.

    It looks like all the other columns have indexes.

    Another thing I resort to at times is simplifying the query to determine
    what causes it to be slow. For example, you may want to remove the subselect
    and run the query, then add it back and run it. Same with the joins or the
    column checks on the where clause. You may come across one particular thing
    that makes the query slow.

    That's about all I can think of now. Hope some of it helps.


    "dhaya" <shivdayal@hotmail.com> wrote:
    >
    >By looking at the plan, i don't know how to find how to find which one is
    >slow here. Looks like the CMC_CLHP_HOSP
    >
    >"dhaya" <shivdayal@yahoo.com> wrote:
    >>
    >>I tried replacing IN with OR
    >>And then looked like the tables are too big so join is slow.
    >>I am thinking only creating temp tables and indices may work, don't know
    >>if anything else would work
    >>
    >>have the query and plan below
    >>
    >>SELECT CLCL.CLCL_ID,
    >> SBSB.SBSB_ID,
    >> CLCL.CLCL_TOT_CHG,
    >> CLCL.CLCL_TOT_PAYABLE,
    >> CLCL.CLCL_CUR_STS,
    >> CLCL.CLCL_LOW_SVC_DT,
    >> CLCL.CLCL_HIGH_SVC_DT,
    >> CLCL.PDPD_ID,
    >> CDML1.CDML_COPAY_AMT,
    >> CDML1.CDML_CHG_AMT,
    >> CDML1.CDML_ALLOW,
    >> CDML1.CDML_SEQ_NO
    >>
    >>FROM CMC_CLCL_CLAIM CLCL
    >>
    >>INNER JOIN CMC_SBSB_SUBSC SBSB
    >>ON CLCL.SBSB_CK = SBSB.SBSB_CK
    >>
    >>INNER JOIN CMC_CDML_CL_LINE CDML1
    >>ON CLCL.CLCL_ID = CDML1.CLCL_ID
    >>
    >>INNER JOIN CMC_CLHP_HOSP CLHP
    >>ON CLCL.CLCL_ID = CLHP.CLCL_ID
    >>
    >>WHERE
    >>
    >>(
    >>CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS'

    >OR
    >>CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS' OR
    >>CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    >> OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID= 'ICUS'
    >>OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID= 'NUR'
    >>OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID= 'PRBS'
    >> OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID= 'RB'
    >>OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID= 'RBD'

    >OR
    >>CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'

    >
    >> OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID= 'SBDR'
    >>OR CDML1.SESE_ID= 'SKNU'
    >>)
    >>
    >>AND CLCL.CLCL_CL_SUB_TYPE='H'
    >>
    >>AND CLCL.CLCL_ID IN
    >>(
    >>SELECT SUB_CDML1.CLCL_ID
    >>FROM CMC_CDML_CL_LINE SUB_CDML1
    >>INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    >>ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    >>WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    >>AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    >>)
    >>
    >>AND CLHP.CLHP_FAC_TYPE = '1'
    >>
    >>AND
    >>(
    >>CLHP.CLHP_BILL_CLASS ='E' OR
    >>CLHP.CLHP_BILL_CLASS ='U' OR
    >>CLHP.CLHP_BILL_CLASS ='S' OR
    >>CLHP.CLHP_BILL_CLASS ='3'
    >>)
    >>
    >>ORDER BY CLCL.PDPD_ID
    >>
    >>
    >>
    >>----------
    >>
    >>QUERY PLAN FOR STATEMENT 1 (at line 1).
    >>
    >>
    >>
    >>
    >> STEP 1
    >> The type of query is INSERT.
    >> The update mode is direct.
    >> Worktable1 created for ORDER BY.
    >>
    >>
    >> FROM TABLE
    >> CMC_CLCL_CLAIM
    >> CLCL
    >> Nested iteration.
    >> Table Scan.
    >> Forward scan.
    >> Positioning at start of table.
    >> Using I/O Size 16 Kbytes for data pages.
    >> With LRU Buffer Replacement Strategy for data pages.
    >>
    >>
    >> FROM TABLE
    >> CMC_SBSB_SUBSC
    >> SBSB
    >> Nested iteration.
    >> Index : CMCX_SBSB_PRIMARY
    >> Forward scan.
    >> Positioning by key.
    >> Keys are:
    >> SBSB_CK ASC
    >> Using I/O Size 2 Kbytes for index leaf pages.
    >> With LRU Buffer Replacement Strategy for index leaf pages.
    >> Using I/O Size 2 Kbytes for data pages.
    >> With LRU Buffer Replacement Strategy for data pages.
    >>
    >>
    >> FROM TABLE
    >> CMC_CDML_CL_LINE
    >> CDML1
    >> Nested iteration.
    >> Index : CMCX_CDML_PRIMARY
    >> Forward scan.
    >> Positioning by key.
    >> Keys are:
    >> CLCL_ID ASC
    >> Using I/O Size 16 Kbytes for index leaf pages.
    >> With LRU Buffer Replacement Strategy for index leaf pages.
    >> Using I/O Size 2 Kbytes for data pages.
    >> With LRU Buffer Replacement Strategy for data pages.
    >>
    >>
    >> FROM TABLE
    >> CMC_CLHP_HOSP
    >> CLHP
    >> Nested iteration.
    >> Index : CMCX_CLHP_SECOND
    >> Forward scan.
    >> Positioning by key.
    >> Keys are:
    >> CLCL_ID ASC
    >> Using I/O Size 2 Kbytes for index leaf pages.
    >> With LRU Buffer Replacement Strategy for index leaf pages.
    >> Using I/O Size 2 Kbytes for data pages.
    >> With LRU Buffer Replacement Strategy for data pages.
    >>
    >>
    >> FROM TABLE
    >> CMC_CDML_CL_LINE
    >> SUB_CDML1
    >> EXISTS TABLE : nested iteration.
    >> Index : CMCX_CDML_PRIMARY
    >> Forward scan.
    >> Positioning by key.
    >> Keys are:
    >> CLCL_ID ASC
    >> Using I/O Size 16 Kbytes for index leaf pages.
    >> With LRU Buffer Replacement Strategy for index leaf pages.
    >> Using I/O Size 2 Kbytes for data pages.
    >> With LRU Buffer Replacement Strategy for data pages.
    >>
    >>
    >> FROM TABLE
    >> CMC_CDML_CL_LINE
    >> SUB_CDML2
    >> EXISTS TABLE : nested iteration.
    >> Index : CMCX_CDML_PRIMARY
    >> Forward scan.
    >> Positioning by key.
    >> Keys are:
    >> CLCL_ID ASC
    >> Using I/O Size 16 Kbytes for index leaf pages.
    >> With LRU Buffer Replacement Strategy for index leaf pages.
    >> Using I/O Size 2 Kbytes for data pages.
    >> With LRU Buffer Replacement Strategy for data pages.
    >> TO TABLE
    >> Worktable1.
    >>
    >>
    >> STEP 2
    >> The type of query is SELECT.
    >> This step involves sorting.
    >>
    >>
    >> FROM TABLE
    >> Worktable1.
    >> Using GETSORTED
    >> Table Scan.
    >> Forward scan.
    >> Positioning at start of table.
    >> Using I/O Size 16 Kbytes for data pages.
    >> With MRU Buffer Replacement Strategy for data pages.
    >>
    >>
    >>Parse and Compile Time 0.
    >>SQL Server cpu time: 0 ms.
    >>Server Message: Number 1562, Severity 10
    >>Server 'aims_dev2', Line 1:
    >>The sort for Worktable1 is done in Serial
    >>Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202 apf=0
    >>total=150202), physical reads: (regular=5584 apf=13331 total=18915), apf
    >>IOs used=13228
    >>Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431

    apf=11
    >>total=233442), physical reads: (regular=4573 apf=412 total=4985), apf IOs
    >>used=209
    >>Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    >>apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    >>apf IOs used=88751
    >>Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084 apf=0
    >>total=17084), physical reads: (regular=2117 apf=18 total=2135), apf IOs

    >used=18
    >>
    >>Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170 apf=0
    >>total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    >>Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038 apf=0
    >>total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15

    >
    >>Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    >>physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    >>Total writes for this command: 6
    >>
    >>Execution Time 144.
    >>SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    >>(1 row affected)
    >>
    >>
    >>
    >>
    >>"KevinV" <none@none.com> wrote:
    >>>
    >>>Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >>>
    >>>There is a lot to tuning queries. The query analyzer execution plan is

    >usually
    >>>a good place to start. Look for the step in the execution plan that has

    >>the
    >>>highest cost and start there to look for optimizations. Generally adding
    >>>indexes for certain columns, like columns referenced in joins and where

    >>clauses
    >>>is what will improve the query the most. If you see steps that are doing
    >>>table scans then that likely means you need an index. It is also possible
    >>>that your query will just be slow depending on the number of rows of data,
    >>>physical placement of data, log and index database files and the configuration
    >>>of the server.
    >>>
    >>>
    >>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>
    >>>>any way to this query , its very slow.
    >>>>
    >>>>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >>>> CMC_SBSB_SUBSC.SBSB_ID,
    >>>> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>>> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>>> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>>> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>>> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>>> CMC_CLCL_CLAIM.PDPD_ID,
    >>>> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >>>> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >>>> CMC_CDML_CL_LINE.CDML_ALLOW,
    >>>> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>>>into adhocdb..JP_COPAY_ERRORS
    >>>>FROM CMC_CLCL_CLAIM
    >>>>
    >>>>INNER JOIN CMC_SBSB_SUBSC
    >>>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>>
    >>>>INNER JOIN CMC_CDML_CL_LINE
    >>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>>
    >>>>INNER JOIN CMC_CLHP_HOSP
    >>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>>
    >>>>WHERE
    >>>>
    >>>>CMC_CDML_CL_LINE.SESE_ID In
    >>>> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>>> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>>> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>>> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>>= '07/07/2003'
    >>>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>>>(
    >>>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>>INNER JOIN CMC_CDML_CL_LINE B
    >>>>ON A.CLCL_ID = B.CLCL_ID
    >>>> WHERE
    >>>>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>>>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>>>)
    >>>>
    >>>>
    >>>>The last inner query IN is to get from a detail table rows which have

    >different
    >>>>copay amount
    >>>>
    >>>>example
    >>>>111 will be selected in below case
    >>>>
    >>>>claim_id sequence_no copay
    >>>>111 1 10.0
    >>>>111 2 20.0
    >>>>222 1 100.0
    >>>>222 2 100.0
    >>>>
    >>>>Thank you!
    >>>>dhaya
    >>>>
    >>>

    >>

    >



  7. #7
    dhaya Guest

    Re: query tuning


    I actually tried with each of the WHERE clauses and the indexes, results are
    below. Looks like since its non-clustered join is slow, moreover all the
    fields used in the where don't have index ? In such cases, what is the best
    solution to deal with these kind of things ? Should i create some temp tables
    or use cursors etc.

    the results below.
    JOINS Query time results:

    ' Direct select from CLCL table :
    Execution Time 20.
    SQL Server cpu time: 2000 ms. SQL Server elapsed time: 17893 ms.
    (0 rows affected)


    ' When Joined with CDML table , type 'H', date params
    Execution Time 24.
    SQL Server cpu time: 2400 ms. SQL Server elapsed time: 20266 ms.
    (0 rows affected)


    ' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    included
    Execution Time 22.
    SQL Server cpu time: 2200 ms. SQL Server elapsed time: 82940 ms.


    ' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    included, looking for multiple copay's
    Execution Time 29.
    SQL Server cpu time: 2900 ms. SQL Server elapsed time: 30180 ms.


    ' When Joined with the CLHP table and filtering with FAC_TYPE and BILL CLASS

    Execution Time 29.
    SQL Server cpu time: 2900 ms. SQL Server elapsed time: 25260 ms.


    'In another time, the query which does a direct select from table only takes
    more mins
    Execution Time 39.
    SQL Server cpu time: 3900 ms. SQL Server elapsed time: 29793 ms.


    'the query without the date params

    Execution Time 384.
    SQL Server cpu time: 38400 ms. SQL Server elapsed time: 748623 ms.

    ' the query without date params and CLHP and COPAY
    Execution Time 158.
    SQL Server cpu time: 15800 ms. SQL Server elapsed time: 316946 ms.

    ---------------------------------------------------------------------------INDEXES
    IN TABLES

    CMCX_CLCL_CLUSTER clustered located on default
    MEME_CK, CLCL_ID
    CMCX_CLCL_PRIMARY nonclustered, unique located on default
    CLCL_ID
    CMCX_CLCL_SBSB_CK nonclustered located on default
    SBSB_CK
    CMCX_CLCL_PRPR_ID nonclustered located on default
    PRPR_ID
    CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default
    ATXR_SOURCE_ID
    CMCX_CLCL_DRAG_DT nonclustered located on default
    CLCL_DRAG_DT
    CMCX_CLCL_CUR_STS nonclustered located on default
    CLCL_CUR_STS
    CMCX_CLCL_NEXT_REV_DT nonclustered located on default
    CLCL_NEXT_REV_DT
    CMCX_CLCL_PAID_DT nonclustered located on default
    CLCL_PAID_DT
    AIN_PP11_CLCL_CLAIM nonclustered located on default
    CLCL_INPUT_DT
    CMCX_CDML_CLUSTER clustered located on default
    MEME_CK, CDML_FROM_DT
    CMCX_CDML_PRIMARY nonclustered, unique located on default
    CLCL_ID, CDML_SEQ_NO
    CMCX_CDML_PRPR_ID nonclustered located on default
    PRPR_ID
    CMCX_CLHP_PRIMARY clustered located on default
    MEME_CK, CLCL_ID
    CMCX_CLHP_SECOND nonclustered, unique located on default
    CLCL_ID

    Columns used without index
    RECD_DT
    SUB_TYPE
    CLHP_FAC_TYPE
    CLHP_BILL_CLASS
    SESE_ID




    "KevinV" <none@none.com> wrote:
    >
    >Here's some various comments below.
    >
    >The 'Show Execution Plan' option should show you a graphical query plan.
    >Each step in the plan has a cost associated with it. I don't know what version
    >of sql you have and/or if there is a text only display of the plan. I don't
    >see the costs in your query plan, although I don't know if there is enough
    >steps anyway. The graphical plan may just show two steps which doesn't help
    >much.
    >
    >I would use the INs instead of ORs. I'm not sure if it makes a difference
    >performance wise but it is much more readable.
    >
    >Make sure your order by column is an index. It may still do a table scan
    >anyway.
    >
    >It looks like all the other columns have indexes.
    >
    >Another thing I resort to at times is simplifying the query to determine
    >what causes it to be slow. For example, you may want to remove the subselect
    >and run the query, then add it back and run it. Same with the joins or the
    >column checks on the where clause. You may come across one particular thing
    >that makes the query slow.
    >
    >That's about all I can think of now. Hope some of it helps.
    >
    >
    >"dhaya" <shivdayal@hotmail.com> wrote:
    >>
    >>By looking at the plan, i don't know how to find how to find which one

    is
    >>slow here. Looks like the CMC_CLHP_HOSP
    >>
    >>"dhaya" <shivdayal@yahoo.com> wrote:
    >>>
    >>>I tried replacing IN with OR
    >>>And then looked like the tables are too big so join is slow.
    >>>I am thinking only creating temp tables and indices may work, don't know
    >>>if anything else would work
    >>>
    >>>have the query and plan below
    >>>
    >>>SELECT CLCL.CLCL_ID,
    >>> SBSB.SBSB_ID,
    >>> CLCL.CLCL_TOT_CHG,
    >>> CLCL.CLCL_TOT_PAYABLE,
    >>> CLCL.CLCL_CUR_STS,
    >>> CLCL.CLCL_LOW_SVC_DT,
    >>> CLCL.CLCL_HIGH_SVC_DT,
    >>> CLCL.PDPD_ID,
    >>> CDML1.CDML_COPAY_AMT,
    >>> CDML1.CDML_CHG_AMT,
    >>> CDML1.CDML_ALLOW,
    >>> CDML1.CDML_SEQ_NO
    >>>
    >>>FROM CMC_CLCL_CLAIM CLCL
    >>>
    >>>INNER JOIN CMC_SBSB_SUBSC SBSB
    >>>ON CLCL.SBSB_CK = SBSB.SBSB_CK
    >>>
    >>>INNER JOIN CMC_CDML_CL_LINE CDML1
    >>>ON CLCL.CLCL_ID = CDML1.CLCL_ID
    >>>
    >>>INNER JOIN CMC_CLHP_HOSP CLHP
    >>>ON CLCL.CLCL_ID = CLHP.CLCL_ID
    >>>
    >>>WHERE
    >>>
    >>>(
    >>>CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS'

    >>OR
    >>>CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS'

    OR
    >>>CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    >>> OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID= 'ICUS'
    >>>OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID= 'NUR'
    >>>OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID= 'PRBS'
    >>> OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID= 'RB'
    >>>OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID= 'RBD'

    >>OR
    >>>CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'

    >>
    >>> OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID= 'SBDR'
    >>>OR CDML1.SESE_ID= 'SKNU'
    >>>)
    >>>
    >>>AND CLCL.CLCL_CL_SUB_TYPE='H'
    >>>
    >>>AND CLCL.CLCL_ID IN
    >>>(
    >>>SELECT SUB_CDML1.CLCL_ID
    >>>FROM CMC_CDML_CL_LINE SUB_CDML1
    >>>INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    >>>ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    >>>WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    >>>AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    >>>)
    >>>
    >>>AND CLHP.CLHP_FAC_TYPE = '1'
    >>>
    >>>AND
    >>>(
    >>>CLHP.CLHP_BILL_CLASS ='E' OR
    >>>CLHP.CLHP_BILL_CLASS ='U' OR
    >>>CLHP.CLHP_BILL_CLASS ='S' OR
    >>>CLHP.CLHP_BILL_CLASS ='3'
    >>>)
    >>>
    >>>ORDER BY CLCL.PDPD_ID
    >>>
    >>>
    >>>
    >>>----------
    >>>
    >>>QUERY PLAN FOR STATEMENT 1 (at line 1).
    >>>
    >>>
    >>>
    >>>
    >>> STEP 1
    >>> The type of query is INSERT.
    >>> The update mode is direct.
    >>> Worktable1 created for ORDER BY.
    >>>
    >>>
    >>> FROM TABLE
    >>> CMC_CLCL_CLAIM
    >>> CLCL
    >>> Nested iteration.
    >>> Table Scan.
    >>> Forward scan.
    >>> Positioning at start of table.
    >>> Using I/O Size 16 Kbytes for data pages.
    >>> With LRU Buffer Replacement Strategy for data pages.
    >>>
    >>>
    >>> FROM TABLE
    >>> CMC_SBSB_SUBSC
    >>> SBSB
    >>> Nested iteration.
    >>> Index : CMCX_SBSB_PRIMARY
    >>> Forward scan.
    >>> Positioning by key.
    >>> Keys are:
    >>> SBSB_CK ASC
    >>> Using I/O Size 2 Kbytes for index leaf pages.
    >>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>> Using I/O Size 2 Kbytes for data pages.
    >>> With LRU Buffer Replacement Strategy for data pages.
    >>>
    >>>
    >>> FROM TABLE
    >>> CMC_CDML_CL_LINE
    >>> CDML1
    >>> Nested iteration.
    >>> Index : CMCX_CDML_PRIMARY
    >>> Forward scan.
    >>> Positioning by key.
    >>> Keys are:
    >>> CLCL_ID ASC
    >>> Using I/O Size 16 Kbytes for index leaf pages.
    >>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>> Using I/O Size 2 Kbytes for data pages.
    >>> With LRU Buffer Replacement Strategy for data pages.
    >>>
    >>>
    >>> FROM TABLE
    >>> CMC_CLHP_HOSP
    >>> CLHP
    >>> Nested iteration.
    >>> Index : CMCX_CLHP_SECOND
    >>> Forward scan.
    >>> Positioning by key.
    >>> Keys are:
    >>> CLCL_ID ASC
    >>> Using I/O Size 2 Kbytes for index leaf pages.
    >>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>> Using I/O Size 2 Kbytes for data pages.
    >>> With LRU Buffer Replacement Strategy for data pages.
    >>>
    >>>
    >>> FROM TABLE
    >>> CMC_CDML_CL_LINE
    >>> SUB_CDML1
    >>> EXISTS TABLE : nested iteration.
    >>> Index : CMCX_CDML_PRIMARY
    >>> Forward scan.
    >>> Positioning by key.
    >>> Keys are:
    >>> CLCL_ID ASC
    >>> Using I/O Size 16 Kbytes for index leaf pages.
    >>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>> Using I/O Size 2 Kbytes for data pages.
    >>> With LRU Buffer Replacement Strategy for data pages.
    >>>
    >>>
    >>> FROM TABLE
    >>> CMC_CDML_CL_LINE
    >>> SUB_CDML2
    >>> EXISTS TABLE : nested iteration.
    >>> Index : CMCX_CDML_PRIMARY
    >>> Forward scan.
    >>> Positioning by key.
    >>> Keys are:
    >>> CLCL_ID ASC
    >>> Using I/O Size 16 Kbytes for index leaf pages.
    >>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>> Using I/O Size 2 Kbytes for data pages.
    >>> With LRU Buffer Replacement Strategy for data pages.
    >>> TO TABLE
    >>> Worktable1.
    >>>
    >>>
    >>> STEP 2
    >>> The type of query is SELECT.
    >>> This step involves sorting.
    >>>
    >>>
    >>> FROM TABLE
    >>> Worktable1.
    >>> Using GETSORTED
    >>> Table Scan.
    >>> Forward scan.
    >>> Positioning at start of table.
    >>> Using I/O Size 16 Kbytes for data pages.
    >>> With MRU Buffer Replacement Strategy for data pages.
    >>>
    >>>
    >>>Parse and Compile Time 0.
    >>>SQL Server cpu time: 0 ms.
    >>>Server Message: Number 1562, Severity 10
    >>>Server 'aims_dev2', Line 1:
    >>>The sort for Worktable1 is done in Serial
    >>>Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202 apf=0
    >>>total=150202), physical reads: (regular=5584 apf=13331 total=18915), apf
    >>>IOs used=13228
    >>>Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431

    >apf=11
    >>>total=233442), physical reads: (regular=4573 apf=412 total=4985), apf

    IOs
    >>>used=209
    >>>Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    >>>apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    >>>apf IOs used=88751
    >>>Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084 apf=0
    >>>total=17084), physical reads: (regular=2117 apf=18 total=2135), apf IOs

    >>used=18
    >>>
    >>>Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170 apf=0
    >>>total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0


    >>>Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038 apf=0
    >>>total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15

    >>
    >>>Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    >>>physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    >>>Total writes for this command: 6
    >>>
    >>>Execution Time 144.
    >>>SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    >>>(1 row affected)
    >>>
    >>>
    >>>
    >>>
    >>>"KevinV" <none@none.com> wrote:
    >>>>
    >>>>Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >>>>
    >>>>There is a lot to tuning queries. The query analyzer execution plan is

    >>usually
    >>>>a good place to start. Look for the step in the execution plan that has
    >>>the
    >>>>highest cost and start there to look for optimizations. Generally adding
    >>>>indexes for certain columns, like columns referenced in joins and where
    >>>clauses
    >>>>is what will improve the query the most. If you see steps that are doing
    >>>>table scans then that likely means you need an index. It is also possible
    >>>>that your query will just be slow depending on the number of rows of

    data,
    >>>>physical placement of data, log and index database files and the configuration
    >>>>of the server.
    >>>>
    >>>>
    >>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>
    >>>>>any way to this query , its very slow.
    >>>>>
    >>>>>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >>>>> CMC_SBSB_SUBSC.SBSB_ID,
    >>>>> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>>>> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>>>> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>>>> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>>>> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>>>> CMC_CLCL_CLAIM.PDPD_ID,
    >>>>> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >>>>> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >>>>> CMC_CDML_CL_LINE.CDML_ALLOW,
    >>>>> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>>>>into adhocdb..JP_COPAY_ERRORS
    >>>>>FROM CMC_CLCL_CLAIM
    >>>>>
    >>>>>INNER JOIN CMC_SBSB_SUBSC
    >>>>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>>>
    >>>>>INNER JOIN CMC_CDML_CL_LINE
    >>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>>>
    >>>>>INNER JOIN CMC_CLHP_HOSP
    >>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>>>
    >>>>>WHERE
    >>>>>
    >>>>>CMC_CDML_CL_LINE.SESE_ID In
    >>>>> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>>>> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>>>> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>>>> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>>>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>>>= '07/07/2003'
    >>>>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>>>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>>>>(
    >>>>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>>>INNER JOIN CMC_CDML_CL_LINE B
    >>>>>ON A.CLCL_ID = B.CLCL_ID
    >>>>> WHERE
    >>>>>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>>>>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>>>>)
    >>>>>
    >>>>>
    >>>>>The last inner query IN is to get from a detail table rows which have

    >>different
    >>>>>copay amount
    >>>>>
    >>>>>example
    >>>>>111 will be selected in below case
    >>>>>
    >>>>>claim_id sequence_no copay
    >>>>>111 1 10.0
    >>>>>111 2 20.0
    >>>>>222 1 100.0
    >>>>>222 2 100.0
    >>>>>
    >>>>>Thank you!
    >>>>>dhaya
    >>>>>
    >>>>
    >>>

    >>

    >



  8. #8
    dhaya Guest

    Re: query tuning


    updates:

    ended up with temp tables, but still looks like its running very slow.

    I ended up with something like this, i got a feedback its very slow still


    any idea, the columns that have index are below.

    IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    BEGIN
    DROP table tempdb..Claims_Copay
    END

    go

    IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    BEGIN
    DROP table tempdb..Claims_Copay_Hosp
    END

    go

    SELECT
    CMC_CLCL_CLAIM.CLCL_ID,
    CMC_SBSB_SUBSC.SBSB_ID,
    CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    CMC_CLCL_CLAIM.CLCL_CUR_STS,
    CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    CMC_CLCL_CLAIM.PDPD_ID,
    CMC_PDDS_PROD_DESC.PDDS_DESC

    INTO tempdb..Claims_Copay_Hosp

    FROM CMC_CLCL_CLAIM
    INNER JOIN CMC_SBSB_SUBSC
    ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    INNER JOIN CMC_CLHP_HOSP
    ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    INNER JOIN CMC_PDPD_PRODUCT
    ON CMC_CLCL_CLAIM.PDPD_ID = CMC_PDPD_PRODUCT.PDPD_ID
    INNER JOIN CMC_PDDS_PROD_DESC
    ON CMC_PDPD_PRODUCT.PDPD_ID = CMC_PDDS_PROD_DESC.PDPD_ID

    WHERE
    CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >= '07/07/2003'

    AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')

    go

    IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    begin
    CREATE CLUSTERED INDEX Index1 ON tempdb..Claims_Copay_Hosp (CLCL_ID)
    end

    go

    IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    begin
    select
    tempdb..Claims_Copay_Hosp.CLCL_ID,
    tempdb..Claims_Copay_Hosp.SBSB_ID,
    tempdb..Claims_Copay_Hosp.CLCL_TOT_CHG,
    tempdb..Claims_Copay_Hosp.CLCL_TOT_PAYABLE,
    tempdb..Claims_Copay_Hosp.CLCL_CUR_STS,
    tempdb..Claims_Copay_Hosp.CLCL_LOW_SVC_DT,
    tempdb..Claims_Copay_Hosp.CLCL_HIGH_SVC_DT,
    tempdb..Claims_Copay_Hosp.PDPD_ID,
    tempdb..Claims_Copay_Hosp.PDDS_DESC,
    CDML_COPAY_AMT,
    CDML_CHG_AMT,
    CDML_ALLOW,
    CDML_SEQ_NO
    into tempdb..Claims_Copay
    from tempdb..Claims_Copay_Hosp
    INNER JOIN CMC_CDML_CL_LINE
    ON tempdb..Claims_Copay_Hosp.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    WHERE
    CMC_CDML_CL_LINE .SESE_ID In
    ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    'SPRB','SRBR','SRB','SBDR','SKNU')
    and Exists
    (
    SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    WHERE
    tempdb..Claims_Copay_Hosp.CLCL_ID = A.CLCL_ID
    group by A.CLCL_ID
    having count(distinct A.CDML_COPAY_AMT) > 1)

    end

    go

    IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    begin
    DROP TABLE tempdb..Claims_Copay_Hosp
    end

    go

    IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    begin
    CREATE CLUSTERED INDEX Index2 ON tempdb..Claims_Copay (CLCL_ID)
    end

    go

    IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    begin
    select * from tempdb..Claims_Copay
    end

    -------

    INDEXES IN TABLES

    CMCX_CLCL_CLUSTER clustered located on default
    MEME_CK, CLCL_ID
    CMCX_CLCL_PRIMARY nonclustered, unique located on default
    CLCL_ID
    CMCX_CLCL_SBSB_CK nonclustered located on default
    SBSB_CK
    CMCX_CLCL_PRPR_ID nonclustered located on default
    PRPR_ID
    CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default
    ATXR_SOURCE_ID
    CMCX_CLCL_DRAG_DT nonclustered located on default
    CLCL_DRAG_DT
    CMCX_CLCL_CUR_STS nonclustered located on default
    CLCL_CUR_STS
    CMCX_CLCL_NEXT_REV_DT nonclustered located on default
    CLCL_NEXT_REV_DT
    CMCX_CLCL_PAID_DT nonclustered located on default
    CLCL_PAID_DT
    AIN_PP11_CLCL_CLAIM nonclustered located on default
    CLCL_INPUT_DT
    CMCX_CDML_CLUSTER clustered located on default
    MEME_CK, CDML_FROM_DT
    CMCX_CDML_PRIMARY nonclustered, unique located on default
    CLCL_ID, CDML_SEQ_NO
    CMCX_CDML_PRPR_ID nonclustered located on default
    PRPR_ID
    CMCX_CLHP_PRIMARY clustered located on default
    MEME_CK, CLCL_ID
    CMCX_CLHP_SECOND nonclustered, unique located on default
    CLCL_ID


    All the columns used in WHERE clause are without index
    RECD_DT
    SUB_TYPE
    CLHP_FAC_TYPE
    CLHP_BILL_CLASS
    SESE_ID


    "dhaya" <shivdayal@hotmail.com> wrote:
    >
    >I actually tried with each of the WHERE clauses and the indexes, results

    are
    >below. Looks like since its non-clustered join is slow, moreover all the
    >fields used in the where don't have index ? In such cases, what is the best
    >solution to deal with these kind of things ? Should i create some temp tables
    >or use cursors etc.
    >
    >the results below.
    >JOINS Query time results:
    >
    >' Direct select from CLCL table :
    >Execution Time 20.
    >SQL Server cpu time: 2000 ms. SQL Server elapsed time: 17893 ms.
    >(0 rows affected)
    >
    >
    >' When Joined with CDML table , type 'H', date params
    >Execution Time 24.
    >SQL Server cpu time: 2400 ms. SQL Server elapsed time: 20266 ms.
    >(0 rows affected)
    >
    >
    >' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >included
    >Execution Time 22.
    >SQL Server cpu time: 2200 ms. SQL Server elapsed time: 82940 ms.
    >
    >
    >' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >included, looking for multiple copay's
    >Execution Time 29.
    >SQL Server cpu time: 2900 ms. SQL Server elapsed time: 30180 ms.
    >
    >
    >' When Joined with the CLHP table and filtering with FAC_TYPE and BILL CLASS
    >
    >Execution Time 29.
    >SQL Server cpu time: 2900 ms. SQL Server elapsed time: 25260 ms.
    >
    >
    >'In another time, the query which does a direct select from table only takes
    >more mins
    >Execution Time 39.
    >SQL Server cpu time: 3900 ms. SQL Server elapsed time: 29793 ms.
    >
    >
    >'the query without the date params
    >
    >Execution Time 384.
    >SQL Server cpu time: 38400 ms. SQL Server elapsed time: 748623 ms.
    >
    >' the query without date params and CLHP and COPAY
    >Execution Time 158.
    >SQL Server cpu time: 15800 ms. SQL Server elapsed time: 316946 ms.
    >
    >---------------------------------------------------------------------------INDEXES
    >IN TABLES
    >
    >CMCX_CLCL_CLUSTER clustered located on default


    > MEME_CK, CLCL_ID
    >CMCX_CLCL_PRIMARY nonclustered, unique located on default


    > CLCL_ID
    >CMCX_CLCL_SBSB_CK nonclustered located on default


    > SBSB_CK
    >CMCX_CLCL_PRPR_ID nonclustered located on default


    > PRPR_ID
    >CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    > ATXR_SOURCE_ID
    >CMCX_CLCL_DRAG_DT nonclustered located on default


    > CLCL_DRAG_DT
    >CMCX_CLCL_CUR_STS nonclustered located on default


    > CLCL_CUR_STS
    >CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    > CLCL_NEXT_REV_DT
    >CMCX_CLCL_PAID_DT nonclustered located on default


    > CLCL_PAID_DT
    >AIN_PP11_CLCL_CLAIM nonclustered located on default


    > CLCL_INPUT_DT
    >CMCX_CDML_CLUSTER clustered located on default


    > MEME_CK, CDML_FROM_DT
    >CMCX_CDML_PRIMARY nonclustered, unique located on default


    > CLCL_ID, CDML_SEQ_NO
    >CMCX_CDML_PRPR_ID nonclustered located on default


    > PRPR_ID
    >CMCX_CLHP_PRIMARY clustered located on default


    > MEME_CK, CLCL_ID
    >CMCX_CLHP_SECOND nonclustered, unique located on default


    > CLCL_ID


    >


    > Columns used without index
    >RECD_DT
    >SUB_TYPE
    >CLHP_FAC_TYPE
    >CLHP_BILL_CLASS
    >SESE_ID
    >
    >
    >
    >
    >"KevinV" <none@none.com> wrote:
    >>
    >>Here's some various comments below.
    >>
    >>The 'Show Execution Plan' option should show you a graphical query plan.
    >>Each step in the plan has a cost associated with it. I don't know what

    version
    >>of sql you have and/or if there is a text only display of the plan. I don't
    >>see the costs in your query plan, although I don't know if there is enough
    >>steps anyway. The graphical plan may just show two steps which doesn't

    help
    >>much.
    >>
    >>I would use the INs instead of ORs. I'm not sure if it makes a difference
    >>performance wise but it is much more readable.
    >>
    >>Make sure your order by column is an index. It may still do a table scan
    >>anyway.
    >>
    >>It looks like all the other columns have indexes.
    >>
    >>Another thing I resort to at times is simplifying the query to determine
    >>what causes it to be slow. For example, you may want to remove the subselect
    >>and run the query, then add it back and run it. Same with the joins or

    the
    >>column checks on the where clause. You may come across one particular thing
    >>that makes the query slow.
    >>
    >>That's about all I can think of now. Hope some of it helps.
    >>
    >>
    >>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>
    >>>By looking at the plan, i don't know how to find how to find which one

    >is
    >>>slow here. Looks like the CMC_CLHP_HOSP
    >>>
    >>>"dhaya" <shivdayal@yahoo.com> wrote:
    >>>>
    >>>>I tried replacing IN with OR
    >>>>And then looked like the tables are too big so join is slow.
    >>>>I am thinking only creating temp tables and indices may work, don't know
    >>>>if anything else would work
    >>>>
    >>>>have the query and plan below
    >>>>
    >>>>SELECT CLCL.CLCL_ID,
    >>>> SBSB.SBSB_ID,
    >>>> CLCL.CLCL_TOT_CHG,
    >>>> CLCL.CLCL_TOT_PAYABLE,
    >>>> CLCL.CLCL_CUR_STS,
    >>>> CLCL.CLCL_LOW_SVC_DT,
    >>>> CLCL.CLCL_HIGH_SVC_DT,
    >>>> CLCL.PDPD_ID,
    >>>> CDML1.CDML_COPAY_AMT,
    >>>> CDML1.CDML_CHG_AMT,
    >>>> CDML1.CDML_ALLOW,
    >>>> CDML1.CDML_SEQ_NO
    >>>>
    >>>>FROM CMC_CLCL_CLAIM CLCL
    >>>>
    >>>>INNER JOIN CMC_SBSB_SUBSC SBSB
    >>>>ON CLCL.SBSB_CK = SBSB.SBSB_CK
    >>>>
    >>>>INNER JOIN CMC_CDML_CL_LINE CDML1
    >>>>ON CLCL.CLCL_ID = CDML1.CLCL_ID
    >>>>
    >>>>INNER JOIN CMC_CLHP_HOSP CLHP
    >>>>ON CLCL.CLCL_ID = CLHP.CLCL_ID
    >>>>
    >>>>WHERE
    >>>>
    >>>>(
    >>>>CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS'
    >>>OR
    >>>>CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS'

    >OR
    >>>>CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    >>>> OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID= 'ICUS'
    >>>>OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID= 'NUR'
    >>>>OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID= 'PRBS'
    >>>> OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID=

    'RB'
    >>>>OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID= 'RBD'
    >>>OR
    >>>>CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'
    >>>
    >>>> OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID=

    'SBDR'
    >>>>OR CDML1.SESE_ID= 'SKNU'
    >>>>)
    >>>>
    >>>>AND CLCL.CLCL_CL_SUB_TYPE='H'
    >>>>
    >>>>AND CLCL.CLCL_ID IN
    >>>>(
    >>>>SELECT SUB_CDML1.CLCL_ID
    >>>>FROM CMC_CDML_CL_LINE SUB_CDML1
    >>>>INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    >>>>ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    >>>>WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    >>>>AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    >>>>)
    >>>>
    >>>>AND CLHP.CLHP_FAC_TYPE = '1'
    >>>>
    >>>>AND
    >>>>(
    >>>>CLHP.CLHP_BILL_CLASS ='E' OR
    >>>>CLHP.CLHP_BILL_CLASS ='U' OR
    >>>>CLHP.CLHP_BILL_CLASS ='S' OR
    >>>>CLHP.CLHP_BILL_CLASS ='3'
    >>>>)
    >>>>
    >>>>ORDER BY CLCL.PDPD_ID
    >>>>
    >>>>
    >>>>
    >>>>----------
    >>>>
    >>>>QUERY PLAN FOR STATEMENT 1 (at line 1).
    >>>>
    >>>>
    >>>>
    >>>>
    >>>> STEP 1
    >>>> The type of query is INSERT.
    >>>> The update mode is direct.
    >>>> Worktable1 created for ORDER BY.
    >>>>
    >>>>
    >>>> FROM TABLE
    >>>> CMC_CLCL_CLAIM
    >>>> CLCL
    >>>> Nested iteration.
    >>>> Table Scan.
    >>>> Forward scan.
    >>>> Positioning at start of table.
    >>>> Using I/O Size 16 Kbytes for data pages.
    >>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>
    >>>>
    >>>> FROM TABLE
    >>>> CMC_SBSB_SUBSC
    >>>> SBSB
    >>>> Nested iteration.
    >>>> Index : CMCX_SBSB_PRIMARY
    >>>> Forward scan.
    >>>> Positioning by key.
    >>>> Keys are:
    >>>> SBSB_CK ASC
    >>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>> Using I/O Size 2 Kbytes for data pages.
    >>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>
    >>>>
    >>>> FROM TABLE
    >>>> CMC_CDML_CL_LINE
    >>>> CDML1
    >>>> Nested iteration.
    >>>> Index : CMCX_CDML_PRIMARY
    >>>> Forward scan.
    >>>> Positioning by key.
    >>>> Keys are:
    >>>> CLCL_ID ASC
    >>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>> Using I/O Size 2 Kbytes for data pages.
    >>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>
    >>>>
    >>>> FROM TABLE
    >>>> CMC_CLHP_HOSP
    >>>> CLHP
    >>>> Nested iteration.
    >>>> Index : CMCX_CLHP_SECOND
    >>>> Forward scan.
    >>>> Positioning by key.
    >>>> Keys are:
    >>>> CLCL_ID ASC
    >>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>> Using I/O Size 2 Kbytes for data pages.
    >>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>
    >>>>
    >>>> FROM TABLE
    >>>> CMC_CDML_CL_LINE
    >>>> SUB_CDML1
    >>>> EXISTS TABLE : nested iteration.
    >>>> Index : CMCX_CDML_PRIMARY
    >>>> Forward scan.
    >>>> Positioning by key.
    >>>> Keys are:
    >>>> CLCL_ID ASC
    >>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>> Using I/O Size 2 Kbytes for data pages.
    >>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>
    >>>>
    >>>> FROM TABLE
    >>>> CMC_CDML_CL_LINE
    >>>> SUB_CDML2
    >>>> EXISTS TABLE : nested iteration.
    >>>> Index : CMCX_CDML_PRIMARY
    >>>> Forward scan.
    >>>> Positioning by key.
    >>>> Keys are:
    >>>> CLCL_ID ASC
    >>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>> Using I/O Size 2 Kbytes for data pages.
    >>>> With LRU Buffer Replacement Strategy for data pages.
    >>>> TO TABLE
    >>>> Worktable1.
    >>>>
    >>>>
    >>>> STEP 2
    >>>> The type of query is SELECT.
    >>>> This step involves sorting.
    >>>>
    >>>>
    >>>> FROM TABLE
    >>>> Worktable1.
    >>>> Using GETSORTED
    >>>> Table Scan.
    >>>> Forward scan.
    >>>> Positioning at start of table.
    >>>> Using I/O Size 16 Kbytes for data pages.
    >>>> With MRU Buffer Replacement Strategy for data pages.
    >>>>
    >>>>
    >>>>Parse and Compile Time 0.
    >>>>SQL Server cpu time: 0 ms.
    >>>>Server Message: Number 1562, Severity 10
    >>>>Server 'aims_dev2', Line 1:
    >>>>The sort for Worktable1 is done in Serial
    >>>>Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202 apf=0
    >>>>total=150202), physical reads: (regular=5584 apf=13331 total=18915),

    apf
    >>>>IOs used=13228
    >>>>Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431

    >>apf=11
    >>>>total=233442), physical reads: (regular=4573 apf=412 total=4985), apf

    >IOs
    >>>>used=209
    >>>>Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    >>>>apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    >>>>apf IOs used=88751
    >>>>Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084 apf=0
    >>>>total=17084), physical reads: (regular=2117 apf=18 total=2135), apf IOs
    >>>used=18
    >>>>
    >>>>Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170 apf=0
    >>>>total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

    >
    >>>>Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038

    apf=0
    >>>>total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15
    >>>
    >>>>Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    >>>>physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    >>>>Total writes for this command: 6
    >>>>
    >>>>Execution Time 144.
    >>>>SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    >>>>(1 row affected)
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>"KevinV" <none@none.com> wrote:
    >>>>>
    >>>>>Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >>>>>
    >>>>>There is a lot to tuning queries. The query analyzer execution plan

    is
    >>>usually
    >>>>>a good place to start. Look for the step in the execution plan that

    has
    >>>>the
    >>>>>highest cost and start there to look for optimizations. Generally adding
    >>>>>indexes for certain columns, like columns referenced in joins and where
    >>>>clauses
    >>>>>is what will improve the query the most. If you see steps that are doing
    >>>>>table scans then that likely means you need an index. It is also possible
    >>>>>that your query will just be slow depending on the number of rows of

    >data,
    >>>>>physical placement of data, log and index database files and the configuration
    >>>>>of the server.
    >>>>>
    >>>>>
    >>>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>>
    >>>>>>any way to this query , its very slow.
    >>>>>>
    >>>>>>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >>>>>> CMC_SBSB_SUBSC.SBSB_ID,
    >>>>>> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>>>>> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>>>>> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>>>>> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>>>>> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>>>>> CMC_CLCL_CLAIM.PDPD_ID,
    >>>>>> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >>>>>> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >>>>>> CMC_CDML_CL_LINE.CDML_ALLOW,
    >>>>>> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>>>>>into adhocdb..JP_COPAY_ERRORS
    >>>>>>FROM CMC_CLCL_CLAIM
    >>>>>>
    >>>>>>INNER JOIN CMC_SBSB_SUBSC
    >>>>>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>>>>
    >>>>>>INNER JOIN CMC_CDML_CL_LINE
    >>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>>>>
    >>>>>>INNER JOIN CMC_CLHP_HOSP
    >>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>>>>
    >>>>>>WHERE
    >>>>>>
    >>>>>>CMC_CDML_CL_LINE.SESE_ID In
    >>>>>> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>>>>> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>>>>> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>>>>> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>>>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>>>>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>>>>= '07/07/2003'
    >>>>>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>>>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>>>>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>>>>>(
    >>>>>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>>>>INNER JOIN CMC_CDML_CL_LINE B
    >>>>>>ON A.CLCL_ID = B.CLCL_ID
    >>>>>> WHERE
    >>>>>>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>>>>>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>>>>>)
    >>>>>>
    >>>>>>
    >>>>>>The last inner query IN is to get from a detail table rows which have
    >>>different
    >>>>>>copay amount
    >>>>>>
    >>>>>>example
    >>>>>>111 will be selected in below case
    >>>>>>
    >>>>>>claim_id sequence_no copay
    >>>>>>111 1 10.0
    >>>>>>111 2 20.0
    >>>>>>222 1 100.0
    >>>>>>222 2 100.0
    >>>>>>
    >>>>>>Thank you!
    >>>>>>dhaya
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  9. #9
    KevinV Guest

    Re: query tuning


    Columns used in where clauses should generally have indexes on them. If the
    where clause columns donít have indexes, the query optimizer will have no
    choice but to scan every row in the table. You should add indexes at least
    for testing and determine if they do any good. That goes for columns used
    in joins and order by. You can add the indexes, rerun the query and check
    the execution plan to see if they do any good.

    In particular, you want an index on the column that you are using in the
    where clause that limits the result set the most. I would guess this is probably
    the RECD_DT column.

    Non-clustered indexes versus clustered indexes wonít make much difference
    in the query performance. Indexes versus no indexes makes a larger difference.

    Donít use cursors, that is the worst option. Creating any preliminary temp
    tables is not going to do you any good for the most part.

    In your list of test times, what happened with the ones that takes 384 seconds
    and the 158 seconds. The others arenít bad, these are way higher so what
    was different? This kind of jump in times may indicate where the problem
    is.

    "dhaya" <shivdayal@hotmail.com> wrote:
    >
    >updates:
    >
    >ended up with temp tables, but still looks like its running very slow.
    >
    >I ended up with something like this, i got a feedback its very slow still
    >
    >
    >any idea, the columns that have index are below.
    >
    >IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >BEGIN
    > DROP table tempdb..Claims_Copay
    >END
    >
    >go
    >
    >IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >BEGIN
    > DROP table tempdb..Claims_Copay_Hosp
    >END
    >
    >go
    >
    >SELECT
    >CMC_CLCL_CLAIM.CLCL_ID,
    >CMC_SBSB_SUBSC.SBSB_ID,
    >CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >CMC_CLCL_CLAIM.PDPD_ID,
    >CMC_PDDS_PROD_DESC.PDDS_DESC
    >
    >INTO tempdb..Claims_Copay_Hosp
    >
    >FROM CMC_CLCL_CLAIM
    >INNER JOIN CMC_SBSB_SUBSC
    >ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >INNER JOIN CMC_CLHP_HOSP
    >ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >INNER JOIN CMC_PDPD_PRODUCT
    >ON CMC_CLCL_CLAIM.PDPD_ID = CMC_PDPD_PRODUCT.PDPD_ID
    >INNER JOIN CMC_PDDS_PROD_DESC
    >ON CMC_PDPD_PRODUCT.PDPD_ID = CMC_PDDS_PROD_DESC.PDPD_ID
    >
    >WHERE
    >CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>= '07/07/2003'

    >AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >
    >go
    >
    >IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >begin
    >CREATE CLUSTERED INDEX Index1 ON tempdb..Claims_Copay_Hosp (CLCL_ID)
    >end
    >
    >go
    >
    >IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >begin
    >select
    >tempdb..Claims_Copay_Hosp.CLCL_ID,
    >tempdb..Claims_Copay_Hosp.SBSB_ID,
    >tempdb..Claims_Copay_Hosp.CLCL_TOT_CHG,
    >tempdb..Claims_Copay_Hosp.CLCL_TOT_PAYABLE,
    >tempdb..Claims_Copay_Hosp.CLCL_CUR_STS,
    >tempdb..Claims_Copay_Hosp.CLCL_LOW_SVC_DT,
    >tempdb..Claims_Copay_Hosp.CLCL_HIGH_SVC_DT,
    >tempdb..Claims_Copay_Hosp.PDPD_ID,
    >tempdb..Claims_Copay_Hosp.PDDS_DESC,
    >CDML_COPAY_AMT,
    >CDML_CHG_AMT,
    >CDML_ALLOW,
    >CDML_SEQ_NO
    >into tempdb..Claims_Copay
    >from tempdb..Claims_Copay_Hosp
    >INNER JOIN CMC_CDML_CL_LINE
    >ON tempdb..Claims_Copay_Hosp.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >WHERE
    >CMC_CDML_CL_LINE .SESE_ID In
    >('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >'SPRB','SRBR','SRB','SBDR','SKNU')
    >and Exists
    >(
    >SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >WHERE
    >tempdb..Claims_Copay_Hosp.CLCL_ID = A.CLCL_ID
    >group by A.CLCL_ID
    >having count(distinct A.CDML_COPAY_AMT) > 1)
    >
    >end
    >
    >go
    >
    >IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >begin
    > DROP TABLE tempdb..Claims_Copay_Hosp
    >end
    >
    >go
    >
    >IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >begin
    > CREATE CLUSTERED INDEX Index2 ON tempdb..Claims_Copay (CLCL_ID)
    >end
    >
    >go
    >
    >IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >begin
    > select * from tempdb..Claims_Copay
    >end
    >
    >-------
    >
    >INDEXES IN TABLES
    >
    >CMCX_CLCL_CLUSTER clustered located on default


    > MEME_CK, CLCL_ID
    >CMCX_CLCL_PRIMARY nonclustered, unique located on default


    > CLCL_ID
    >CMCX_CLCL_SBSB_CK nonclustered located on default


    > SBSB_CK
    >CMCX_CLCL_PRPR_ID nonclustered located on default


    > PRPR_ID
    >CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    > ATXR_SOURCE_ID
    >CMCX_CLCL_DRAG_DT nonclustered located on default


    > CLCL_DRAG_DT
    >CMCX_CLCL_CUR_STS nonclustered located on default


    > CLCL_CUR_STS
    >CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    > CLCL_NEXT_REV_DT
    >CMCX_CLCL_PAID_DT nonclustered located on default


    > CLCL_PAID_DT
    >AIN_PP11_CLCL_CLAIM nonclustered located on default


    > CLCL_INPUT_DT
    >CMCX_CDML_CLUSTER clustered located on default


    > MEME_CK, CDML_FROM_DT
    >CMCX_CDML_PRIMARY nonclustered, unique located on default


    > CLCL_ID, CDML_SEQ_NO
    >CMCX_CDML_PRPR_ID nonclustered located on default


    > PRPR_ID
    >CMCX_CLHP_PRIMARY clustered located on default


    > MEME_CK, CLCL_ID
    >CMCX_CLHP_SECOND nonclustered, unique located on default


    > CLCL_ID


    >
    >
    >All the columns used in WHERE clause are without index
    >RECD_DT
    >SUB_TYPE
    >CLHP_FAC_TYPE
    >CLHP_BILL_CLASS
    >SESE_ID
    >
    >
    >"dhaya" <shivdayal@hotmail.com> wrote:
    >>
    >>I actually tried with each of the WHERE clauses and the indexes, results

    >are
    >>below. Looks like since its non-clustered join is slow, moreover all the
    >>fields used in the where don't have index ? In such cases, what is the

    best
    >>solution to deal with these kind of things ? Should i create some temp

    tables
    >>or use cursors etc.
    >>
    >>the results below.
    >>JOINS Query time results:
    >>
    >>' Direct select from CLCL table :
    >>Execution Time 20.
    >>SQL Server cpu time: 2000 ms. SQL Server elapsed time: 17893 ms.
    >>(0 rows affected)
    >>
    >>
    >>' When Joined with CDML table , type 'H', date params
    >>Execution Time 24.
    >>SQL Server cpu time: 2400 ms. SQL Server elapsed time: 20266 ms.
    >>(0 rows affected)
    >>
    >>
    >>' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >>included
    >>Execution Time 22.
    >>SQL Server cpu time: 2200 ms. SQL Server elapsed time: 82940 ms.
    >>
    >>
    >>' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >>included, looking for multiple copay's
    >>Execution Time 29.
    >>SQL Server cpu time: 2900 ms. SQL Server elapsed time: 30180 ms.
    >>
    >>
    >>' When Joined with the CLHP table and filtering with FAC_TYPE and BILL

    CLASS
    >>
    >>Execution Time 29.
    >>SQL Server cpu time: 2900 ms. SQL Server elapsed time: 25260 ms.
    >>
    >>
    >>'In another time, the query which does a direct select from table only

    takes
    >>more mins
    >>Execution Time 39.
    >>SQL Server cpu time: 3900 ms. SQL Server elapsed time: 29793 ms.
    >>
    >>
    >>'the query without the date params
    >>
    >>Execution Time 384.
    >>SQL Server cpu time: 38400 ms. SQL Server elapsed time: 748623 ms.
    >>
    >>' the query without date params and CLHP and COPAY
    >>Execution Time 158.
    >>SQL Server cpu time: 15800 ms. SQL Server elapsed time: 316946 ms.
    >>
    >>---------------------------------------------------------------------------INDEXES
    >>IN TABLES
    >>
    >>CMCX_CLCL_CLUSTER clustered located on default


    >
    >> MEME_CK, CLCL_ID
    >>CMCX_CLCL_PRIMARY nonclustered, unique located on default


    >
    >> CLCL_ID
    >>CMCX_CLCL_SBSB_CK nonclustered located on default


    >
    >> SBSB_CK
    >>CMCX_CLCL_PRPR_ID nonclustered located on default


    >
    >> PRPR_ID
    >>CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    >
    >> ATXR_SOURCE_ID
    >>CMCX_CLCL_DRAG_DT nonclustered located on default


    >
    >> CLCL_DRAG_DT
    >>CMCX_CLCL_CUR_STS nonclustered located on default


    >
    >> CLCL_CUR_STS
    >>CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    >
    >> CLCL_NEXT_REV_DT
    >>CMCX_CLCL_PAID_DT nonclustered located on default


    >
    >> CLCL_PAID_DT
    >>AIN_PP11_CLCL_CLAIM nonclustered located on default


    >
    >> CLCL_INPUT_DT
    >>CMCX_CDML_CLUSTER clustered located on default


    >
    >> MEME_CK, CDML_FROM_DT
    >>CMCX_CDML_PRIMARY nonclustered, unique located on default


    >
    >> CLCL_ID, CDML_SEQ_NO
    >>CMCX_CDML_PRPR_ID nonclustered located on default


    >
    >> PRPR_ID
    >>CMCX_CLHP_PRIMARY clustered located on default


    >
    >> MEME_CK, CLCL_ID
    >>CMCX_CLHP_SECOND nonclustered, unique located on default


    >
    >> CLCL_ID


    >
    >>


    >
    >> Columns used without index
    >>RECD_DT
    >>SUB_TYPE
    >>CLHP_FAC_TYPE
    >>CLHP_BILL_CLASS
    >>SESE_ID
    >>
    >>
    >>
    >>
    >>"KevinV" <none@none.com> wrote:
    >>>
    >>>Here's some various comments below.
    >>>
    >>>The 'Show Execution Plan' option should show you a graphical query plan.
    >>>Each step in the plan has a cost associated with it. I don't know what

    >version
    >>>of sql you have and/or if there is a text only display of the plan. I

    don't
    >>>see the costs in your query plan, although I don't know if there is enough
    >>>steps anyway. The graphical plan may just show two steps which doesn't

    >help
    >>>much.
    >>>
    >>>I would use the INs instead of ORs. I'm not sure if it makes a difference
    >>>performance wise but it is much more readable.
    >>>
    >>>Make sure your order by column is an index. It may still do a table scan
    >>>anyway.
    >>>
    >>>It looks like all the other columns have indexes.
    >>>
    >>>Another thing I resort to at times is simplifying the query to determine
    >>>what causes it to be slow. For example, you may want to remove the subselect
    >>>and run the query, then add it back and run it. Same with the joins or

    >the
    >>>column checks on the where clause. You may come across one particular

    thing
    >>>that makes the query slow.
    >>>
    >>>That's about all I can think of now. Hope some of it helps.
    >>>
    >>>
    >>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>
    >>>>By looking at the plan, i don't know how to find how to find which one

    >>is
    >>>>slow here. Looks like the CMC_CLHP_HOSP
    >>>>
    >>>>"dhaya" <shivdayal@yahoo.com> wrote:
    >>>>>
    >>>>>I tried replacing IN with OR
    >>>>>And then looked like the tables are too big so join is slow.
    >>>>>I am thinking only creating temp tables and indices may work, don't

    know
    >>>>>if anything else would work
    >>>>>
    >>>>>have the query and plan below
    >>>>>
    >>>>>SELECT CLCL.CLCL_ID,
    >>>>> SBSB.SBSB_ID,
    >>>>> CLCL.CLCL_TOT_CHG,
    >>>>> CLCL.CLCL_TOT_PAYABLE,
    >>>>> CLCL.CLCL_CUR_STS,
    >>>>> CLCL.CLCL_LOW_SVC_DT,
    >>>>> CLCL.CLCL_HIGH_SVC_DT,
    >>>>> CLCL.PDPD_ID,
    >>>>> CDML1.CDML_COPAY_AMT,
    >>>>> CDML1.CDML_CHG_AMT,
    >>>>> CDML1.CDML_ALLOW,
    >>>>> CDML1.CDML_SEQ_NO
    >>>>>
    >>>>>FROM CMC_CLCL_CLAIM CLCL
    >>>>>
    >>>>>INNER JOIN CMC_SBSB_SUBSC SBSB
    >>>>>ON CLCL.SBSB_CK = SBSB.SBSB_CK
    >>>>>
    >>>>>INNER JOIN CMC_CDML_CL_LINE CDML1
    >>>>>ON CLCL.CLCL_ID = CDML1.CLCL_ID
    >>>>>
    >>>>>INNER JOIN CMC_CLHP_HOSP CLHP
    >>>>>ON CLCL.CLCL_ID = CLHP.CLCL_ID
    >>>>>
    >>>>>WHERE
    >>>>>
    >>>>>(
    >>>>>CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS'
    >>>>OR
    >>>>>CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS'

    >>OR
    >>>>>CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    >>>>> OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID=

    'ICUS'
    >>>>>OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID=

    'NUR'
    >>>>>OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID= 'PRBS'
    >>>>> OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID=

    >'RB'
    >>>>>OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID= 'RBD'
    >>>>OR
    >>>>>CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'
    >>>>
    >>>>> OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID=

    >'SBDR'
    >>>>>OR CDML1.SESE_ID= 'SKNU'
    >>>>>)
    >>>>>
    >>>>>AND CLCL.CLCL_CL_SUB_TYPE='H'
    >>>>>
    >>>>>AND CLCL.CLCL_ID IN
    >>>>>(
    >>>>>SELECT SUB_CDML1.CLCL_ID
    >>>>>FROM CMC_CDML_CL_LINE SUB_CDML1
    >>>>>INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    >>>>>ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    >>>>>WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    >>>>>AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    >>>>>)
    >>>>>
    >>>>>AND CLHP.CLHP_FAC_TYPE = '1'
    >>>>>
    >>>>>AND
    >>>>>(
    >>>>>CLHP.CLHP_BILL_CLASS ='E' OR
    >>>>>CLHP.CLHP_BILL_CLASS ='U' OR
    >>>>>CLHP.CLHP_BILL_CLASS ='S' OR
    >>>>>CLHP.CLHP_BILL_CLASS ='3'
    >>>>>)
    >>>>>
    >>>>>ORDER BY CLCL.PDPD_ID
    >>>>>
    >>>>>
    >>>>>
    >>>>>----------
    >>>>>
    >>>>>QUERY PLAN FOR STATEMENT 1 (at line 1).
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>> STEP 1
    >>>>> The type of query is INSERT.
    >>>>> The update mode is direct.
    >>>>> Worktable1 created for ORDER BY.
    >>>>>
    >>>>>
    >>>>> FROM TABLE
    >>>>> CMC_CLCL_CLAIM
    >>>>> CLCL
    >>>>> Nested iteration.
    >>>>> Table Scan.
    >>>>> Forward scan.
    >>>>> Positioning at start of table.
    >>>>> Using I/O Size 16 Kbytes for data pages.
    >>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>
    >>>>>
    >>>>> FROM TABLE
    >>>>> CMC_SBSB_SUBSC
    >>>>> SBSB
    >>>>> Nested iteration.
    >>>>> Index : CMCX_SBSB_PRIMARY
    >>>>> Forward scan.
    >>>>> Positioning by key.
    >>>>> Keys are:
    >>>>> SBSB_CK ASC
    >>>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>
    >>>>>
    >>>>> FROM TABLE
    >>>>> CMC_CDML_CL_LINE
    >>>>> CDML1
    >>>>> Nested iteration.
    >>>>> Index : CMCX_CDML_PRIMARY
    >>>>> Forward scan.
    >>>>> Positioning by key.
    >>>>> Keys are:
    >>>>> CLCL_ID ASC
    >>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>
    >>>>>
    >>>>> FROM TABLE
    >>>>> CMC_CLHP_HOSP
    >>>>> CLHP
    >>>>> Nested iteration.
    >>>>> Index : CMCX_CLHP_SECOND
    >>>>> Forward scan.
    >>>>> Positioning by key.
    >>>>> Keys are:
    >>>>> CLCL_ID ASC
    >>>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>
    >>>>>
    >>>>> FROM TABLE
    >>>>> CMC_CDML_CL_LINE
    >>>>> SUB_CDML1
    >>>>> EXISTS TABLE : nested iteration.
    >>>>> Index : CMCX_CDML_PRIMARY
    >>>>> Forward scan.
    >>>>> Positioning by key.
    >>>>> Keys are:
    >>>>> CLCL_ID ASC
    >>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>
    >>>>>
    >>>>> FROM TABLE
    >>>>> CMC_CDML_CL_LINE
    >>>>> SUB_CDML2
    >>>>> EXISTS TABLE : nested iteration.
    >>>>> Index : CMCX_CDML_PRIMARY
    >>>>> Forward scan.
    >>>>> Positioning by key.
    >>>>> Keys are:
    >>>>> CLCL_ID ASC
    >>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>> With LRU Buffer Replacement Strategy for index leaf pages.
    >>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>> TO TABLE
    >>>>> Worktable1.
    >>>>>
    >>>>>
    >>>>> STEP 2
    >>>>> The type of query is SELECT.
    >>>>> This step involves sorting.
    >>>>>
    >>>>>
    >>>>> FROM TABLE
    >>>>> Worktable1.
    >>>>> Using GETSORTED
    >>>>> Table Scan.
    >>>>> Forward scan.
    >>>>> Positioning at start of table.
    >>>>> Using I/O Size 16 Kbytes for data pages.
    >>>>> With MRU Buffer Replacement Strategy for data pages.
    >>>>>
    >>>>>
    >>>>>Parse and Compile Time 0.
    >>>>>SQL Server cpu time: 0 ms.
    >>>>>Server Message: Number 1562, Severity 10
    >>>>>Server 'aims_dev2', Line 1:
    >>>>>The sort for Worktable1 is done in Serial
    >>>>>Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202 apf=0
    >>>>>total=150202), physical reads: (regular=5584 apf=13331 total=18915),

    >apf
    >>>>>IOs used=13228
    >>>>>Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431
    >>>apf=11
    >>>>>total=233442), physical reads: (regular=4573 apf=412 total=4985), apf

    >>IOs
    >>>>>used=209
    >>>>>Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    >>>>>apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    >>>>>apf IOs used=88751
    >>>>>Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084

    apf=0
    >>>>>total=17084), physical reads: (regular=2117 apf=18 total=2135), apf

    IOs
    >>>>used=18
    >>>>>
    >>>>>Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170 apf=0
    >>>>>total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

    >>
    >>>>>Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038

    >apf=0
    >>>>>total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15
    >>>>
    >>>>>Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    >>>>>physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    >>>>>Total writes for this command: 6
    >>>>>
    >>>>>Execution Time 144.
    >>>>>SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    >>>>>(1 row affected)
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>"KevinV" <none@none.com> wrote:
    >>>>>>
    >>>>>>Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >>>>>>
    >>>>>>There is a lot to tuning queries. The query analyzer execution plan

    >is
    >>>>usually
    >>>>>>a good place to start. Look for the step in the execution plan that

    >has
    >>>>>the
    >>>>>>highest cost and start there to look for optimizations. Generally adding
    >>>>>>indexes for certain columns, like columns referenced in joins and where
    >>>>>clauses
    >>>>>>is what will improve the query the most. If you see steps that are

    doing
    >>>>>>table scans then that likely means you need an index. It is also possible
    >>>>>>that your query will just be slow depending on the number of rows of

    >>data,
    >>>>>>physical placement of data, log and index database files and the configuration
    >>>>>>of the server.
    >>>>>>
    >>>>>>
    >>>>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>>>
    >>>>>>>any way to this query , its very slow.
    >>>>>>>
    >>>>>>>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >>>>>>> CMC_SBSB_SUBSC.SBSB_ID,
    >>>>>>> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>>>>>> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>>>>>> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>>>>>> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>>>>>> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>>>>>> CMC_CLCL_CLAIM.PDPD_ID,
    >>>>>>> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >>>>>>> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >>>>>>> CMC_CDML_CL_LINE.CDML_ALLOW,
    >>>>>>> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>>>>>>into adhocdb..JP_COPAY_ERRORS
    >>>>>>>FROM CMC_CLCL_CLAIM
    >>>>>>>
    >>>>>>>INNER JOIN CMC_SBSB_SUBSC
    >>>>>>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>>>>>
    >>>>>>>INNER JOIN CMC_CDML_CL_LINE
    >>>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>>>>>
    >>>>>>>INNER JOIN CMC_CLHP_HOSP
    >>>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>>>>>
    >>>>>>>WHERE
    >>>>>>>
    >>>>>>>CMC_CDML_CL_LINE.SESE_ID In
    >>>>>>> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>>>>>> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>>>>>> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>>>>>> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>>>>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>>>>>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>>>>>= '07/07/2003'
    >>>>>>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>>>>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>>>>>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>>>>>>(
    >>>>>>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>>>>>INNER JOIN CMC_CDML_CL_LINE B
    >>>>>>>ON A.CLCL_ID = B.CLCL_ID
    >>>>>>> WHERE
    >>>>>>>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>>>>>>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>>>>>>)
    >>>>>>>
    >>>>>>>
    >>>>>>>The last inner query IN is to get from a detail table rows which have
    >>>>different
    >>>>>>>copay amount
    >>>>>>>
    >>>>>>>example
    >>>>>>>111 will be selected in below case
    >>>>>>>
    >>>>>>>claim_id sequence_no copay
    >>>>>>>111 1 10.0
    >>>>>>>111 2 20.0
    >>>>>>>222 1 100.0
    >>>>>>>222 2 100.0
    >>>>>>>
    >>>>>>>Thank you!
    >>>>>>>dhaya
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  10. #10
    dhaya Guest

    Re: query tuning


    Thanks a lot, that helped me.

    finally i am getting to run by running records worth of one day etc.

    the only piece which is not tuned for me is this below

    table
    CLCL (master clustered)
    CLCL_ID (PK)

    CDML (detail)
    CLCL_ID FK (Clustered)
    COPAY_AMT

    The record example is

    clcl_id copay
    1 200
    1 400
    1 500
    2 100
    2 100
    3 1
    3 2

    i am trying to catch when copay is differeny for same clcl_id.

    can the following be more tuned etc.

    select
    tempdb..Claims_Copay_Hosp.CLCL_ID,
    tempdb..Claims_Copay_Hosp.CLCL_TOT_CHG,
    tempdb..Claims_Copay_Hosp.CLCL_TOT_PAYABLE,
    tempdb..Claims_Copay_Hosp.CLCL_CUR_STS,
    tempdb..Claims_Copay_Hosp.CLCL_LOW_SVC_DT,
    tempdb..Claims_Copay_Hosp.CLCL_HIGH_SVC_DT,
    tempdb..Claims_Copay_Hosp.PDPD_ID,
    tempdb..Claims_Copay_Hosp.SBSB_CK,
    CDML1.CDML_SEQ_NO,
    CDML1.CDML_COPAY_AMT,
    CDML1.CDML_CHG_AMT,
    CDML1.CDML_ALLOW,
    CDML1.IDCD_ID,
    CDML1.IDCD_ID_REL,
    ' ' AS SBSB_ID,
    '

    ' AS PDPD_DESC

    INTO tempdb..Claims_Copay

    FROM tempdb..Claims_Copay_Hosp

    INNER JOIN CMC_CDML_CL_LINE CDML1
    ON tempdb..Claims_Copay_Hosp.CLCL_ID = CDML1.CLCL_ID

    WHERE
    EXISTS
    (

    SELECT
    CDML2.CLCL_ID

    FROM CMC_CDML_CL_LINE CDML2

    WHERE
    tempdb..Claims_Copay_Hosp.CLCL_ID = CDML2.CLCL_ID
    AND CDML1.CLCL_ID = CDML2.CLCL_ID
    AND CDML1.CDML_COPAY_AMT > 0
    AND CDML2.CDML_COPAY_AMT > 0
    GROUP BY CDML2.CLCL_ID
    HAVING COUNT (DISTINCT CDML2.CDML_COPAY_AMT) > 1
    )


    "KevinV" <none@none.com> wrote:
    >
    >Columns used in where clauses should generally have indexes on them. If

    the
    >where clause columns donít have indexes, the query optimizer will have no
    >choice but to scan every row in the table. You should add indexes at least
    >for testing and determine if they do any good. That goes for columns used
    >in joins and order by. You can add the indexes, rerun the query and check
    >the execution plan to see if they do any good.
    >
    >In particular, you want an index on the column that you are using in the
    >where clause that limits the result set the most. I would guess this is

    probably
    >the RECD_DT column.
    >
    >Non-clustered indexes versus clustered indexes wonít make much difference
    >in the query performance. Indexes versus no indexes makes a larger difference.
    >
    >Donít use cursors, that is the worst option. Creating any preliminary temp
    >tables is not going to do you any good for the most part.
    >
    >In your list of test times, what happened with the ones that takes 384 seconds
    >and the 158 seconds. The others arenít bad, these are way higher so what
    >was different? This kind of jump in times may indicate where the problem
    >is.
    >
    >"dhaya" <shivdayal@hotmail.com> wrote:
    >>
    >>updates:
    >>
    >>ended up with temp tables, but still looks like its running very slow.


    >>
    >>I ended up with something like this, i got a feedback its very slow still
    >>
    >>
    >>any idea, the columns that have index are below.
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>BEGIN
    >> DROP table tempdb..Claims_Copay
    >>END
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>BEGIN
    >> DROP table tempdb..Claims_Copay_Hosp
    >>END
    >>
    >>go
    >>
    >>SELECT
    >>CMC_CLCL_CLAIM.CLCL_ID,
    >>CMC_SBSB_SUBSC.SBSB_ID,
    >>CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>CMC_CLCL_CLAIM.PDPD_ID,
    >>CMC_PDDS_PROD_DESC.PDDS_DESC
    >>
    >>INTO tempdb..Claims_Copay_Hosp
    >>
    >>FROM CMC_CLCL_CLAIM
    >>INNER JOIN CMC_SBSB_SUBSC
    >>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>INNER JOIN CMC_CLHP_HOSP
    >>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>INNER JOIN CMC_PDPD_PRODUCT
    >>ON CMC_CLCL_CLAIM.PDPD_ID = CMC_PDPD_PRODUCT.PDPD_ID
    >>INNER JOIN CMC_PDDS_PROD_DESC
    >>ON CMC_PDPD_PRODUCT.PDPD_ID = CMC_PDDS_PROD_DESC.PDPD_ID
    >>
    >>WHERE
    >>CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>= '07/07/2003'

    >>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>begin
    >>CREATE CLUSTERED INDEX Index1 ON tempdb..Claims_Copay_Hosp (CLCL_ID)
    >>end
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>begin
    >>select
    >>tempdb..Claims_Copay_Hosp.CLCL_ID,
    >>tempdb..Claims_Copay_Hosp.SBSB_ID,
    >>tempdb..Claims_Copay_Hosp.CLCL_TOT_CHG,
    >>tempdb..Claims_Copay_Hosp.CLCL_TOT_PAYABLE,
    >>tempdb..Claims_Copay_Hosp.CLCL_CUR_STS,
    >>tempdb..Claims_Copay_Hosp.CLCL_LOW_SVC_DT,
    >>tempdb..Claims_Copay_Hosp.CLCL_HIGH_SVC_DT,
    >>tempdb..Claims_Copay_Hosp.PDPD_ID,
    >>tempdb..Claims_Copay_Hosp.PDDS_DESC,
    >>CDML_COPAY_AMT,
    >>CDML_CHG_AMT,
    >>CDML_ALLOW,
    >>CDML_SEQ_NO
    >>into tempdb..Claims_Copay
    >>from tempdb..Claims_Copay_Hosp
    >>INNER JOIN CMC_CDML_CL_LINE
    >>ON tempdb..Claims_Copay_Hosp.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>WHERE
    >>CMC_CDML_CL_LINE .SESE_ID In
    >>('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>'SPRB','SRBR','SRB','SBDR','SKNU')
    >>and Exists
    >>(
    >>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>WHERE
    >>tempdb..Claims_Copay_Hosp.CLCL_ID = A.CLCL_ID
    >>group by A.CLCL_ID
    >>having count(distinct A.CDML_COPAY_AMT) > 1)
    >>
    >>end
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>begin
    >> DROP TABLE tempdb..Claims_Copay_Hosp
    >>end
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>begin
    >> CREATE CLUSTERED INDEX Index2 ON tempdb..Claims_Copay (CLCL_ID)
    >>end
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>begin
    >> select * from tempdb..Claims_Copay
    >>end
    >>
    >>-------
    >>
    >>INDEXES IN TABLES
    >>
    >>CMCX_CLCL_CLUSTER clustered located on default


    >
    >> MEME_CK, CLCL_ID
    >>CMCX_CLCL_PRIMARY nonclustered, unique located on default


    >
    >> CLCL_ID
    >>CMCX_CLCL_SBSB_CK nonclustered located on default


    >
    >> SBSB_CK
    >>CMCX_CLCL_PRPR_ID nonclustered located on default


    >
    >> PRPR_ID
    >>CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    >
    >> ATXR_SOURCE_ID
    >>CMCX_CLCL_DRAG_DT nonclustered located on default


    >
    >> CLCL_DRAG_DT
    >>CMCX_CLCL_CUR_STS nonclustered located on default


    >
    >> CLCL_CUR_STS
    >>CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    >
    >> CLCL_NEXT_REV_DT
    >>CMCX_CLCL_PAID_DT nonclustered located on default


    >
    >> CLCL_PAID_DT
    >>AIN_PP11_CLCL_CLAIM nonclustered located on default


    >
    >> CLCL_INPUT_DT
    >>CMCX_CDML_CLUSTER clustered located on default


    >
    >> MEME_CK, CDML_FROM_DT
    >>CMCX_CDML_PRIMARY nonclustered, unique located on default


    >
    >> CLCL_ID, CDML_SEQ_NO
    >>CMCX_CDML_PRPR_ID nonclustered located on default


    >
    >> PRPR_ID
    >>CMCX_CLHP_PRIMARY clustered located on default


    >
    >> MEME_CK, CLCL_ID
    >>CMCX_CLHP_SECOND nonclustered, unique located on default


    >
    >> CLCL_ID


    >
    >>
    >>
    >>All the columns used in WHERE clause are without index
    >>RECD_DT
    >>SUB_TYPE
    >>CLHP_FAC_TYPE
    >>CLHP_BILL_CLASS
    >>SESE_ID
    >>
    >>
    >>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>
    >>>I actually tried with each of the WHERE clauses and the indexes, results

    >>are
    >>>below. Looks like since its non-clustered join is slow, moreover all the
    >>>fields used in the where don't have index ? In such cases, what is the

    >best
    >>>solution to deal with these kind of things ? Should i create some temp

    >tables
    >>>or use cursors etc.
    >>>
    >>>the results below.
    >>>JOINS Query time results:
    >>>
    >>>' Direct select from CLCL table :
    >>>Execution Time 20.
    >>>SQL Server cpu time: 2000 ms. SQL Server elapsed time: 17893 ms.
    >>>(0 rows affected)
    >>>
    >>>
    >>>' When Joined with CDML table , type 'H', date params
    >>>Execution Time 24.
    >>>SQL Server cpu time: 2400 ms. SQL Server elapsed time: 20266 ms.
    >>>(0 rows affected)
    >>>
    >>>
    >>>' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >>>included
    >>>Execution Time 22.
    >>>SQL Server cpu time: 2200 ms. SQL Server elapsed time: 82940 ms.
    >>>
    >>>
    >>>' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >>>included, looking for multiple copay's
    >>>Execution Time 29.
    >>>SQL Server cpu time: 2900 ms. SQL Server elapsed time: 30180 ms.
    >>>
    >>>
    >>>' When Joined with the CLHP table and filtering with FAC_TYPE and BILL

    >CLASS
    >>>
    >>>Execution Time 29.
    >>>SQL Server cpu time: 2900 ms. SQL Server elapsed time: 25260 ms.
    >>>
    >>>
    >>>'In another time, the query which does a direct select from table only

    >takes
    >>>more mins
    >>>Execution Time 39.
    >>>SQL Server cpu time: 3900 ms. SQL Server elapsed time: 29793 ms.
    >>>
    >>>
    >>>'the query without the date params
    >>>
    >>>Execution Time 384.
    >>>SQL Server cpu time: 38400 ms. SQL Server elapsed time: 748623 ms.
    >>>
    >>>' the query without date params and CLHP and COPAY
    >>>Execution Time 158.
    >>>SQL Server cpu time: 15800 ms. SQL Server elapsed time: 316946 ms.
    >>>
    >>>---------------------------------------------------------------------------INDEXES
    >>>IN TABLES
    >>>
    >>>CMCX_CLCL_CLUSTER clustered located on default


    >
    >>
    >>> MEME_CK, CLCL_ID
    >>>CMCX_CLCL_PRIMARY nonclustered, unique located on default

    >
    >>
    >>> CLCL_ID
    >>>CMCX_CLCL_SBSB_CK nonclustered located on default


    >
    >>
    >>> SBSB_CK
    >>>CMCX_CLCL_PRPR_ID nonclustered located on default


    >
    >>
    >>> PRPR_ID
    >>>CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    >
    >>
    >>> ATXR_SOURCE_ID
    >>>CMCX_CLCL_DRAG_DT nonclustered located on default


    >
    >>
    >>> CLCL_DRAG_DT
    >>>CMCX_CLCL_CUR_STS nonclustered located on default


    >
    >>
    >>> CLCL_CUR_STS
    >>>CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    >
    >>
    >>> CLCL_NEXT_REV_DT
    >>>CMCX_CLCL_PAID_DT nonclustered located on default


    >
    >>
    >>> CLCL_PAID_DT
    >>>AIN_PP11_CLCL_CLAIM nonclustered located on default


    >
    >>
    >>> CLCL_INPUT_DT
    >>>CMCX_CDML_CLUSTER clustered located on default


    >
    >>
    >>> MEME_CK, CDML_FROM_DT
    >>>CMCX_CDML_PRIMARY nonclustered, unique located on default


    >
    >>
    >>> CLCL_ID, CDML_SEQ_NO
    >>>CMCX_CDML_PRPR_ID nonclustered located on default


    >
    >>
    >>> PRPR_ID
    >>>CMCX_CLHP_PRIMARY clustered located on default


    >
    >>
    >>> MEME_CK, CLCL_ID
    >>>CMCX_CLHP_SECOND nonclustered, unique located on default


    >
    >>
    >>> CLCL_ID


    >
    >>
    >>>


    >
    >>
    >>> Columns used without index
    >>>RECD_DT
    >>>SUB_TYPE
    >>>CLHP_FAC_TYPE
    >>>CLHP_BILL_CLASS
    >>>SESE_ID
    >>>
    >>>
    >>>
    >>>
    >>>"KevinV" <none@none.com> wrote:
    >>>>
    >>>>Here's some various comments below.
    >>>>
    >>>>The 'Show Execution Plan' option should show you a graphical query plan.
    >>>>Each step in the plan has a cost associated with it. I don't know what

    >>version
    >>>>of sql you have and/or if there is a text only display of the plan. I

    >don't
    >>>>see the costs in your query plan, although I don't know if there is enough
    >>>>steps anyway. The graphical plan may just show two steps which doesn't

    >>help
    >>>>much.
    >>>>
    >>>>I would use the INs instead of ORs. I'm not sure if it makes a difference
    >>>>performance wise but it is much more readable.
    >>>>
    >>>>Make sure your order by column is an index. It may still do a table scan
    >>>>anyway.
    >>>>
    >>>>It looks like all the other columns have indexes.
    >>>>
    >>>>Another thing I resort to at times is simplifying the query to determine
    >>>>what causes it to be slow. For example, you may want to remove the subselect
    >>>>and run the query, then add it back and run it. Same with the joins or

    >>the
    >>>>column checks on the where clause. You may come across one particular

    >thing
    >>>>that makes the query slow.
    >>>>
    >>>>That's about all I can think of now. Hope some of it helps.
    >>>>
    >>>>
    >>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>
    >>>>>By looking at the plan, i don't know how to find how to find which one
    >>>is
    >>>>>slow here. Looks like the CMC_CLHP_HOSP
    >>>>>
    >>>>>"dhaya" <shivdayal@yahoo.com> wrote:
    >>>>>>
    >>>>>>I tried replacing IN with OR
    >>>>>>And then looked like the tables are too big so join is slow.
    >>>>>>I am thinking only creating temp tables and indices may work, don't

    >know
    >>>>>>if anything else would work
    >>>>>>
    >>>>>>have the query and plan below
    >>>>>>
    >>>>>>SELECT CLCL.CLCL_ID,
    >>>>>> SBSB.SBSB_ID,
    >>>>>> CLCL.CLCL_TOT_CHG,
    >>>>>> CLCL.CLCL_TOT_PAYABLE,
    >>>>>> CLCL.CLCL_CUR_STS,
    >>>>>> CLCL.CLCL_LOW_SVC_DT,
    >>>>>> CLCL.CLCL_HIGH_SVC_DT,
    >>>>>> CLCL.PDPD_ID,
    >>>>>> CDML1.CDML_COPAY_AMT,
    >>>>>> CDML1.CDML_CHG_AMT,
    >>>>>> CDML1.CDML_ALLOW,
    >>>>>> CDML1.CDML_SEQ_NO
    >>>>>>
    >>>>>>FROM CMC_CLCL_CLAIM CLCL
    >>>>>>
    >>>>>>INNER JOIN CMC_SBSB_SUBSC SBSB
    >>>>>>ON CLCL.SBSB_CK = SBSB.SBSB_CK
    >>>>>>
    >>>>>>INNER JOIN CMC_CDML_CL_LINE CDML1
    >>>>>>ON CLCL.CLCL_ID = CDML1.CLCL_ID
    >>>>>>
    >>>>>>INNER JOIN CMC_CLHP_HOSP CLHP
    >>>>>>ON CLCL.CLCL_ID = CLHP.CLCL_ID
    >>>>>>
    >>>>>>WHERE
    >>>>>>
    >>>>>>(
    >>>>>>CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS'
    >>>>>OR
    >>>>>>CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS'
    >>>OR
    >>>>>>CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    >>>>>> OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID=

    >'ICUS'
    >>>>>>OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID=

    >'NUR'
    >>>>>>OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID=

    'PRBS'
    >>>>>> OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID=

    >>'RB'
    >>>>>>OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID= 'RBD'
    >>>>>OR
    >>>>>>CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'
    >>>>>
    >>>>>> OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID=

    >>'SBDR'
    >>>>>>OR CDML1.SESE_ID= 'SKNU'
    >>>>>>)
    >>>>>>
    >>>>>>AND CLCL.CLCL_CL_SUB_TYPE='H'
    >>>>>>
    >>>>>>AND CLCL.CLCL_ID IN
    >>>>>>(
    >>>>>>SELECT SUB_CDML1.CLCL_ID
    >>>>>>FROM CMC_CDML_CL_LINE SUB_CDML1
    >>>>>>INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    >>>>>>ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    >>>>>>WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    >>>>>>AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    >>>>>>)
    >>>>>>
    >>>>>>AND CLHP.CLHP_FAC_TYPE = '1'
    >>>>>>
    >>>>>>AND
    >>>>>>(
    >>>>>>CLHP.CLHP_BILL_CLASS ='E' OR
    >>>>>>CLHP.CLHP_BILL_CLASS ='U' OR
    >>>>>>CLHP.CLHP_BILL_CLASS ='S' OR
    >>>>>>CLHP.CLHP_BILL_CLASS ='3'
    >>>>>>)
    >>>>>>
    >>>>>>ORDER BY CLCL.PDPD_ID
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>----------
    >>>>>>
    >>>>>>QUERY PLAN FOR STATEMENT 1 (at line 1).
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>> STEP 1
    >>>>>> The type of query is INSERT.
    >>>>>> The update mode is direct.
    >>>>>> Worktable1 created for ORDER BY.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CLCL_CLAIM
    >>>>>> CLCL
    >>>>>> Nested iteration.
    >>>>>> Table Scan.
    >>>>>> Forward scan.
    >>>>>> Positioning at start of table.
    >>>>>> Using I/O Size 16 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_SBSB_SUBSC
    >>>>>> SBSB
    >>>>>> Nested iteration.
    >>>>>> Index : CMCX_SBSB_PRIMARY
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> SBSB_CK ASC
    >>>>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CDML_CL_LINE
    >>>>>> CDML1
    >>>>>> Nested iteration.
    >>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> CLCL_ID ASC
    >>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CLHP_HOSP
    >>>>>> CLHP
    >>>>>> Nested iteration.
    >>>>>> Index : CMCX_CLHP_SECOND
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> CLCL_ID ASC
    >>>>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CDML_CL_LINE
    >>>>>> SUB_CDML1
    >>>>>> EXISTS TABLE : nested iteration.
    >>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> CLCL_ID ASC
    >>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CDML_CL_LINE
    >>>>>> SUB_CDML2
    >>>>>> EXISTS TABLE : nested iteration.
    >>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> CLCL_ID ASC
    >>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>> TO TABLE
    >>>>>> Worktable1.
    >>>>>>
    >>>>>>
    >>>>>> STEP 2
    >>>>>> The type of query is SELECT.
    >>>>>> This step involves sorting.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> Worktable1.
    >>>>>> Using GETSORTED
    >>>>>> Table Scan.
    >>>>>> Forward scan.
    >>>>>> Positioning at start of table.
    >>>>>> Using I/O Size 16 Kbytes for data pages.
    >>>>>> With MRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>>Parse and Compile Time 0.
    >>>>>>SQL Server cpu time: 0 ms.
    >>>>>>Server Message: Number 1562, Severity 10
    >>>>>>Server 'aims_dev2', Line 1:
    >>>>>>The sort for Worktable1 is done in Serial
    >>>>>>Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202

    apf=0
    >>>>>>total=150202), physical reads: (regular=5584 apf=13331 total=18915),

    >>apf
    >>>>>>IOs used=13228
    >>>>>>Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431
    >>>>apf=11
    >>>>>>total=233442), physical reads: (regular=4573 apf=412 total=4985), apf
    >>>IOs
    >>>>>>used=209
    >>>>>>Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    >>>>>>apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    >>>>>>apf IOs used=88751
    >>>>>>Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084

    >apf=0
    >>>>>>total=17084), physical reads: (regular=2117 apf=18 total=2135), apf

    >IOs
    >>>>>used=18
    >>>>>>
    >>>>>>Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170

    apf=0
    >>>>>>total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    >>>
    >>>>>>Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038

    >>apf=0
    >>>>>>total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15
    >>>>>
    >>>>>>Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    >>>>>>physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    >>>>>>Total writes for this command: 6
    >>>>>>
    >>>>>>Execution Time 144.
    >>>>>>SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    >>>>>>(1 row affected)
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>"KevinV" <none@none.com> wrote:
    >>>>>>>
    >>>>>>>Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >>>>>>>
    >>>>>>>There is a lot to tuning queries. The query analyzer execution plan

    >>is
    >>>>>usually
    >>>>>>>a good place to start. Look for the step in the execution plan that

    >>has
    >>>>>>the
    >>>>>>>highest cost and start there to look for optimizations. Generally

    adding
    >>>>>>>indexes for certain columns, like columns referenced in joins and

    where
    >>>>>>clauses
    >>>>>>>is what will improve the query the most. If you see steps that are

    >doing
    >>>>>>>table scans then that likely means you need an index. It is also possible
    >>>>>>>that your query will just be slow depending on the number of rows

    of
    >>>data,
    >>>>>>>physical placement of data, log and index database files and the configuration
    >>>>>>>of the server.
    >>>>>>>
    >>>>>>>
    >>>>>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>>>>
    >>>>>>>>any way to this query , its very slow.
    >>>>>>>>
    >>>>>>>>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >>>>>>>> CMC_SBSB_SUBSC.SBSB_ID,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>>>>>>> CMC_CLCL_CLAIM.PDPD_ID,
    >>>>>>>> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >>>>>>>> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >>>>>>>> CMC_CDML_CL_LINE.CDML_ALLOW,
    >>>>>>>> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>>>>>>>into adhocdb..JP_COPAY_ERRORS
    >>>>>>>>FROM CMC_CLCL_CLAIM
    >>>>>>>>
    >>>>>>>>INNER JOIN CMC_SBSB_SUBSC
    >>>>>>>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>>>>>>
    >>>>>>>>INNER JOIN CMC_CDML_CL_LINE
    >>>>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>>>>>>
    >>>>>>>>INNER JOIN CMC_CLHP_HOSP
    >>>>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>>>>>>
    >>>>>>>>WHERE
    >>>>>>>>
    >>>>>>>>CMC_CDML_CL_LINE.SESE_ID In
    >>>>>>>> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>>>>>>> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>>>>>>> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>>>>>>> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>>>>>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>>>>>>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>>>>>>= '07/07/2003'
    >>>>>>>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>>>>>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>>>>>>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>>>>>>>(
    >>>>>>>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>>>>>>INNER JOIN CMC_CDML_CL_LINE B
    >>>>>>>>ON A.CLCL_ID = B.CLCL_ID
    >>>>>>>> WHERE
    >>>>>>>>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>>>>>>>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>>>>>>>)
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>The last inner query IN is to get from a detail table rows which

    have
    >>>>>different
    >>>>>>>>copay amount
    >>>>>>>>
    >>>>>>>>example
    >>>>>>>>111 will be selected in below case
    >>>>>>>>
    >>>>>>>>claim_id sequence_no copay
    >>>>>>>>111 1 10.0
    >>>>>>>>111 2 20.0
    >>>>>>>>222 1 100.0
    >>>>>>>>222 2 100.0
    >>>>>>>>
    >>>>>>>>Thank you!
    >>>>>>>>dhaya
    >>>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  11. #11
    dhaya Guest

    Re: query tuning


    Thanks a lot, that helped me.

    finally i am getting to run by running records worth of one day etc.

    the only piece which is not tuned for me is this below

    table
    CLCL (master clustered)
    CLCL_ID (PK)

    CDML (detail)
    CLCL_ID FK (Clustered)
    COPAY_AMT

    The record example is

    clcl_id copay
    1 200
    1 400
    1 500
    2 100
    2 100
    3 1
    3 2

    i am trying to catch when copay is differeny for same clcl_id.

    can the following be more tuned etc.

    select
    tempdb..Claims_Copay_Hosp.CLCL_ID,
    tempdb..Claims_Copay_Hosp.CLCL_TOT_CHG,
    tempdb..Claims_Copay_Hosp.CLCL_TOT_PAYABLE,
    tempdb..Claims_Copay_Hosp.CLCL_CUR_STS,
    tempdb..Claims_Copay_Hosp.CLCL_LOW_SVC_DT,
    tempdb..Claims_Copay_Hosp.CLCL_HIGH_SVC_DT,
    tempdb..Claims_Copay_Hosp.PDPD_ID,
    tempdb..Claims_Copay_Hosp.SBSB_CK,
    CDML1.CDML_SEQ_NO,
    CDML1.CDML_COPAY_AMT,
    CDML1.CDML_CHG_AMT,
    CDML1.CDML_ALLOW,
    CDML1.IDCD_ID,
    CDML1.IDCD_ID_REL,
    ' ' AS SBSB_ID,
    '

    ' AS PDPD_DESC

    INTO tempdb..Claims_Copay

    FROM tempdb..Claims_Copay_Hosp

    INNER JOIN CMC_CDML_CL_LINE CDML1
    ON tempdb..Claims_Copay_Hosp.CLCL_ID = CDML1.CLCL_ID

    WHERE
    EXISTS
    (

    SELECT
    CDML2.CLCL_ID

    FROM CMC_CDML_CL_LINE CDML2

    WHERE
    tempdb..Claims_Copay_Hosp.CLCL_ID = CDML2.CLCL_ID
    AND CDML1.CLCL_ID = CDML2.CLCL_ID
    AND CDML1.CDML_COPAY_AMT > 0
    AND CDML2.CDML_COPAY_AMT > 0
    GROUP BY CDML2.CLCL_ID
    HAVING COUNT (DISTINCT CDML2.CDML_COPAY_AMT) > 1
    )


    "KevinV" <none@none.com> wrote:
    >
    >Columns used in where clauses should generally have indexes on them. If

    the
    >where clause columns donít have indexes, the query optimizer will have no
    >choice but to scan every row in the table. You should add indexes at least
    >for testing and determine if they do any good. That goes for columns used
    >in joins and order by. You can add the indexes, rerun the query and check
    >the execution plan to see if they do any good.
    >
    >In particular, you want an index on the column that you are using in the
    >where clause that limits the result set the most. I would guess this is

    probably
    >the RECD_DT column.
    >
    >Non-clustered indexes versus clustered indexes wonít make much difference
    >in the query performance. Indexes versus no indexes makes a larger difference.
    >
    >Donít use cursors, that is the worst option. Creating any preliminary temp
    >tables is not going to do you any good for the most part.
    >
    >In your list of test times, what happened with the ones that takes 384 seconds
    >and the 158 seconds. The others arenít bad, these are way higher so what
    >was different? This kind of jump in times may indicate where the problem
    >is.
    >
    >"dhaya" <shivdayal@hotmail.com> wrote:
    >>
    >>updates:
    >>
    >>ended up with temp tables, but still looks like its running very slow.


    >>
    >>I ended up with something like this, i got a feedback its very slow still
    >>
    >>
    >>any idea, the columns that have index are below.
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>BEGIN
    >> DROP table tempdb..Claims_Copay
    >>END
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>BEGIN
    >> DROP table tempdb..Claims_Copay_Hosp
    >>END
    >>
    >>go
    >>
    >>SELECT
    >>CMC_CLCL_CLAIM.CLCL_ID,
    >>CMC_SBSB_SUBSC.SBSB_ID,
    >>CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>CMC_CLCL_CLAIM.PDPD_ID,
    >>CMC_PDDS_PROD_DESC.PDDS_DESC
    >>
    >>INTO tempdb..Claims_Copay_Hosp
    >>
    >>FROM CMC_CLCL_CLAIM
    >>INNER JOIN CMC_SBSB_SUBSC
    >>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>INNER JOIN CMC_CLHP_HOSP
    >>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>INNER JOIN CMC_PDPD_PRODUCT
    >>ON CMC_CLCL_CLAIM.PDPD_ID = CMC_PDPD_PRODUCT.PDPD_ID
    >>INNER JOIN CMC_PDDS_PROD_DESC
    >>ON CMC_PDPD_PRODUCT.PDPD_ID = CMC_PDDS_PROD_DESC.PDPD_ID
    >>
    >>WHERE
    >>CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>= '07/07/2003'

    >>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>begin
    >>CREATE CLUSTERED INDEX Index1 ON tempdb..Claims_Copay_Hosp (CLCL_ID)
    >>end
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>begin
    >>select
    >>tempdb..Claims_Copay_Hosp.CLCL_ID,
    >>tempdb..Claims_Copay_Hosp.SBSB_ID,
    >>tempdb..Claims_Copay_Hosp.CLCL_TOT_CHG,
    >>tempdb..Claims_Copay_Hosp.CLCL_TOT_PAYABLE,
    >>tempdb..Claims_Copay_Hosp.CLCL_CUR_STS,
    >>tempdb..Claims_Copay_Hosp.CLCL_LOW_SVC_DT,
    >>tempdb..Claims_Copay_Hosp.CLCL_HIGH_SVC_DT,
    >>tempdb..Claims_Copay_Hosp.PDPD_ID,
    >>tempdb..Claims_Copay_Hosp.PDDS_DESC,
    >>CDML_COPAY_AMT,
    >>CDML_CHG_AMT,
    >>CDML_ALLOW,
    >>CDML_SEQ_NO
    >>into tempdb..Claims_Copay
    >>from tempdb..Claims_Copay_Hosp
    >>INNER JOIN CMC_CDML_CL_LINE
    >>ON tempdb..Claims_Copay_Hosp.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>WHERE
    >>CMC_CDML_CL_LINE .SESE_ID In
    >>('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>'SPRB','SRBR','SRB','SBDR','SKNU')
    >>and Exists
    >>(
    >>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>WHERE
    >>tempdb..Claims_Copay_Hosp.CLCL_ID = A.CLCL_ID
    >>group by A.CLCL_ID
    >>having count(distinct A.CDML_COPAY_AMT) > 1)
    >>
    >>end
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>begin
    >> DROP TABLE tempdb..Claims_Copay_Hosp
    >>end
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>begin
    >> CREATE CLUSTERED INDEX Index2 ON tempdb..Claims_Copay (CLCL_ID)
    >>end
    >>
    >>go
    >>
    >>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>begin
    >> select * from tempdb..Claims_Copay
    >>end
    >>
    >>-------
    >>
    >>INDEXES IN TABLES
    >>
    >>CMCX_CLCL_CLUSTER clustered located on default


    >
    >> MEME_CK, CLCL_ID
    >>CMCX_CLCL_PRIMARY nonclustered, unique located on default


    >
    >> CLCL_ID
    >>CMCX_CLCL_SBSB_CK nonclustered located on default


    >
    >> SBSB_CK
    >>CMCX_CLCL_PRPR_ID nonclustered located on default


    >
    >> PRPR_ID
    >>CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    >
    >> ATXR_SOURCE_ID
    >>CMCX_CLCL_DRAG_DT nonclustered located on default


    >
    >> CLCL_DRAG_DT
    >>CMCX_CLCL_CUR_STS nonclustered located on default


    >
    >> CLCL_CUR_STS
    >>CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    >
    >> CLCL_NEXT_REV_DT
    >>CMCX_CLCL_PAID_DT nonclustered located on default


    >
    >> CLCL_PAID_DT
    >>AIN_PP11_CLCL_CLAIM nonclustered located on default


    >
    >> CLCL_INPUT_DT
    >>CMCX_CDML_CLUSTER clustered located on default


    >
    >> MEME_CK, CDML_FROM_DT
    >>CMCX_CDML_PRIMARY nonclustered, unique located on default


    >
    >> CLCL_ID, CDML_SEQ_NO
    >>CMCX_CDML_PRPR_ID nonclustered located on default


    >
    >> PRPR_ID
    >>CMCX_CLHP_PRIMARY clustered located on default


    >
    >> MEME_CK, CLCL_ID
    >>CMCX_CLHP_SECOND nonclustered, unique located on default


    >
    >> CLCL_ID


    >
    >>
    >>
    >>All the columns used in WHERE clause are without index
    >>RECD_DT
    >>SUB_TYPE
    >>CLHP_FAC_TYPE
    >>CLHP_BILL_CLASS
    >>SESE_ID
    >>
    >>
    >>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>
    >>>I actually tried with each of the WHERE clauses and the indexes, results

    >>are
    >>>below. Looks like since its non-clustered join is slow, moreover all the
    >>>fields used in the where don't have index ? In such cases, what is the

    >best
    >>>solution to deal with these kind of things ? Should i create some temp

    >tables
    >>>or use cursors etc.
    >>>
    >>>the results below.
    >>>JOINS Query time results:
    >>>
    >>>' Direct select from CLCL table :
    >>>Execution Time 20.
    >>>SQL Server cpu time: 2000 ms. SQL Server elapsed time: 17893 ms.
    >>>(0 rows affected)
    >>>
    >>>
    >>>' When Joined with CDML table , type 'H', date params
    >>>Execution Time 24.
    >>>SQL Server cpu time: 2400 ms. SQL Server elapsed time: 20266 ms.
    >>>(0 rows affected)
    >>>
    >>>
    >>>' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >>>included
    >>>Execution Time 22.
    >>>SQL Server cpu time: 2200 ms. SQL Server elapsed time: 82940 ms.
    >>>
    >>>
    >>>' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >>>included, looking for multiple copay's
    >>>Execution Time 29.
    >>>SQL Server cpu time: 2900 ms. SQL Server elapsed time: 30180 ms.
    >>>
    >>>
    >>>' When Joined with the CLHP table and filtering with FAC_TYPE and BILL

    >CLASS
    >>>
    >>>Execution Time 29.
    >>>SQL Server cpu time: 2900 ms. SQL Server elapsed time: 25260 ms.
    >>>
    >>>
    >>>'In another time, the query which does a direct select from table only

    >takes
    >>>more mins
    >>>Execution Time 39.
    >>>SQL Server cpu time: 3900 ms. SQL Server elapsed time: 29793 ms.
    >>>
    >>>
    >>>'the query without the date params
    >>>
    >>>Execution Time 384.
    >>>SQL Server cpu time: 38400 ms. SQL Server elapsed time: 748623 ms.
    >>>
    >>>' the query without date params and CLHP and COPAY
    >>>Execution Time 158.
    >>>SQL Server cpu time: 15800 ms. SQL Server elapsed time: 316946 ms.
    >>>
    >>>---------------------------------------------------------------------------INDEXES
    >>>IN TABLES
    >>>
    >>>CMCX_CLCL_CLUSTER clustered located on default


    >
    >>
    >>> MEME_CK, CLCL_ID
    >>>CMCX_CLCL_PRIMARY nonclustered, unique located on default

    >
    >>
    >>> CLCL_ID
    >>>CMCX_CLCL_SBSB_CK nonclustered located on default


    >
    >>
    >>> SBSB_CK
    >>>CMCX_CLCL_PRPR_ID nonclustered located on default


    >
    >>
    >>> PRPR_ID
    >>>CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    >
    >>
    >>> ATXR_SOURCE_ID
    >>>CMCX_CLCL_DRAG_DT nonclustered located on default


    >
    >>
    >>> CLCL_DRAG_DT
    >>>CMCX_CLCL_CUR_STS nonclustered located on default


    >
    >>
    >>> CLCL_CUR_STS
    >>>CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    >
    >>
    >>> CLCL_NEXT_REV_DT
    >>>CMCX_CLCL_PAID_DT nonclustered located on default


    >
    >>
    >>> CLCL_PAID_DT
    >>>AIN_PP11_CLCL_CLAIM nonclustered located on default


    >
    >>
    >>> CLCL_INPUT_DT
    >>>CMCX_CDML_CLUSTER clustered located on default


    >
    >>
    >>> MEME_CK, CDML_FROM_DT
    >>>CMCX_CDML_PRIMARY nonclustered, unique located on default


    >
    >>
    >>> CLCL_ID, CDML_SEQ_NO
    >>>CMCX_CDML_PRPR_ID nonclustered located on default


    >
    >>
    >>> PRPR_ID
    >>>CMCX_CLHP_PRIMARY clustered located on default


    >
    >>
    >>> MEME_CK, CLCL_ID
    >>>CMCX_CLHP_SECOND nonclustered, unique located on default


    >
    >>
    >>> CLCL_ID


    >
    >>
    >>>


    >
    >>
    >>> Columns used without index
    >>>RECD_DT
    >>>SUB_TYPE
    >>>CLHP_FAC_TYPE
    >>>CLHP_BILL_CLASS
    >>>SESE_ID
    >>>
    >>>
    >>>
    >>>
    >>>"KevinV" <none@none.com> wrote:
    >>>>
    >>>>Here's some various comments below.
    >>>>
    >>>>The 'Show Execution Plan' option should show you a graphical query plan.
    >>>>Each step in the plan has a cost associated with it. I don't know what

    >>version
    >>>>of sql you have and/or if there is a text only display of the plan. I

    >don't
    >>>>see the costs in your query plan, although I don't know if there is enough
    >>>>steps anyway. The graphical plan may just show two steps which doesn't

    >>help
    >>>>much.
    >>>>
    >>>>I would use the INs instead of ORs. I'm not sure if it makes a difference
    >>>>performance wise but it is much more readable.
    >>>>
    >>>>Make sure your order by column is an index. It may still do a table scan
    >>>>anyway.
    >>>>
    >>>>It looks like all the other columns have indexes.
    >>>>
    >>>>Another thing I resort to at times is simplifying the query to determine
    >>>>what causes it to be slow. For example, you may want to remove the subselect
    >>>>and run the query, then add it back and run it. Same with the joins or

    >>the
    >>>>column checks on the where clause. You may come across one particular

    >thing
    >>>>that makes the query slow.
    >>>>
    >>>>That's about all I can think of now. Hope some of it helps.
    >>>>
    >>>>
    >>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>
    >>>>>By looking at the plan, i don't know how to find how to find which one
    >>>is
    >>>>>slow here. Looks like the CMC_CLHP_HOSP
    >>>>>
    >>>>>"dhaya" <shivdayal@yahoo.com> wrote:
    >>>>>>
    >>>>>>I tried replacing IN with OR
    >>>>>>And then looked like the tables are too big so join is slow.
    >>>>>>I am thinking only creating temp tables and indices may work, don't

    >know
    >>>>>>if anything else would work
    >>>>>>
    >>>>>>have the query and plan below
    >>>>>>
    >>>>>>SELECT CLCL.CLCL_ID,
    >>>>>> SBSB.SBSB_ID,
    >>>>>> CLCL.CLCL_TOT_CHG,
    >>>>>> CLCL.CLCL_TOT_PAYABLE,
    >>>>>> CLCL.CLCL_CUR_STS,
    >>>>>> CLCL.CLCL_LOW_SVC_DT,
    >>>>>> CLCL.CLCL_HIGH_SVC_DT,
    >>>>>> CLCL.PDPD_ID,
    >>>>>> CDML1.CDML_COPAY_AMT,
    >>>>>> CDML1.CDML_CHG_AMT,
    >>>>>> CDML1.CDML_ALLOW,
    >>>>>> CDML1.CDML_SEQ_NO
    >>>>>>
    >>>>>>FROM CMC_CLCL_CLAIM CLCL
    >>>>>>
    >>>>>>INNER JOIN CMC_SBSB_SUBSC SBSB
    >>>>>>ON CLCL.SBSB_CK = SBSB.SBSB_CK
    >>>>>>
    >>>>>>INNER JOIN CMC_CDML_CL_LINE CDML1
    >>>>>>ON CLCL.CLCL_ID = CDML1.CLCL_ID
    >>>>>>
    >>>>>>INNER JOIN CMC_CLHP_HOSP CLHP
    >>>>>>ON CLCL.CLCL_ID = CLHP.CLCL_ID
    >>>>>>
    >>>>>>WHERE
    >>>>>>
    >>>>>>(
    >>>>>>CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS'
    >>>>>OR
    >>>>>>CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS'
    >>>OR
    >>>>>>CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    >>>>>> OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID=

    >'ICUS'
    >>>>>>OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID=

    >'NUR'
    >>>>>>OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID=

    'PRBS'
    >>>>>> OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID=

    >>'RB'
    >>>>>>OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID= 'RBD'
    >>>>>OR
    >>>>>>CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'
    >>>>>
    >>>>>> OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID=

    >>'SBDR'
    >>>>>>OR CDML1.SESE_ID= 'SKNU'
    >>>>>>)
    >>>>>>
    >>>>>>AND CLCL.CLCL_CL_SUB_TYPE='H'
    >>>>>>
    >>>>>>AND CLCL.CLCL_ID IN
    >>>>>>(
    >>>>>>SELECT SUB_CDML1.CLCL_ID
    >>>>>>FROM CMC_CDML_CL_LINE SUB_CDML1
    >>>>>>INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    >>>>>>ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    >>>>>>WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    >>>>>>AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    >>>>>>)
    >>>>>>
    >>>>>>AND CLHP.CLHP_FAC_TYPE = '1'
    >>>>>>
    >>>>>>AND
    >>>>>>(
    >>>>>>CLHP.CLHP_BILL_CLASS ='E' OR
    >>>>>>CLHP.CLHP_BILL_CLASS ='U' OR
    >>>>>>CLHP.CLHP_BILL_CLASS ='S' OR
    >>>>>>CLHP.CLHP_BILL_CLASS ='3'
    >>>>>>)
    >>>>>>
    >>>>>>ORDER BY CLCL.PDPD_ID
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>----------
    >>>>>>
    >>>>>>QUERY PLAN FOR STATEMENT 1 (at line 1).
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>> STEP 1
    >>>>>> The type of query is INSERT.
    >>>>>> The update mode is direct.
    >>>>>> Worktable1 created for ORDER BY.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CLCL_CLAIM
    >>>>>> CLCL
    >>>>>> Nested iteration.
    >>>>>> Table Scan.
    >>>>>> Forward scan.
    >>>>>> Positioning at start of table.
    >>>>>> Using I/O Size 16 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_SBSB_SUBSC
    >>>>>> SBSB
    >>>>>> Nested iteration.
    >>>>>> Index : CMCX_SBSB_PRIMARY
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> SBSB_CK ASC
    >>>>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CDML_CL_LINE
    >>>>>> CDML1
    >>>>>> Nested iteration.
    >>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> CLCL_ID ASC
    >>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CLHP_HOSP
    >>>>>> CLHP
    >>>>>> Nested iteration.
    >>>>>> Index : CMCX_CLHP_SECOND
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> CLCL_ID ASC
    >>>>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CDML_CL_LINE
    >>>>>> SUB_CDML1
    >>>>>> EXISTS TABLE : nested iteration.
    >>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> CLCL_ID ASC
    >>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> CMC_CDML_CL_LINE
    >>>>>> SUB_CDML2
    >>>>>> EXISTS TABLE : nested iteration.
    >>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>> Forward scan.
    >>>>>> Positioning by key.
    >>>>>> Keys are:
    >>>>>> CLCL_ID ASC
    >>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>> With LRU Buffer Replacement Strategy for index leaf pages.


    >>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>> TO TABLE
    >>>>>> Worktable1.
    >>>>>>
    >>>>>>
    >>>>>> STEP 2
    >>>>>> The type of query is SELECT.
    >>>>>> This step involves sorting.
    >>>>>>
    >>>>>>
    >>>>>> FROM TABLE
    >>>>>> Worktable1.
    >>>>>> Using GETSORTED
    >>>>>> Table Scan.
    >>>>>> Forward scan.
    >>>>>> Positioning at start of table.
    >>>>>> Using I/O Size 16 Kbytes for data pages.
    >>>>>> With MRU Buffer Replacement Strategy for data pages.
    >>>>>>
    >>>>>>
    >>>>>>Parse and Compile Time 0.
    >>>>>>SQL Server cpu time: 0 ms.
    >>>>>>Server Message: Number 1562, Severity 10
    >>>>>>Server 'aims_dev2', Line 1:
    >>>>>>The sort for Worktable1 is done in Serial
    >>>>>>Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202

    apf=0
    >>>>>>total=150202), physical reads: (regular=5584 apf=13331 total=18915),

    >>apf
    >>>>>>IOs used=13228
    >>>>>>Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431
    >>>>apf=11
    >>>>>>total=233442), physical reads: (regular=4573 apf=412 total=4985), apf
    >>>IOs
    >>>>>>used=209
    >>>>>>Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    >>>>>>apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    >>>>>>apf IOs used=88751
    >>>>>>Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084

    >apf=0
    >>>>>>total=17084), physical reads: (regular=2117 apf=18 total=2135), apf

    >IOs
    >>>>>used=18
    >>>>>>
    >>>>>>Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170

    apf=0
    >>>>>>total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    >>>
    >>>>>>Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038

    >>apf=0
    >>>>>>total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15
    >>>>>
    >>>>>>Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    >>>>>>physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    >>>>>>Total writes for this command: 6
    >>>>>>
    >>>>>>Execution Time 144.
    >>>>>>SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    >>>>>>(1 row affected)
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>"KevinV" <none@none.com> wrote:
    >>>>>>>
    >>>>>>>Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >>>>>>>
    >>>>>>>There is a lot to tuning queries. The query analyzer execution plan

    >>is
    >>>>>usually
    >>>>>>>a good place to start. Look for the step in the execution plan that

    >>has
    >>>>>>the
    >>>>>>>highest cost and start there to look for optimizations. Generally

    adding
    >>>>>>>indexes for certain columns, like columns referenced in joins and

    where
    >>>>>>clauses
    >>>>>>>is what will improve the query the most. If you see steps that are

    >doing
    >>>>>>>table scans then that likely means you need an index. It is also possible
    >>>>>>>that your query will just be slow depending on the number of rows

    of
    >>>data,
    >>>>>>>physical placement of data, log and index database files and the configuration
    >>>>>>>of the server.
    >>>>>>>
    >>>>>>>
    >>>>>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>>>>
    >>>>>>>>any way to this query , its very slow.
    >>>>>>>>
    >>>>>>>>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >>>>>>>> CMC_SBSB_SUBSC.SBSB_ID,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>>>>>>> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>>>>>>> CMC_CLCL_CLAIM.PDPD_ID,
    >>>>>>>> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >>>>>>>> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >>>>>>>> CMC_CDML_CL_LINE.CDML_ALLOW,
    >>>>>>>> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>>>>>>>into adhocdb..JP_COPAY_ERRORS
    >>>>>>>>FROM CMC_CLCL_CLAIM
    >>>>>>>>
    >>>>>>>>INNER JOIN CMC_SBSB_SUBSC
    >>>>>>>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>>>>>>
    >>>>>>>>INNER JOIN CMC_CDML_CL_LINE
    >>>>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>>>>>>
    >>>>>>>>INNER JOIN CMC_CLHP_HOSP
    >>>>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>>>>>>
    >>>>>>>>WHERE
    >>>>>>>>
    >>>>>>>>CMC_CDML_CL_LINE.SESE_ID In
    >>>>>>>> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>>>>>>> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>>>>>>> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>>>>>>> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>>>>>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>>>>>>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>>>>>>= '07/07/2003'
    >>>>>>>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>>>>>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>>>>>>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>>>>>>>(
    >>>>>>>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>>>>>>INNER JOIN CMC_CDML_CL_LINE B
    >>>>>>>>ON A.CLCL_ID = B.CLCL_ID
    >>>>>>>> WHERE
    >>>>>>>>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>>>>>>>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>>>>>>>)
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>The last inner query IN is to get from a detail table rows which

    have
    >>>>>different
    >>>>>>>>copay amount
    >>>>>>>>
    >>>>>>>>example
    >>>>>>>>111 will be selected in below case
    >>>>>>>>
    >>>>>>>>claim_id sequence_no copay
    >>>>>>>>111 1 10.0
    >>>>>>>>111 2 20.0
    >>>>>>>>222 1 100.0
    >>>>>>>>222 2 100.0
    >>>>>>>>
    >>>>>>>>Thank you!
    >>>>>>>>dhaya
    >>>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  12. #12
    KevinV Guest

    Re: query tuning


    It looks alright. As long as your where and join columns have indexes, there
    isn't much else to do.

    "dhaya" <shivdayal@hotmail.com> wrote:
    >
    >Thanks a lot, that helped me.
    >
    >finally i am getting to run by running records worth of one day etc.
    >
    >the only piece which is not tuned for me is this below
    >
    >table
    >CLCL (master clustered)
    >CLCL_ID (PK)
    >
    >CDML (detail)
    >CLCL_ID FK (Clustered)
    >COPAY_AMT
    >
    >The record example is
    >
    >clcl_id copay
    >1 200
    >1 400
    >1 500
    >2 100
    >2 100
    >3 1
    >3 2
    >
    >i am trying to catch when copay is differeny for same clcl_id.
    >
    >can the following be more tuned etc.
    >
    >select
    >tempdb..Claims_Copay_Hosp.CLCL_ID,
    >tempdb..Claims_Copay_Hosp.CLCL_TOT_CHG,
    >tempdb..Claims_Copay_Hosp.CLCL_TOT_PAYABLE,
    >tempdb..Claims_Copay_Hosp.CLCL_CUR_STS,
    >tempdb..Claims_Copay_Hosp.CLCL_LOW_SVC_DT,
    >tempdb..Claims_Copay_Hosp.CLCL_HIGH_SVC_DT,
    >tempdb..Claims_Copay_Hosp.PDPD_ID,
    >tempdb..Claims_Copay_Hosp.SBSB_CK,
    >CDML1.CDML_SEQ_NO,
    >CDML1.CDML_COPAY_AMT,
    >CDML1.CDML_CHG_AMT,
    >CDML1.CDML_ALLOW,
    >CDML1.IDCD_ID,
    >CDML1.IDCD_ID_REL,
    >' ' AS SBSB_ID,
    >'


    >


    > ' AS PDPD_DESC
    >
    >INTO tempdb..Claims_Copay
    >
    >FROM tempdb..Claims_Copay_Hosp
    >
    >INNER JOIN CMC_CDML_CL_LINE CDML1
    >ON tempdb..Claims_Copay_Hosp.CLCL_ID = CDML1.CLCL_ID
    >
    >WHERE
    >EXISTS
    >(
    >
    >SELECT
    >CDML2.CLCL_ID
    >
    >FROM CMC_CDML_CL_LINE CDML2
    >
    >WHERE
    >tempdb..Claims_Copay_Hosp.CLCL_ID = CDML2.CLCL_ID
    >AND CDML1.CLCL_ID = CDML2.CLCL_ID
    >AND CDML1.CDML_COPAY_AMT > 0
    >AND CDML2.CDML_COPAY_AMT > 0
    >GROUP BY CDML2.CLCL_ID
    >HAVING COUNT (DISTINCT CDML2.CDML_COPAY_AMT) > 1
    >)
    >
    >
    >"KevinV" <none@none.com> wrote:
    >>
    >>Columns used in where clauses should generally have indexes on them. If

    >the
    >>where clause columns donít have indexes, the query optimizer will have

    no
    >>choice but to scan every row in the table. You should add indexes at least
    >>for testing and determine if they do any good. That goes for columns used
    >>in joins and order by. You can add the indexes, rerun the query and check
    >>the execution plan to see if they do any good.
    >>
    >>In particular, you want an index on the column that you are using in the
    >>where clause that limits the result set the most. I would guess this is

    >probably
    >>the RECD_DT column.
    >>
    >>Non-clustered indexes versus clustered indexes wonít make much difference
    >>in the query performance. Indexes versus no indexes makes a larger difference.
    >>
    >>Donít use cursors, that is the worst option. Creating any preliminary temp
    >>tables is not going to do you any good for the most part.
    >>
    >>In your list of test times, what happened with the ones that takes 384

    seconds
    >>and the 158 seconds. The others arenít bad, these are way higher so what
    >>was different? This kind of jump in times may indicate where the problem
    >>is.
    >>
    >>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>
    >>>updates:
    >>>
    >>>ended up with temp tables, but still looks like its running very slow.

    >
    >>>
    >>>I ended up with something like this, i got a feedback its very slow still
    >>>
    >>>
    >>>any idea, the columns that have index are below.
    >>>
    >>>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>>BEGIN
    >>> DROP table tempdb..Claims_Copay
    >>>END
    >>>
    >>>go
    >>>
    >>>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>>BEGIN
    >>> DROP table tempdb..Claims_Copay_Hosp
    >>>END
    >>>
    >>>go
    >>>
    >>>SELECT
    >>>CMC_CLCL_CLAIM.CLCL_ID,
    >>>CMC_SBSB_SUBSC.SBSB_ID,
    >>>CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>>CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>>CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>>CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>>CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>>CMC_CLCL_CLAIM.PDPD_ID,
    >>>CMC_PDDS_PROD_DESC.PDDS_DESC
    >>>
    >>>INTO tempdb..Claims_Copay_Hosp
    >>>
    >>>FROM CMC_CLCL_CLAIM
    >>>INNER JOIN CMC_SBSB_SUBSC
    >>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>INNER JOIN CMC_CLHP_HOSP
    >>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>INNER JOIN CMC_PDPD_PRODUCT
    >>>ON CMC_CLCL_CLAIM.PDPD_ID = CMC_PDPD_PRODUCT.PDPD_ID
    >>>INNER JOIN CMC_PDDS_PROD_DESC
    >>>ON CMC_PDPD_PRODUCT.PDPD_ID = CMC_PDDS_PROD_DESC.PDPD_ID
    >>>
    >>>WHERE
    >>>CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>= '07/07/2003'
    >>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>
    >>>go
    >>>
    >>>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>>begin
    >>>CREATE CLUSTERED INDEX Index1 ON tempdb..Claims_Copay_Hosp (CLCL_ID)
    >>>end
    >>>
    >>>go
    >>>
    >>>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>>begin
    >>>select
    >>>tempdb..Claims_Copay_Hosp.CLCL_ID,
    >>>tempdb..Claims_Copay_Hosp.SBSB_ID,
    >>>tempdb..Claims_Copay_Hosp.CLCL_TOT_CHG,
    >>>tempdb..Claims_Copay_Hosp.CLCL_TOT_PAYABLE,
    >>>tempdb..Claims_Copay_Hosp.CLCL_CUR_STS,
    >>>tempdb..Claims_Copay_Hosp.CLCL_LOW_SVC_DT,
    >>>tempdb..Claims_Copay_Hosp.CLCL_HIGH_SVC_DT,
    >>>tempdb..Claims_Copay_Hosp.PDPD_ID,
    >>>tempdb..Claims_Copay_Hosp.PDDS_DESC,
    >>>CDML_COPAY_AMT,
    >>>CDML_CHG_AMT,
    >>>CDML_ALLOW,
    >>>CDML_SEQ_NO
    >>>into tempdb..Claims_Copay
    >>>from tempdb..Claims_Copay_Hosp
    >>>INNER JOIN CMC_CDML_CL_LINE
    >>>ON tempdb..Claims_Copay_Hosp.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>WHERE
    >>>CMC_CDML_CL_LINE .SESE_ID In
    >>>('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>>'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>>'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>>'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>and Exists
    >>>(
    >>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>WHERE
    >>>tempdb..Claims_Copay_Hosp.CLCL_ID = A.CLCL_ID
    >>>group by A.CLCL_ID
    >>>having count(distinct A.CDML_COPAY_AMT) > 1)
    >>>
    >>>end
    >>>
    >>>go
    >>>
    >>>IF OBJECT_ID ('tempdb..Claims_Copay_Hosp') IS NOT NULL
    >>>begin
    >>> DROP TABLE tempdb..Claims_Copay_Hosp
    >>>end
    >>>
    >>>go
    >>>
    >>>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>>begin
    >>> CREATE CLUSTERED INDEX Index2 ON tempdb..Claims_Copay (CLCL_ID)
    >>>end
    >>>
    >>>go
    >>>
    >>>IF OBJECT_ID ('tempdb..Claims_Copay') IS NOT NULL
    >>>begin
    >>> select * from tempdb..Claims_Copay
    >>>end
    >>>
    >>>-------
    >>>
    >>>INDEXES IN TABLES
    >>>
    >>>CMCX_CLCL_CLUSTER clustered located on default


    >
    >>
    >>> MEME_CK, CLCL_ID
    >>>CMCX_CLCL_PRIMARY nonclustered, unique located on default

    >
    >>
    >>> CLCL_ID
    >>>CMCX_CLCL_SBSB_CK nonclustered located on default


    >
    >>
    >>> SBSB_CK
    >>>CMCX_CLCL_PRPR_ID nonclustered located on default


    >
    >>
    >>> PRPR_ID
    >>>CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    >
    >>
    >>> ATXR_SOURCE_ID
    >>>CMCX_CLCL_DRAG_DT nonclustered located on default


    >
    >>
    >>> CLCL_DRAG_DT
    >>>CMCX_CLCL_CUR_STS nonclustered located on default


    >
    >>
    >>> CLCL_CUR_STS
    >>>CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    >
    >>
    >>> CLCL_NEXT_REV_DT
    >>>CMCX_CLCL_PAID_DT nonclustered located on default


    >
    >>
    >>> CLCL_PAID_DT
    >>>AIN_PP11_CLCL_CLAIM nonclustered located on default


    >
    >>
    >>> CLCL_INPUT_DT
    >>>CMCX_CDML_CLUSTER clustered located on default


    >
    >>
    >>> MEME_CK, CDML_FROM_DT
    >>>CMCX_CDML_PRIMARY nonclustered, unique located on default


    >
    >>
    >>> CLCL_ID, CDML_SEQ_NO
    >>>CMCX_CDML_PRPR_ID nonclustered located on default


    >
    >>
    >>> PRPR_ID
    >>>CMCX_CLHP_PRIMARY clustered located on default


    >
    >>
    >>> MEME_CK, CLCL_ID
    >>>CMCX_CLHP_SECOND nonclustered, unique located on default


    >
    >>
    >>> CLCL_ID


    >
    >>
    >>>
    >>>
    >>>All the columns used in WHERE clause are without index
    >>>RECD_DT
    >>>SUB_TYPE
    >>>CLHP_FAC_TYPE
    >>>CLHP_BILL_CLASS
    >>>SESE_ID
    >>>
    >>>
    >>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>
    >>>>I actually tried with each of the WHERE clauses and the indexes, results
    >>>are
    >>>>below. Looks like since its non-clustered join is slow, moreover all

    the
    >>>>fields used in the where don't have index ? In such cases, what is the

    >>best
    >>>>solution to deal with these kind of things ? Should i create some temp

    >>tables
    >>>>or use cursors etc.
    >>>>
    >>>>the results below.
    >>>>JOINS Query time results:
    >>>>
    >>>>' Direct select from CLCL table :
    >>>>Execution Time 20.
    >>>>SQL Server cpu time: 2000 ms. SQL Server elapsed time: 17893 ms.
    >>>>(0 rows affected)
    >>>>
    >>>>
    >>>>' When Joined with CDML table , type 'H', date params
    >>>>Execution Time 24.
    >>>>SQL Server cpu time: 2400 ms. SQL Server elapsed time: 20266 ms.
    >>>>(0 rows affected)
    >>>>
    >>>>
    >>>>' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >>>>included
    >>>>Execution Time 22.
    >>>>SQL Server cpu time: 2200 ms. SQL Server elapsed time: 82940 ms.
    >>>>
    >>>>
    >>>>' When Joined with CDML table , type 'H', date params, and SESE_ID criteria
    >>>>included, looking for multiple copay's
    >>>>Execution Time 29.
    >>>>SQL Server cpu time: 2900 ms. SQL Server elapsed time: 30180 ms.
    >>>>
    >>>>
    >>>>' When Joined with the CLHP table and filtering with FAC_TYPE and BILL

    >>CLASS
    >>>>
    >>>>Execution Time 29.
    >>>>SQL Server cpu time: 2900 ms. SQL Server elapsed time: 25260 ms.
    >>>>
    >>>>
    >>>>'In another time, the query which does a direct select from table only

    >>takes
    >>>>more mins
    >>>>Execution Time 39.
    >>>>SQL Server cpu time: 3900 ms. SQL Server elapsed time: 29793 ms.
    >>>>
    >>>>
    >>>>'the query without the date params
    >>>>
    >>>>Execution Time 384.
    >>>>SQL Server cpu time: 38400 ms. SQL Server elapsed time: 748623 ms.
    >>>>
    >>>>' the query without date params and CLHP and COPAY
    >>>>Execution Time 158.
    >>>>SQL Server cpu time: 15800 ms. SQL Server elapsed time: 316946 ms.
    >>>>
    >>>>---------------------------------------------------------------------------INDEXES
    >>>>IN TABLES
    >>>>
    >>>>CMCX_CLCL_CLUSTER clustered located on default


    >
    >>
    >>>
    >>>> MEME_CK, CLCL_ID
    >>>>CMCX_CLCL_PRIMARY nonclustered, unique located on default

    >>
    >>>
    >>>> CLCL_ID
    >>>>CMCX_CLCL_SBSB_CK nonclustered located on default


    >
    >>
    >>>
    >>>> SBSB_CK
    >>>>CMCX_CLCL_PRPR_ID nonclustered located on default


    >
    >>
    >>>
    >>>> PRPR_ID
    >>>>CMCX_CLCL_ATXR_SOURCE_ID nonclustered located on default


    >
    >>
    >>>
    >>>> ATXR_SOURCE_ID
    >>>>CMCX_CLCL_DRAG_DT nonclustered located on default


    >
    >>
    >>>
    >>>> CLCL_DRAG_DT
    >>>>CMCX_CLCL_CUR_STS nonclustered located on default


    >
    >>
    >>>
    >>>> CLCL_CUR_STS
    >>>>CMCX_CLCL_NEXT_REV_DT nonclustered located on default


    >
    >>
    >>>
    >>>> CLCL_NEXT_REV_DT
    >>>>CMCX_CLCL_PAID_DT nonclustered located on default


    >
    >>
    >>>
    >>>> CLCL_PAID_DT
    >>>>AIN_PP11_CLCL_CLAIM nonclustered located on default


    >
    >>
    >>>
    >>>> CLCL_INPUT_DT
    >>>>CMCX_CDML_CLUSTER clustered located on default


    >
    >>
    >>>
    >>>> MEME_CK, CDML_FROM_DT
    >>>>CMCX_CDML_PRIMARY nonclustered, unique located on default


    >
    >>
    >>>
    >>>> CLCL_ID, CDML_SEQ_NO
    >>>>CMCX_CDML_PRPR_ID nonclustered located on default


    >
    >>
    >>>
    >>>> PRPR_ID
    >>>>CMCX_CLHP_PRIMARY clustered located on default


    >
    >>
    >>>
    >>>> MEME_CK, CLCL_ID
    >>>>CMCX_CLHP_SECOND nonclustered, unique located on default


    >
    >>
    >>>
    >>>> CLCL_ID


    >
    >>
    >>>
    >>>>


    >
    >>
    >>>
    >>>> Columns used without index
    >>>>RECD_DT
    >>>>SUB_TYPE
    >>>>CLHP_FAC_TYPE
    >>>>CLHP_BILL_CLASS
    >>>>SESE_ID
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>"KevinV" <none@none.com> wrote:
    >>>>>
    >>>>>Here's some various comments below.
    >>>>>
    >>>>>The 'Show Execution Plan' option should show you a graphical query plan.
    >>>>>Each step in the plan has a cost associated with it. I don't know what
    >>>version
    >>>>>of sql you have and/or if there is a text only display of the plan.

    I
    >>don't
    >>>>>see the costs in your query plan, although I don't know if there is

    enough
    >>>>>steps anyway. The graphical plan may just show two steps which doesn't
    >>>help
    >>>>>much.
    >>>>>
    >>>>>I would use the INs instead of ORs. I'm not sure if it makes a difference
    >>>>>performance wise but it is much more readable.
    >>>>>
    >>>>>Make sure your order by column is an index. It may still do a table

    scan
    >>>>>anyway.
    >>>>>
    >>>>>It looks like all the other columns have indexes.
    >>>>>
    >>>>>Another thing I resort to at times is simplifying the query to determine
    >>>>>what causes it to be slow. For example, you may want to remove the subselect
    >>>>>and run the query, then add it back and run it. Same with the joins

    or
    >>>the
    >>>>>column checks on the where clause. You may come across one particular

    >>thing
    >>>>>that makes the query slow.
    >>>>>
    >>>>>That's about all I can think of now. Hope some of it helps.
    >>>>>
    >>>>>
    >>>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>>
    >>>>>>By looking at the plan, i don't know how to find how to find which

    one
    >>>>is
    >>>>>>slow here. Looks like the CMC_CLHP_HOSP
    >>>>>>
    >>>>>>"dhaya" <shivdayal@yahoo.com> wrote:
    >>>>>>>
    >>>>>>>I tried replacing IN with OR
    >>>>>>>And then looked like the tables are too big so join is slow.
    >>>>>>>I am thinking only creating temp tables and indices may work, don't

    >>know
    >>>>>>>if anything else would work
    >>>>>>>
    >>>>>>>have the query and plan below
    >>>>>>>
    >>>>>>>SELECT CLCL.CLCL_ID,
    >>>>>>> SBSB.SBSB_ID,
    >>>>>>> CLCL.CLCL_TOT_CHG,
    >>>>>>> CLCL.CLCL_TOT_PAYABLE,
    >>>>>>> CLCL.CLCL_CUR_STS,
    >>>>>>> CLCL.CLCL_LOW_SVC_DT,
    >>>>>>> CLCL.CLCL_HIGH_SVC_DT,
    >>>>>>> CLCL.PDPD_ID,
    >>>>>>> CDML1.CDML_COPAY_AMT,
    >>>>>>> CDML1.CDML_CHG_AMT,
    >>>>>>> CDML1.CDML_ALLOW,
    >>>>>>> CDML1.CDML_SEQ_NO
    >>>>>>>
    >>>>>>>FROM CMC_CLCL_CLAIM CLCL
    >>>>>>>
    >>>>>>>INNER JOIN CMC_SBSB_SUBSC SBSB
    >>>>>>>ON CLCL.SBSB_CK = SBSB.SBSB_CK
    >>>>>>>
    >>>>>>>INNER JOIN CMC_CDML_CL_LINE CDML1
    >>>>>>>ON CLCL.CLCL_ID = CDML1.CLCL_ID
    >>>>>>>
    >>>>>>>INNER JOIN CMC_CLHP_HOSP CLHP
    >>>>>>>ON CLCL.CLCL_ID = CLHP.CLCL_ID
    >>>>>>>
    >>>>>>>WHERE
    >>>>>>>
    >>>>>>>(
    >>>>>>>CDML1.SESE_ID = 'ARB' OR CDML1.SESE_ID= 'ARBP' OR CDML1.SESE_ID= 'ARBS'
    >>>>>>OR
    >>>>>>>CDML1.SESE_ID= 'CCU' OR CDML1.SESE_ID= 'HOSP' OR CDML1.SESE_ID= 'HOS'
    >>>>OR
    >>>>>>>CDML1.SESE_ID= 'HOLD' OR CDML1.SESE_ID= 'HOLP' OR CDML1.SESE_ID= 'ISOL'
    >>>>>>> OR CDML1.SESE_ID= 'ICU' OR CDML1.SESE_ID= 'ICUP' OR CDML1.SESE_ID=

    >>'ICUS'
    >>>>>>>OR CDML1.SESE_ID= 'MATN' OR CDML1.SESE_ID= 'MPRN' OR CDML1.SESE_ID=

    >>'NUR'
    >>>>>>>OR CDML1.SESE_ID= 'PRB' OR CDML1.SESE_ID= 'PRBP' OR CDML1.SESE_ID=

    >'PRBS'
    >>>>>>> OR CDML1.SESE_ID= 'PRBD' OR CDML1.SESE_ID= 'PRBR' OR CDML1.SESE_ID=
    >>>'RB'
    >>>>>>>OR CDML1.SESE_ID= 'RBP' OR CDML1.SESE_ID= 'RBS' OR CDML1.SESE_ID=

    'RBD'
    >>>>>>OR
    >>>>>>>CDML1.SESE_ID= 'RBDR' OR CDML1.SESE_ID= 'SARB' OR CDML1.SESE_ID= 'SPRB'
    >>>>>>
    >>>>>>> OR CDML1.SESE_ID= 'SRBR' OR CDML1.SESE_ID= 'SRB' OR CDML1.SESE_ID=
    >>>'SBDR'
    >>>>>>>OR CDML1.SESE_ID= 'SKNU'
    >>>>>>>)
    >>>>>>>
    >>>>>>>AND CLCL.CLCL_CL_SUB_TYPE='H'
    >>>>>>>
    >>>>>>>AND CLCL.CLCL_ID IN
    >>>>>>>(
    >>>>>>>SELECT SUB_CDML1.CLCL_ID
    >>>>>>>FROM CMC_CDML_CL_LINE SUB_CDML1
    >>>>>>>INNER JOIN CMC_CDML_CL_LINE SUB_CDML2
    >>>>>>>ON SUB_CDML1.CLCL_ID = SUB_CDML2.CLCL_ID
    >>>>>>>WHERE SUB_CDML1.CDML_SEQ_NO <> SUB_CDML2.CDML_SEQ_NO
    >>>>>>>AND SUB_CDML1.CDML_COPAY_AMT <> SUB_CDML2.CDML_COPAY_AMT
    >>>>>>>)
    >>>>>>>
    >>>>>>>AND CLHP.CLHP_FAC_TYPE = '1'
    >>>>>>>
    >>>>>>>AND
    >>>>>>>(
    >>>>>>>CLHP.CLHP_BILL_CLASS ='E' OR
    >>>>>>>CLHP.CLHP_BILL_CLASS ='U' OR
    >>>>>>>CLHP.CLHP_BILL_CLASS ='S' OR
    >>>>>>>CLHP.CLHP_BILL_CLASS ='3'
    >>>>>>>)
    >>>>>>>
    >>>>>>>ORDER BY CLCL.PDPD_ID
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>----------
    >>>>>>>
    >>>>>>>QUERY PLAN FOR STATEMENT 1 (at line 1).
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>> STEP 1
    >>>>>>> The type of query is INSERT.
    >>>>>>> The update mode is direct.
    >>>>>>> Worktable1 created for ORDER BY.
    >>>>>>>
    >>>>>>>
    >>>>>>> FROM TABLE
    >>>>>>> CMC_CLCL_CLAIM
    >>>>>>> CLCL
    >>>>>>> Nested iteration.
    >>>>>>> Table Scan.
    >>>>>>> Forward scan.
    >>>>>>> Positioning at start of table.
    >>>>>>> Using I/O Size 16 Kbytes for data pages.
    >>>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>>
    >>>>>>>
    >>>>>>> FROM TABLE
    >>>>>>> CMC_SBSB_SUBSC
    >>>>>>> SBSB
    >>>>>>> Nested iteration.
    >>>>>>> Index : CMCX_SBSB_PRIMARY
    >>>>>>> Forward scan.
    >>>>>>> Positioning by key.
    >>>>>>> Keys are:
    >>>>>>> SBSB_CK ASC
    >>>>>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>>>>> With LRU Buffer Replacement Strategy for index leaf pages.

    >
    >>>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>>
    >>>>>>>
    >>>>>>> FROM TABLE
    >>>>>>> CMC_CDML_CL_LINE
    >>>>>>> CDML1
    >>>>>>> Nested iteration.
    >>>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>>> Forward scan.
    >>>>>>> Positioning by key.
    >>>>>>> Keys are:
    >>>>>>> CLCL_ID ASC
    >>>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>>> With LRU Buffer Replacement Strategy for index leaf pages.

    >
    >>>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>>
    >>>>>>>
    >>>>>>> FROM TABLE
    >>>>>>> CMC_CLHP_HOSP
    >>>>>>> CLHP
    >>>>>>> Nested iteration.
    >>>>>>> Index : CMCX_CLHP_SECOND
    >>>>>>> Forward scan.
    >>>>>>> Positioning by key.
    >>>>>>> Keys are:
    >>>>>>> CLCL_ID ASC
    >>>>>>> Using I/O Size 2 Kbytes for index leaf pages.
    >>>>>>> With LRU Buffer Replacement Strategy for index leaf pages.

    >
    >>>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>>
    >>>>>>>
    >>>>>>> FROM TABLE
    >>>>>>> CMC_CDML_CL_LINE
    >>>>>>> SUB_CDML1
    >>>>>>> EXISTS TABLE : nested iteration.
    >>>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>>> Forward scan.
    >>>>>>> Positioning by key.
    >>>>>>> Keys are:
    >>>>>>> CLCL_ID ASC
    >>>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>>> With LRU Buffer Replacement Strategy for index leaf pages.

    >
    >>>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>>
    >>>>>>>
    >>>>>>> FROM TABLE
    >>>>>>> CMC_CDML_CL_LINE
    >>>>>>> SUB_CDML2
    >>>>>>> EXISTS TABLE : nested iteration.
    >>>>>>> Index : CMCX_CDML_PRIMARY
    >>>>>>> Forward scan.
    >>>>>>> Positioning by key.
    >>>>>>> Keys are:
    >>>>>>> CLCL_ID ASC
    >>>>>>> Using I/O Size 16 Kbytes for index leaf pages.
    >>>>>>> With LRU Buffer Replacement Strategy for index leaf pages.

    >
    >>>>>>> Using I/O Size 2 Kbytes for data pages.
    >>>>>>> With LRU Buffer Replacement Strategy for data pages.
    >>>>>>> TO TABLE
    >>>>>>> Worktable1.
    >>>>>>>
    >>>>>>>
    >>>>>>> STEP 2
    >>>>>>> The type of query is SELECT.
    >>>>>>> This step involves sorting.
    >>>>>>>
    >>>>>>>
    >>>>>>> FROM TABLE
    >>>>>>> Worktable1.
    >>>>>>> Using GETSORTED
    >>>>>>> Table Scan.
    >>>>>>> Forward scan.
    >>>>>>> Positioning at start of table.
    >>>>>>> Using I/O Size 16 Kbytes for data pages.
    >>>>>>> With MRU Buffer Replacement Strategy for data pages.
    >>>>>>>
    >>>>>>>
    >>>>>>>Parse and Compile Time 0.
    >>>>>>>SQL Server cpu time: 0 ms.
    >>>>>>>Server Message: Number 1562, Severity 10
    >>>>>>>Server 'aims_dev2', Line 1:
    >>>>>>>The sort for Worktable1 is done in Serial
    >>>>>>>Table: CMC_CLCL_CLAIM scan count 1, logical reads: (regular=150202

    >apf=0
    >>>>>>>total=150202), physical reads: (regular=5584 apf=13331 total=18915),
    >>>apf
    >>>>>>>IOs used=13228
    >>>>>>>Table: CMC_SBSB_SUBSC scan count 58198, logical reads: (regular=233431
    >>>>>apf=11
    >>>>>>>total=233442), physical reads: (regular=4573 apf=412 total=4985),

    apf
    >>>>IOs
    >>>>>>>used=209
    >>>>>>>Table: CMC_CDML_CL_LINE scan count 58198, logical reads: (regular=361037
    >>>>>>>apf=27 total=361064), physical reads: (regular=16320 apf=89551 total=105871),
    >>>>>>>apf IOs used=88751
    >>>>>>>Table: CMC_CLHP_HOSP scan count 4256, logical reads: (regular=17084

    >>apf=0
    >>>>>>>total=17084), physical reads: (regular=2117 apf=18 total=2135), apf

    >>IOs
    >>>>>>used=18
    >>>>>>>
    >>>>>>>Table: CMC_CDML_CL_LINE scan count 17, logical reads: (regular=170

    >apf=0
    >>>>>>>total=170), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    >>>>
    >>>>>>>Table: CMC_CDML_CL_LINE scan count 198, logical reads: (regular=2038
    >>>apf=0
    >>>>>>>total=2038), physical reads: (regular=4 apf=3 total=7), apf IOs used=15
    >>>>>>
    >>>>>>>Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9),
    >>>>>>>physical reads: (regular=2 apf=0 total=2), apf IOs used=0
    >>>>>>>Total writes for this command: 6
    >>>>>>>
    >>>>>>>Execution Time 144.
    >>>>>>>SQL Server cpu time: 14400 ms. SQL Server elapsed time: 266126 ms.
    >>>>>>>(1 row affected)
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>"KevinV" <none@none.com> wrote:
    >>>>>>>>
    >>>>>>>>Have you ran this in Query Analyzer with 'Show Execution Plan' on?
    >>>>>>>>
    >>>>>>>>There is a lot to tuning queries. The query analyzer execution plan
    >>>is
    >>>>>>usually
    >>>>>>>>a good place to start. Look for the step in the execution plan that
    >>>has
    >>>>>>>the
    >>>>>>>>highest cost and start there to look for optimizations. Generally

    >adding
    >>>>>>>>indexes for certain columns, like columns referenced in joins and

    >where
    >>>>>>>clauses
    >>>>>>>>is what will improve the query the most. If you see steps that are

    >>doing
    >>>>>>>>table scans then that likely means you need an index. It is also

    possible
    >>>>>>>>that your query will just be slow depending on the number of rows

    >of
    >>>>data,
    >>>>>>>>physical placement of data, log and index database files and the

    configuration
    >>>>>>>>of the server.
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>"dhaya" <shivdayal@hotmail.com> wrote:
    >>>>>>>>>
    >>>>>>>>>any way to this query , its very slow.
    >>>>>>>>>
    >>>>>>>>>SELECT CMC_CLCL_CLAIM.CLCL_ID,
    >>>>>>>>> CMC_SBSB_SUBSC.SBSB_ID,
    >>>>>>>>> CMC_CLCL_CLAIM.CLCL_TOT_CHG,
    >>>>>>>>> CMC_CLCL_CLAIM.CLCL_TOT_PAYABLE,
    >>>>>>>>> CMC_CLCL_CLAIM.CLCL_CUR_STS,
    >>>>>>>>> CMC_CLCL_CLAIM.CLCL_LOW_SVC_DT,
    >>>>>>>>> CMC_CLCL_CLAIM.CLCL_HIGH_SVC_DT,
    >>>>>>>>> CMC_CLCL_CLAIM.PDPD_ID,
    >>>>>>>>> CMC_CDML_CL_LINE.CDML_COPAY_AMT,
    >>>>>>>>> CMC_CDML_CL_LINE.CDML_CHG_AMT,
    >>>>>>>>> CMC_CDML_CL_LINE.CDML_ALLOW,
    >>>>>>>>> CMC_CDML_CL_LINE.CDML_SEQ_NO
    >>>>>>>>>into adhocdb..JP_COPAY_ERRORS
    >>>>>>>>>FROM CMC_CLCL_CLAIM
    >>>>>>>>>
    >>>>>>>>>INNER JOIN CMC_SBSB_SUBSC
    >>>>>>>>>ON CMC_CLCL_CLAIM.SBSB_CK = CMC_SBSB_SUBSC.SBSB_CK
    >>>>>>>>>
    >>>>>>>>>INNER JOIN CMC_CDML_CL_LINE
    >>>>>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CDML_CL_LINE.CLCL_ID
    >>>>>>>>>
    >>>>>>>>>INNER JOIN CMC_CLHP_HOSP
    >>>>>>>>>ON CMC_CLCL_CLAIM.CLCL_ID = CMC_CLHP_HOSP.CLCL_ID
    >>>>>>>>>
    >>>>>>>>>WHERE
    >>>>>>>>>
    >>>>>>>>>CMC_CDML_CL_LINE.SESE_ID In
    >>>>>>>>> ('ARB','ARBP','ARBS','CCU','HOSP','HOS','HOLD','HOLP',
    >>>>>>>>> 'ISOL','ICU','ICUP','ICUS','MATN','MPRN','NUR','PRB','PRBP',
    >>>>>>>>> 'PRBS','PRBD','PRBR','RB','RBP','RBS','RBD','RBDR','SARB',
    >>>>>>>>> 'SPRB','SRBR','SRB','SBDR','SKNU')
    >>>>>>>>>AND CMC_CLCL_CLAIM.CLCL_CL_SUB_TYPE='H'
    >>>>>>>>>and CMC_CLCL_CLAIM.CLCL_RECD_DT <= '07/01/2003' AND CMC_CLCL_CLAIM.CLCL_RECD_DT
    >>>>>>>>>>= '07/07/2003'
    >>>>>>>>>AND CMC_CLHP_HOSP.CLHP_FAC_TYPE = '1'
    >>>>>>>>>AND CMC_CLHP_HOSP.CLHP_BILL_CLASS IN ('E','U','S','3')
    >>>>>>>>>AND CMC_CLCL_CLAIM.CLCL_ID IN
    >>>>>>>>>(
    >>>>>>>>>SELECT A.CLCL_ID FROM CMC_CDML_CL_LINE A
    >>>>>>>>>INNER JOIN CMC_CDML_CL_LINE B
    >>>>>>>>>ON A.CLCL_ID = B.CLCL_ID
    >>>>>>>>> WHERE
    >>>>>>>>>A.CDML_SEQ_NO <> B.CDML_SEQ_NO
    >>>>>>>>>AND A.CDML_COPAY_AMT <> B.CDML_COPAY_AMT
    >>>>>>>>>)
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>>The last inner query IN is to get from a detail table rows which

    >have
    >>>>>>different
    >>>>>>>>>copay amount
    >>>>>>>>>
    >>>>>>>>>example
    >>>>>>>>>111 will be selected in below case
    >>>>>>>>>
    >>>>>>>>>claim_id sequence_no copay
    >>>>>>>>>111 1 10.0
    >>>>>>>>>111 2 20.0
    >>>>>>>>>222 1 100.0
    >>>>>>>>>222 2 100.0
    >>>>>>>>>
    >>>>>>>>>Thank you!
    >>>>>>>>>dhaya
    >>>>>>>>>
    >>>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center