Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Need index recommendation

Avatar of espanolanthony
espanolanthonyFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft Applications
5 Comments1 Solution331 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Avatar of CapitanHarlock
CapitanHarlockFlag of Spain image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers