July 1, 2010 at 9:19 am
I have a query that has some problem. I mean it take long time to execute so i ran an estimated execution plan but i did not understand what tables exactly might needs indexes or may be there's something else. Can someone help me out what needs to be done here. The query is-------
SELECT vc.charge_id, vc.service_item_id,
(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), 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, pe.enc_nbr, pe.enc_status, ISNULL((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),0) as total_transaction_hostory,pe.enc_id, pe.pat_resp_date,
vc.person_id, vc.invoice_desc_1, vc.invoice_desc_2
from total_charge vc inner join patient_encounter pe on
vc.practice_id = pe.practice_id and vc.source_id = pe.enc_id inner join accounts
ac on pe.practice_id = ac.practice_id and pe.guar_id = ac.guar_id
and pe.guar_type = ac.guar_type 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 WHERE vc.practice_id = '0012' AND
pe.practice_id = '0012' AND ac.practice_id = '0012' AND
ac.acct_id = '3CE69763-F380-4D2C-8482-245E264AB952' AND
pe.billable_ind = 'Y' AND vc.source_type = 'V' AND pe.enc_status != 'A'
UNION SELECT vc.charge_id, vc.service_item_id, (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) , 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, iv.invoice_nbr, iv.status,
ISNULL((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),0) as total_transaction_hostory ,pe.enc_id, pe.pat_resp_date, vc.person_id,
vc.invoice_desc_1, vc.invoice_desc_2 from total_charge vc inner join invoices iv on
vc.practice_id = iv.practice_id and vc.source_id = iv.invoice_id inner join accounts
ac on iv.practice_id = ac.practice_id and iv.acct_id = ac.acct_id 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 patient_encounter pe
on vc.practice_id = pe.practice_id and vc.source_id = pe.enc_id
WHERE vc.practice_id = '0012' AND iv.practice_id = '0012' AND
ac.practice_id = '0012' AND ac.acct_id = '3CE69763-F380-4D2C-8482-245E264AB952'
AND vc.source_type = 'I' ORDER BY 22, 8, 7
Need to check what the execution plan says. so please get me some recommendations to optimize this query.
July 1, 2010 at 9:23 am
This is huge, and I'd recommend you format it so it can be better read. Put the joins on separate lines, the FROM, the WHERE, etc.
In terms of your execution plan, you might want to look for SCANs and see if you can add an index to change them to seeks. I'd also remove the union, and optimize each side of it first.
July 1, 2010 at 9:35 am
There are two UNIONed queries here, each containing two correlated subqueries.
Run each UNIONed query separately and note times.
Coment out the correlated subqueries and run and time again. This will give you a much better starting point, and will only take a couple of minutes.
Here's a formatted version of your query as requested by Steve - it's much easier for humans to read.
SELECT
vc.charge_id,
vc.service_item_id,
-- 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),
-- subquery
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,
pe.enc_nbr,
pe.enc_status,
-- subquery
ISNULL((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),0) as total_transaction_hostory,
-- subquery
pe.enc_id,
pe.pat_resp_date,
vc.person_id,
vc.invoice_desc_1,
vc.invoice_desc_2
from total_charge vc
inner join patient_encounter pe
on vc.practice_id = pe.practice_id and vc.source_id = pe.enc_id
inner join accounts ac
on pe.practice_id = ac.practice_id and pe.guar_id = ac.guar_id and pe.guar_type = ac.guar_type
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
WHERE vc.practice_id = '0012'
AND pe.practice_id = '0012'
AND ac.practice_id = '0012'
AND ac.acct_id = '3CE69763-F380-4D2C-8482-245E264AB952'
AND pe.billable_ind = 'Y'
AND vc.source_type = 'V'
AND pe.enc_status != 'A'
UNION
SELECT
vc.charge_id,
vc.service_item_id,
-- 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),
-- subquery
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,
iv.invoice_nbr,
iv.status,
-- subquery
ISNULL((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),0) as total_transaction_hostory,
-- subquery
pe.enc_id,
pe.pat_resp_date,
vc.person_id,
vc.invoice_desc_1,
vc.invoice_desc_2
from total_charge vc
inner join invoices iv
on vc.practice_id = iv.practice_id and vc.source_id = iv.invoice_id
inner join accounts ac
on iv.practice_id = ac.practice_id and iv.acct_id = ac.acct_id
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 patient_encounter pe
on vc.practice_id = pe.practice_id and vc.source_id = pe.enc_id
WHERE vc.practice_id = '0012'
AND iv.practice_id = '0012'
AND ac.practice_id = '0012'
AND ac.acct_id = '3CE69763-F380-4D2C-8482-245E264AB952'
AND vc.source_type = 'I'
ORDER BY 22, 8, 7
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 1, 2010 at 9:36 am
Sorr I had to reformat the query so It could be veiwed easily.
SELECT
vc.charge_id,
vc.service_item_id,
(
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
),
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,
pe.enc_nbr,
pe.enc_status,
ISNULL(
(
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),0
) as total_transaction_hostory,
pe.enc_id,
pe.pat_resp_date,
vc.person_id,
vc.invoice_desc_1,
vc.invoice_desc_2
from total_charge vc
inner join patient_encounter pe on vc.practice_id = pe.practice_id
and vc.source_id = pe.enc_id
inner join accounts ac on pe.practice_id = ac.practice_id
and pe.guar_id = ac.guar_id
and pe.guar_type = ac.guar_type
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
WHERE vc.practice_id = '0012'
AND pe.practice_id = '0012'
AND ac.practice_id = '0012'
AND ac.acct_id = '3CE69763-F380-4D2C-8482-245E264AB952'
AND pe.billable_ind = 'Y'
AND vc.source_type = 'V'
AND pe.enc_status != 'A'
UNION
SELECT
vc.charge_id,
vc.service_item_id,
(
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
) ,
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,
iv.invoice_nbr,
iv.status,
ISNULL(
(
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
)
,0) as total_transaction_hostory ,
pe.enc_id,
pe.pat_resp_date,
vc.person_id,
vc.invoice_desc_1,
vc.invoice_desc_2
from total_charge vc
inner join invoices iv on vc.practice_id = iv.practice_id
and vc.source_id = iv.invoice_id
inner join accounts ac on iv.practice_id = ac.practice_id
and iv.acct_id = ac.acct_id
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 patient_encounter pe on vc.practice_id = pe.practice_id
and vc.source_id = pe.enc_id
WHERE vc.practice_id = '0012'
AND iv.practice_id = '0012'
AND ac.practice_id = '0012'
AND ac.acct_id = '3CE69763-F380-4D2C-8482-245E264AB952'
AND vc.source_type = 'I'
ORDER BY 22, 8, 7
As for the Query itself with out knowing the exact purpose of the query it is a little difficult but I would like at the many sub queries. these embedded wueries can be expensive particularly when you have a where clause that would cause it to do a select on every single row. this will likely result in not just one but multiple table scans.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 1, 2010 at 10:57 am
actually the applications is getting hour glasses and so i ran the profiler and correlated with Performance monitor. and got this query taking time.
This counter reported me the issue---
\\****\SQLServer:Lock request\sec
and the max value it shows there is 912343.
July 1, 2010 at 1:05 pm
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 1:37 pm
Ok i have attached the execution plan.
July 1, 2010 at 1:55 pm
Table definitions?
Index definitions?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2010 at 2:02 am
post index defintion of trans_detail and Charges tables .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 2, 2010 at 8:06 am
please see the attache3d excel file for details
July 2, 2010 at 9:11 am
espanolanthony (7/2/2010)
please see the attache3d excel file for details
Anthony, anybody wishing to get involved with this thread will need to convert your spreadsheet data into statements which can be interpreted by SQL Server. That could take a considerable amount of time, which is almost certainly why nobody has responded since you posted it earlier today.
Take the time to read the link Gail posted...
GilaMonster (7/1/2010)
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
... it will show you how to post the information folks need to get stuck into your problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 2, 2010 at 10:33 am
Chris Morris-439714 (7/2/2010)
Anthony, anybody wishing to get involved with this thread will need to convert your spreadsheet data into statements which can be interpreted by SQL Server. That could take a considerable amount of time, which is almost certainly why nobody has responded since you posted it earlier today.
Is fine as it is, don't need to create the tables for a perf problem (at least I don't). Just haven't had chance to look back here since this morning. Maybe tomorrow.
Take the time to read the link Gail posted...
:blush:
Include all relevant DDL. Post the definitions of the tables that the queries use along with any indexes and constraints that exist on the table. This can be the create table and create index statements, or just the output of sp_help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2010 at 11:53 pm
Maybe you may want to run the next sql code alone to see how long it takes.
SELECT SUM(ISNULL(td.paid_amt,0)) + SUM(ISNULL(td.adj_amt,0))
from transaction_hostory td , total_charge vc
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
I think this is where is getting a little slow, but is hard to say. If you are using Sql server 2005 or 2008 you can create covering indexes using the INCLUDE option, that way you can avoid the key lookups.
Check if you have an index that uses pe.practice_id, pe.billable_ind ,pe.enc_status on patient_encounter.
The next code is really unnecessary. You are already doing an inner join, so you really need only one line of code. This should not help your performance, just to let you know.
WHERE vc.practice_id = '0012'
AND pe.practice_id = '0012'
AND ac.practice_id = '0012'
July 7, 2010 at 8:02 am
still no comments. 17 views
July 7, 2010 at 8:07 am
espanolanthony (7/7/2010)
still no comments. 17 views
Plenty of comments!
What have you tried so far?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply