We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Need help to Optimize this view

Roman Gherman
on
Medium Priority
463 Views
Last Modified: 2012-05-11
Hi SQL Experts,

I need some help in optimizing this SQL query, I need it to run faster.

I have already run out of ideas.
I am also a Developer and I know SQL pretty well, but maybe I have missed something that I could optimize here.

Note:
1) I have added all necessary indexes
2) I know that only needed columns should be selected, but I just added select all columns in the example bellow because I have a lot of columns there.
3) I have used "Include Actual Execution Plan".

I don't know what to do else. Please help.

Here is the code:

SELECT *
FROM 
	appointment AS APP
INNER JOIN 
	history AS HSTRY 
	ON HSTRY.id = APP.hc_srvc_intndd_dlvry_id
	AND HSTRY.hstry_type_id = 2
	AND APP.hc_srvc_prd_id IS NULL
WHERE
	(HSTRY.chng_dscrptn_id = 21
	OR 
	(HSTRY.chng_dscrptn_id = 18
	 AND
		(HSTRY.rsn_fr_chng_id <> 999
			AND
			NOT EXISTS (SELECT 1
						FROM history AS H
						WHERE
							id = HSTRY.id
						AND hstry_type_id = 2
						AND chng_dscrptn_id = 21 
						AND status_time BETWEEN DATEADD(minute,-1,HSTRY.status_time) AND DATEADD(minute,1,HSTRY.status_time)))
		)
	)

Open in new window

     
Comment
Watch Question

Replace the first WHERE condition with "AND"
Roman GhermanSenior Software Engineer

Author

Commented:
Why do you think that it would work faster?

Do you have an explanation or its just a guess?

I will verify that tomorrow, because I don't have access to that DB now.

Thanks for your reply.

Regards,
Roman
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Commented:
Please post your execution plan...
CERTIFIED EXPERT

Commented:
Are there any table scans in your execution plan which would indicate that indexing the offending item(s) may be appropriate.
Roman GhermanSenior Software Engineer

Author

Commented:
Here is the execution plan: Execution Plan

@radcaesar - There is no change in execution plan when replacing where with AND.

I don't see a need of any other indexes.

I am thinking if an optimization can be done on this line:
AND status_time BETWEEN DATEADD(minute, -1, HSTRY.status_time) AND DATEADD(minute, 1, HSTRY.status_time)

Open in new window



Thanks for reply.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>I don't see a need of any other indexes.
possibly getting existing indexes added more columns to get them a covering index.

however, looking at the "not exists", this sounds like a candidate for a denormalized column in appointment table.
in short, make the column in appointment to be 1 or 0 based on the fact if in there is a row in history matching those condition(s) , kept up-to-date with triggers ....
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Roman GhermanSenior Software Engineer

Author

Commented:
@angelIII thanks for replying, however all records from appointment table has a record in history table, but I need to select only those which corresponds to the where condition. Which means that it is not a good idea to add a new column to appointment table.

Also I would not like to change the structure of the tables as these are very old tables with a lot of data.

Regards,
Roman
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
SELECT *
FROM
        appointment AS APP
INNER JOIN
        history AS HSTRY
        ON HSTRY.id = APP.hc_srvc_intndd_dlvry_id
        AND HSTRY.hstry_type_id = 2
WHERE
            APP.hc_srvc_prd_id IS NULL AND
        (HSTRY.chng_dscrptn_id = 21
        OR
        HSTRY.chng_dscrptn_id = 18)
         AND
                (HSTRY.rsn_fr_chng_id <> 999
                        AND
                        NOT EXISTS (SELECT 1
                                                FROM history AS H
                                                WHERE
                                                        id = HSTRY.id
                                                AND hstry_type_id = 2
                                                AND chng_dscrptn_id = 21
                                                AND status_time BETWEEN DATEADD(minute,-1,HSTRY.status_time) AND DATEADD(minute,1,HSTRY.status_time)))
               
       
Roman GhermanSenior Software Engineer

Author

Commented:
@PatelAlpesh Why do you think that moving  "APP.hc_srvc_prd_id IS NULL" in where clause would increase the speed?

@Tony303 yes, thats a small optimisation, thanks


I'll wait some more hours to see if there are any other solutions and if not, I will select the person who helped me more. Thanks
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Roman GhermanSenior Software Engineer

Author

Commented:
Thanks to all of you for your help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.