Need index recommendation

espanolanthony
espanolanthony used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
A easy way to optimize this query is  running Database engine tunning advisor
copy this query in a SQL Server query screen and push this button in toolbox. It defines indexes and Statistics

Author

Commented:
but how do i just analyse only  this query. when i go to tools-- DTA then i connect to the respective server and select the database. and when i press the Start Analyse button it does nothing. I see the workload file location empty.
but how to optimise just the above query in the DTA. I haven'e seen that before
Top Expert 2012

Commented:
At the very least make these changes (there are probably a half a dozen other changes you should also make):
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),
		sim.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,
        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,
        ISNULL(td.total_transaction_hostory, 0) 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
        INNER JOIN (
				SELECT	TOP 1
						description
				FROM   services 
				-- ORDER BY ??? 
				) sim ON vc.service_item_lib_id = sim.service_item_lib_id
						AND vc.service_item_id = sim.service_item_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 (
				SELECT  practice_id,
						charge_id,
						source_id,
						SUM(ISNULL(paid_amt, 0)) + SUM(ISNULL(adj_amt, 0)) total_transaction_hostory
                FROM    transaction_hostory
                WHERE   trans_id NOT IN ('3D476601-C585-44E3-8D49-7AE4FD7B890D', 'D8EFF527-CE55-4DC5-81EF-C29802647D8A')
                GROUP BY practice_id,
						charge_id,
						source_id) td ON td.practice_id = vc.practice_id
										AND td.charge_id = vc.charge_id
										AND td.source_id = vc.source_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			-- Consider whether you cannot use UNION ALL instead of 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),
		sim.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,
        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,
        ISNULL(td.total_transaction_hostory, 0) 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
        INNER JOIN (
				SELECT	TOP 1
						description
				FROM   services 
				-- ORDER BY ??? 
				) sim ON vc.service_item_lib_id = sim.service_item_lib_id
						AND vc.service_item_id = sim.service_item_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
		LEFT JOIN (
				SELECT  practice_id,
						charge_id,
						source_id,
						SUM(ISNULL(paid_amt, 0)) + SUM(ISNULL(adj_amt, 0)) total_transaction_hostory
                FROM    transaction_hostory
                WHERE   trans_id NOT IN ('3D476601-C585-44E3-8D49-7AE4FD7B890D', 'D8EFF527-CE55-4DC5-81EF-C29802647D8A')
                GROUP BY practice_id,
						charge_id,
						source_id) td ON td.practice_id = vc.practice_id
										AND td.charge_id = vc.charge_id
										AND td.source_id = vc.source_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 

Open in new window

Hi

To run DTA you must copy the query in a SQL Query in SS Management Studio. Then press the button DTA in toolbar (see DTA.jpg)
DTA will we open like DTA2.jpg
DTA.JPG
DTA2.JPG

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial