query tuning

dhaya · 08-18-2003, 07:54 PM

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

Rune Bivrin · 08-19-2003, 01:51 PM

"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

KevinV · 08-19-2003, 01:52 PM

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
>

dhaya · 08-19-2003, 02:24 PM

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
>>
>

dhaya · 08-19-2003, 02:27 PM

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
>>>
>>
>

KevinV · 08-19-2003, 05:17 PM

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
>>>>
>>>
>>
>

dhaya · 08-20-2003, 12:16 AM

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
>>>>>
>>>>
>>>
>>
>

dhaya · 08-20-2003, 11:05 AM

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
>>>>>>
>>>>>
>>>>
>>>
>>
>

KevinV · 08-20-2003, 02:13 PM

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
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

dhaya · 08-22-2003, 12:39 PM

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
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

dhaya · 08-22-2003, 12:39 PM

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
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

KevinV · 08-25-2003, 05:24 PM

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
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

Thread: query tuning

Thread Tools

Display

query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Re: query tuning

Posting Permissions