November 20, 2007 at 3:47 pm
Hi,
I would appreaciate some assitance in getting the following query to run faster:
Within the application the query takes about 10 seconds to run.
Server is 2005, clustered server, with 32 gig of RAM.
The query:
SET DATEFORMAT DMY
SELECT
CLAIM.SB_ITEM_CLAIM_ID,
CLAIM.SB_AMOUNT_CLAIMED,
ISNULL(CLAIM.SB_GST_CLAIMED,0) AS SB_GST_CLAIMED,
CLAIM.SB_AMOUNT_PAID,
ISNULL(CLAIM.SB_GST_PAID,0) AS SB_GST_PAID,
claim.SB_ADJUST_APPROVED,
claim.SB_ADJUST_APPROVED_BY,
ABS(CLAIM.SB_AMOUNT_CLAIMED) AS SORT_AMOUNT,
CASE WHEN CLAIM.SB_ITEM_CLAIM_COMMENT <> '' AND CLAIM.SB_ITEM_CLAIM_COMMENT IS NOT NULL THEN
CLAIM.SB_ITEM_CLAIM_COMMENT
ELSE
''
END AS SB_ITEM_CLAIM_COMMENT,
CLAIM.SB_CLAIM_TYPE_CODE,
CLAIM.SB_CREATED_BY,
TYPE.SB_CLAIM_TYPE_DESC,
ITEM.SB_ITEM_STATUS_CODE,
ITEM.SB_INVOICE_ITEM_ID,
ITEM.SB_MBS_ITEM_NUMBER,
ITEM.SB_INVOICE_COST - ISNULL(ITEM.SB_HIC_AMOUNT_PAID,0)
- ISNULL(ITEM.SB_FUND_AMOUNT_PAID,0)
- ISNULL(ITEM.SB_WRITEOFF_AMOUNT,0)
- ISNULL(ITEM.SB_ADJUST_AMOUNT,0)
AS BALANCE,
CLAIM.SB_UNDO_ADJUSTMENT,
ISNULL(INVOICE_PRINT.SB_INVOICE_PRINT_INV_NO, INVOICE.SB_INVOICE_NUMBER) AS SB_INVOICE_NUMBER,
ISNULL(INVOICE_PRINT.SB_INVOICE_PRINT_INV_NO,'XX') as INV_NO,
PATIENT.SB_PATIENT_MRN,
PATIENT.SB_PATIENT_SURNAME + ', ' + LEFT(PATIENT.SB_PATIENT_OTHER_NAMES, 1) AS PATIENT,
PATIENT.SB_HL7_SOURCE,
REASON.SB_ADJUST_CODE AS REASON_CODE,
REASON.SB_ADJUST_DESC AS REASON,
CLAIM.SB_CLAIM_DATE,
INVOICE.SB_INVOICE_ID,
INVOICE.SB_INVOICE_PRINT_ID,
CASE
WHEN CLAIM.SB_ADJUST_APPROVED = 'Y' THEN
'Yes'
when CLAIM.SB_ADJUST_APPROVED = 'n' then
'No'
when
(
(isnull(SB_CAN_APPROVE_OWN,'n') = 'n' and CLAIM.SB_CREATED_BY <> 'sthornton')
or
isnull(SB_CAN_APPROVE_OWN,'n') = 'y'
) and
(
(
-- INTERNAL WRITEOFF
INVOICE.SB_SOURCE_CODE = 'I' AND
ABS(CLAIM.SB_AMOUNT_CLAIMED) <= ISNULL(SB_WRITEOFF_INTERNAL,0) AND
CLAIM.SB_CLAIM_TYPE_CODE = 'WRI'
)
OR
(
-- EXTERNAL WRITEOFF
INVOICE.SB_SOURCE_CODE <> 'I' AND
ABS(CLAIM.SB_AMOUNT_CLAIMED) <= ISNULL(SB_WRITEOFF_EXTERNAL,0) AND
CLAIM.SB_CLAIM_TYPE_CODE = 'WRI'
)
OR
(
CLAIM.SB_CLAIM_TYPE_CODE <> 'WRI' AND
INVOICE.SB_SOURCE_CODE = 'I' AND
REASON.SB_ADJUST_DB_CR = 'D' AND
ABS(CLAIM.SB_AMOUNT_CLAIMED) <= ISNULL(SB_ADJUST_INTERNAL_DEBIT,0)
)
OR
(
CLAIM.SB_CLAIM_TYPE_CODE <> 'WRI' AND
INVOICE.SB_SOURCE_CODE = 'I' AND
REASON.SB_ADJUST_DB_CR = 'C' AND
ABS(CLAIM.SB_AMOUNT_CLAIMED) <= ISNULL(SB_ADJUST_INTERNAL_CREDIT,0)
)
OR
(
CLAIM.SB_CLAIM_TYPE_CODE <> 'WRI' AND
INVOICE.SB_SOURCE_CODE <> 'I' AND
REASON.SB_ADJUST_DB_CR = 'D' AND
ABS(CLAIM.SB_AMOUNT_CLAIMED) <= ISNULL(SB_ADJUST_EXTERNAL_DEBIT,0)
)
OR
(
CLAIM.SB_CLAIM_TYPE_CODE <> 'WRI' AND
INVOICE.SB_SOURCE_CODE <> 'I' AND
REASON.SB_ADJUST_DB_CR = 'C' AND
ABS(CLAIM.SB_AMOUNT_CLAIMED) <= ISNULL(SB_ADJUST_EXTERNAL_CREDIT,0)
)
)
then
'approve'
else
''
end as adjust_approval
FROM
SB_ITEM_CLAIM CLAIM INNER JOIN
SB_CLAIM_TYPE TYPE ON
CLAIM.SB_CLAIM_TYPE_CODE = TYPE.SB_CLAIM_TYPE_CODE INNER JOIN
SB_INVOICE_ITEM ITEM ON
ITEM.SB_INVOICE_ITEM_ID = CLAIM.SB_INVOICE_ITEM_ID INNER JOIN
SB_INVOICE INVOICE ON
INVOICE.SB_INVOICE_ID = ITEM.SB_INVOICE_ID INNER JOIN
SB_PATIENT_EPISODE EPISODE ON
INVOICE.SB_EPISODE_NUMBER = EPISODE.SB_EPISODE_NUMBER INNER JOIN
SB_PATIENT_REGISTER PATIENT ON
EPISODE.SB_PATIENT_MRN = PATIENT.SB_PATIENT_MRN AND
EPISODE.SB_HL7_SOURCE = PATIENT.SB_HL7_SOURCE inner join
SB_ADJUSTMENT_REASON REASON ON
CLAIM.SB_ADJUST_CODE = REASON.SB_ADJUST_CODE inner join
SB_HOSPITAL HOSPITAL ON
EPISODE.SB_HOSPITAL_CODE = HOSPITAL.SB_HOSPITAL_CODE INNER JOIN
SB_INVOICE_PRINT INVOICE_PRINT ON
INVOICE.SB_INVOICE_PRINT_ID = INVOICE_PRINT.SB_INVOICE_PRINT_ID INNER JOIN
SB_SECURITY_LEVEL SECURITY ON
SECURITY.SB_SECURITY_CODE = 'SYS'
WHERE
claim.sb_claim_type_code in ('ADJ','CA','CAR','WRI','WRR')
AND CLAIM.SB_ADJUST_APPROVED is null
ORDER BY
INVOICE.SB_INVOICE_ID
The execution plan:
|--Parallelism(Gather Streams, ORDER BY:([ITEM].[SB_INVOICE_ID] ASC))
|--Sort(ORDER BY:([ITEM].[SB_INVOICE_ID] ASC))
|--Compute Scalar(DEFINE:([Expr1025]=isnull([Platypus2].[dbo].[SB_INVOICE_PRINT].[SB_INVOICE_PRINT_INV_NO] as [INVOICE_PRINT].[SB_INVOICE_PRINT_INV_NO],[Platypus2].[dbo].[SB_INVOICE].[SB_INVOICE_NUMBER] as [INVOICE].[SB_INVOICE_NUMBER]), [Expr1
|--Hash Match(Inner Join, HASH:([EPISODE].[SB_PATIENT_MRN], [EPISODE].[SB_HL7_SOURCE])=([PATIENT].[SB_PATIENT_MRN], [PATIENT].[SB_HL7_SOURCE]), RESIDUAL:([Platypus2].[dbo].[SB_PATIENT_EPISODE].[SB_PATIENT_MRN] as [EPISODE].[SB_PATIENT_MRN]
|--Bitmap(HASH:([EPISODE].[SB_PATIENT_MRN], [EPISODE].[SB_HL7_SOURCE]), DEFINE:([Bitmap1040]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([EPISODE].[SB_PATIENT_MRN], [EPISODE].[SB_HL7_SOURCE]))
| |--Hash Match(Inner Join, HASH:([HOSPITAL].[SB_HOSPITAL_CODE])=([EPISODE].[SB_HOSPITAL_CODE]), RESIDUAL:([Platypus2].[dbo].[SB_PATIENT_EPISODE].[SB_HOSPITAL_CODE] as [EPISODE].[SB_HOSPITAL_CODE]=[Platypus2].[dbo].[SB_HOSPITA
| |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | |--Index Scan(OBJECT:([Platypus2].[dbo].[SB_HOSPITAL].[Relation_752435_FK] AS [HOSPITAL]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([INVOICE].[SB_EPISODE_NUMBER], [Expr1039]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Hash Match(Inner Join, HASH:([INVOICE].[SB_INVOICE_PRINT_ID])=([INVOICE_PRINT].[SB_INVOICE_PRINT_ID]), RESIDUAL:([Platypus2].[dbo].[SB_INVOICE].[SB_INVOICE_PRINT_ID] as [INVOICE].[SB_INVOICE_PRINT_ID]=[Platypus2
| | |--Bitmap(HASH:([INVOICE].[SB_INVOICE_PRINT_ID]), DEFINE:([Bitmap1038]))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([INVOICE].[SB_INVOICE_PRINT_ID]))
| | | |--Hash Match(Inner Join, HASH:([ITEM].[SB_INVOICE_ID])=([INVOICE].[SB_INVOICE_ID]), RESIDUAL:([Platypus2].[dbo].[SB_INVOICE_ITEM].[SB_INVOICE_ID] as [ITEM].[SB_INVOICE_ID]=[Platypus2].[dbo].[SB_INVO
| | | |--Bitmap(HASH:([ITEM].[SB_INVOICE_ID]), DEFINE:([Bitmap1037]))
| | | | |--Compute Scalar(DEFINE:([Expr1024]=((([Platypus2].[dbo].[SB_INVOICE_ITEM].[SB_INVOICE_COST] as [ITEM].[SB_INVOICE_COST]-isnull([Platypus2].[dbo].[SB_INVOICE_ITEM].[SB_HIC_AMOUNT_PAID] as
| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ITEM].[SB_INVOICE_ID]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([CLAIM].[SB_INVOICE_ITEM_ID], [Expr1036]) OPTIMIZED WITH UNORDERED PREFETCH)
| | | | |--Hash Match(Inner Join, HASH:([TYPE].[SB_CLAIM_TYPE_CODE])=([CLAIM].[SB_CLAIM_TYPE_CODE]), RESIDUAL:([Platypus2].[dbo].[SB_ITEM_CLAIM].[SB_CLAIM_TYPE_CODE] as [CLAIM].[SB_C
| | | | | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | | | | |--Clustered Index Seek(OBJECT:([Platypus2].[dbo].[SB_CLAIM_TYPE].[PK_SB_CLAIM_TYPE] AS [TYPE]), SEEK:([TYPE].[SB_CLAIM_TYPE_CODE] >= 'ADJ' AND [TYPE].[SB_CLAIM_TYP
| | | | | |--Hash Match(Inner Join, HASH:([REASON].[SB_ADJUST_CODE])=([CLAIM].[SB_ADJUST_CODE]), RESIDUAL:([Platypus2].[dbo].[SB_ITEM_CLAIM].[SB_ADJUST_CODE] as [CLAIM].[SB_ADJUST
| | | | | |--Bitmap(HASH:([REASON].[SB_ADJUST_CODE]), DEFINE:([Bitmap1035]))
| | | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([REASON].[SB_ADJUST_CODE]))
| | | | | | |--Nested Loops(Inner Join)
| | | | | | |--Clustered Index Seek(OBJECT:([Platypus2].[dbo].[SB_SECURITY_LEVEL].[PK_SB_SECURITY_LEVEL] AS [SECURITY]), SEEK:([SECURITY].[SB_SECURITY_CODE]='SYS
| | | | | | |--Clustered Index Scan(OBJECT:([Platypus2].[dbo].[SB_ADJUSTMENT_REASON].[PK_SB_ADJUSTMENT_REASON] AS [REASON]))
| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([CLAIM].[SB_ADJUST_CODE]), WHERE:(PROBE([Bitmap1035])=TRUE))
| | | | | |--Compute Scalar(DEFINE:([Expr1020]=isnull([Platypus2].[dbo].[SB_ITEM_CLAIM].[SB_GST_CLAIMED] as [CLAIM].[SB_GST_CLAIMED],(0.00)), [Expr1021]=isnull([Platypus
| | | | | |--Clustered Index Scan(OBJECT:([Platypus2].[dbo].[SB_ITEM_CLAIM].[PK_SB_ITEM_CLAIM] AS [CLAIM]), WHERE:([Platypus2].[dbo].[SB_ITEM_CLAIM].[SB_ADJUST_APPR
| | | | |--Clustered Index Seek(OBJECT:([Platypus2].[dbo].[SB_INVOICE_ITEM].[PK_SB_INVOICE_ITEM] AS [ITEM]), SEEK:([ITEM].[SB_INVOICE_ITEM_ID]=[Platypus2].[dbo].[SB_ITEM_CLAIM].[SB_I
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([INVOICE].[SB_INVOICE_ID]), WHERE:(PROBE([Bitmap1037])=TRUE))
| | | |--Clustered Index Scan(OBJECT:([Platypus2].[dbo].[SB_INVOICE].[PK_SB_INVOICE] AS [INVOICE]))
| | |--Compute Scalar(DEFINE:([Expr1026]=isnull([Platypus2].[dbo].[SB_INVOICE_PRINT].[SB_INVOICE_PRINT_INV_NO] as [INVOICE_PRINT].[SB_INVOICE_PRINT_INV_NO],'XX')))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([INVOICE_PRINT].[SB_INVOICE_PRINT_ID]), WHERE:(PROBE([Bitmap1038])=TRUE))
| | |--Index Scan(OBJECT:([Platypus2].[dbo].[SB_INVOICE_PRINT].[invoice_no_idx] AS [INVOICE_PRINT]))
| |--Clustered Index Seek(OBJECT:([Platypus2].[dbo].[SB_PATIENT_EPISODE].[PK_SB_PATIENT_EPISODE] AS [EPISODE]), SEEK:([EPISODE].[SB_EPISODE_NUMBER]=[Platypus2].[dbo].[SB_INVOICE].[SB_EPISODE_NUMBER] as [INVOICE].[SB_
|--Compute Scalar(DEFINE:([Expr1027]=([Platypus2].[dbo].[SB_PATIENT_REGISTER].[SB_PATIENT_SURNAME] as [PATIENT].[SB_PATIENT_SURNAME]+', ')+substring([Platypus2].[dbo].[SB_PATIENT_REGISTER].[SB_PATIENT_OTHER_NAMES] as [PATIENT].[SB_PAT
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([PATIENT].[SB_PATIENT_MRN], [PATIENT].[SB_HL7_SOURCE]), WHERE:(PROBE([Bitmap1040])=TRUE))
|--Index Scan(OBJECT:([Platypus2].[dbo].[SB_PATIENT_REGISTER].[surname_idx] AS [PATIENT]))
Statistics:
Table 'SB_HOSPITAL'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_CLAIM_TYPE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_ADJUSTMENT_REASON'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_SECURITY_LEVEL'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_ITEM_CLAIM'. Scan count 9, logical reads 60055, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_INVOICE_ITEM'. Scan count 0, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_INVOICE'. Scan count 9, logical reads 19859, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_INVOICE_PRINT'. Scan count 9, logical reads 1679, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_PATIENT_EPISODE'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SB_PATIENT_REGISTER'. Scan count 9, logical reads 2407, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
November 21, 2007 at 6:38 am
well maybe 10 secs is good for this. clustered sql server has no bearing upon performance btw.
you need to eliminate the scans and get some seeks - you might want to try adding a maxdop - if your server has HT then disable it - try setting maxdop to equal half the cores and work your way down seeing what difference you get in overall performance - take heed on your data being in cache so either work without physical io or with all physical io by clearing cache.
not equals sargs are always bad news as are multiple OR statements and else statements - you might want to break your code up - I assume it's a proc?
I don't really think it's fair to drop large queries like this on a forum - there's a good number of us who make a living either writing good code, tuning code or teaching others how to write code - you should get some books and maybe a training course or get in an expert to explain it all to you.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply