July 8, 2010 at 3:42 am
Hello,
seeing your SQL instruction hi have some ideas, lets me explain:
In a generic way you should try to simplify your query and test it. So you should test each part of the UNION separately; try to comment each subquery and test if it reduces execution time; try to comment each joined table and test if it reduces execution time; maybe in one of these steps you can obtain an important reduction of time, this should be a good clue about what to do.
More specific:
In the WHERE clause you have this restriction,
AND ac.practice_id = '0012'
AND ac.acct_id = '3CE69763-F380-4D2C-8482-245E264AB952'
Maybe practice_id + acct_id is unique key for your ACCOUNTS table?. If so you can try to execute this sentence first,
SELECT @guar_id = guar_id, @guar_type = guar_type
FROM acounts
WHERE practice_id = '0012' AND acct_id = '3CE69763-F380-4D2C-8482-245E264AB952'
and next run your query replacing all references to ACCOUNTS by the variables returned by this first SELECT.
Your second subquery:
SELECT SUM(ISNULL(td.paid_amt,0)) +
SUM(ISNULL(td.adj_amt,0))
from transaction_hostory td
WHERE td.practice_id = vc.practice_id
and td.charge_id = vc.charge_id
and td.source_id = vc.source_id
AND td.trans_id NOT IN ('3D476601-C585-44E3-8D49-7AE4FD7B890D', 'D8EFF527-CE55-4DC5-81EF-C29802647D8A')
GROUP BY td.charge_id
Why have you a GROUP BY clause?, charge_id is part of the WHERE clause. Try to create a VIEW with this instruction,
SELECT practice_id, charge_id, source_id, SUM(ISNULL(td.paid_amt,0)) +
SUM(ISNULL(td.adj_amt,0))
FROM transaction_hostory td
WHERE trans_id NOT IN ('3D476601-C585-44E3-8D49-7AE4FD7B890D', 'D8EFF527-CE55-4DC5-81EF-C29802647D8A')
GROUP BY practice_id, charge_id, source_id
and then replace your subquery by a JOIN with this new view.
Also you can try to replace
SUM(ISNULL(td.paid_amt,0)) + SUM(ISNULL(td.adj_amt,0))
by
SUM(ISNULL(td.paid_amt,0) + ISNULL(td.adj_amt,0)),
maybe it works, there is nothing to loose.
Your first subquery:
(Select top 1 sim.description
from services sim
where vc.service_item_lib_id = sim.service_item_lib_id
and vc.service_item_id = sim.service_item_id),
It looks strange to me. Why this TOP 1?, maybe there are some descriptions available?, or there are duplicate records with the same description?. Anyway try to create a view and replace the subquery by this new view.
I Hope you find it useful,
Francesc
July 8, 2010 at 5:40 am
Something like the following may help:
SELECT -- DISTINCT ?
vc.charge_id,
vc.service_item_id,
S.description,
vc.cpt4_code_id,
ISNULL(vc.amt,0),
vc.source_id,
vc.begin_date_of_service,
vc.seq_nbr,
vc.source_type,
vc.link_id,
um.last_name,
vc.create_timestamp,
um2.last_name,
vc.modify_timestamp,
vc.row_timestamp,
ISNULL(vc.cob1_amt, 0),
ISNULL(vc.cob2_amt, 0),
ISNULL(vc.cob3_amt, 0),
ISNULL(vc.pat_amt, 0),
vc.rendering_id,
pm.description,
D.enc_nbr,
D.enc_status,
ISNULL(H.total_transaction_history, 0) AS total_transaction_history,
D.enc_id,
D.pat_resp_date,
vc.person_id,
vc.invoice_desc_1,
vc.invoice_desc_2
FROM total_charge vc
JOIN
(
SELECT pe.practice_id
,pe.enc_id
,pe.enc_nbr
,pe.enc_status
,pe.enc_id
,pe.pat_resp_date
,pe.guar_id
,pe.guar_type
,'V' AS source_type
FROM patient_encounter pe
WHERE pe.practice_id = '0012'
AND pe.billable_ind = 'Y'
AND pe.enc_status != 'A'
UNION ALL
SELECT iv.practice_id
,iv.invoice_id
,iv.invoice_nbr
,iv.status
,pe.enc_id
,pe.pat_resp_date
,iv.acct_id
,'D' -- dummy value, assuming pe.guar_type is char(1)
,'I' AS source_type
FROM invoices iv
LEFT JOIN patient_encounter pe
ON iv.practice_id = pe.practice_id and iv.invoice_id = pe.enc_id
WHERE iv.practice_id = '0012'
)D
ON vc.practice_id = D.practice_id
AND vc.source_id = D.enc_id
AND vc.source_type = D.source_type
JOIN accounts ac
ON vc.practice_id = ac.practice_id
AND D.guar_id = ac.guar_id
-- edited to account for 'D'
AND D.guar_type = CASE D.source_type WHEN 'V' THEN ac.guar_type ELSE 'D' END
AND ac.acct_id = '3CE69763-F380-4D2C-8482-245E264AB952'
LEFT JOIN user_mstr um
ON vc.created_by = um.user_id
LEFT JOIN user_mstr um2
ON vc.modified_by = um2.user_id
LEFT JOIN provider_mstr pm
ON vc.rendering_id = pm.provider_id
LEFT JOIN
(
SELECT sim.service_item_lib_id, sim.service_item_id, sim.description
,ROW_NUMBER() OVER (PARTITION BY vc.service_item_lib_id, vc.service_item_id ORDER BY sim.description) AS simRowNum
FROM services sim
) S
ON vc.service_item_lib_id = S.service_item_lib_id
AND vc.service_item_id = S.service_item_id
AND S.simRowNum = 1
LEFT JOIN
(
SELECT td.practice_id, td.charge_id, td.source_id
,ISNULL(SUM(td.paid_amt), 0) + ISNULL(SUM(td.adj_amt), 0) AS total_transaction_history
FROM transaction_hostory td
WHERE td.trans_id NOT IN ('3D476601-C585-44E3-8D49-7AE4FD7B890D', 'D8EFF527-CE55-4DC5-81EF-C29802647D8A')
GROUP BY td.practice_id, td.charge_id, td.source_id
) H
ON vc.practice_id = H.practice_id
AND vc.charge_id = H.charge_id
AND vc.source_id = H.source_id
WHERE vc.practice_id = '0012'
ORDER BY 22, 8, 7
July 8, 2010 at 5:56 am
Try using the DB tuning advisor.. it will suggest what indexes/statistics are missing. But dont blindly apply the recommendations. Those recommendations will help in fine tuning your query.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply