asked on

Need help to Optimize this view

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

radcaesar

Replace the first WHERE condition with "AND"

Roman Gherman

ASKER

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 BOUDJAKDJI

Please post your execution plan...

Tony303

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

Roman Gherman

ASKER

Here is the 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]

>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 ....

ASKER CERTIFIED SOLUTION

Tony303

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roman Gherman

ASKER

@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 Patel

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 Gherman

ASKER

@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

SOLUTION

Maxi84

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roman Gherman

ASKER

Thanks to all of you for your help.