[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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

     
0
Roman Gherman
Asked:
Roman Gherman
2 Solutions
 
radcaesarCommented:
Replace the first WHERE condition with "AND"
0
 
Roman GhermanAuthor 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
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Please post your execution plan...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Tony303Commented:
Are there any table scans in your execution plan which would indicate that indexing the offending item(s) may be appropriate.
0
 
Roman GhermanAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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 ....
0
 
Tony303Commented:
you could change

from
AND status_time BETWEEN DATEADD(minute, -1, HSTRY.status_time) AND DATEADD(minute, 1, HSTRY.status_time)

to
AND status_time >= DATEADD(minute, -1, HSTRY.status_time) AND status_time <= DATEADD(minute, 1, HSTRY.status_time)
0
 
Roman GhermanAuthor 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
0
 
Alpesh PatelAssistant ConsultantCommented:
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)))
               
       
0
 
Roman GhermanAuthor 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
0
 
Maxi84Commented:
You have a few key lookups.  Look at which columns are returned from the key lookups, and consider INCLUDE - ing these columns in your index.  Esp the key lookup on [history] seems to be very expensive.  Consider including the columns in the history.ucl_h index.
0
 
Roman GhermanAuthor Commented:
Thanks to all of you for your help.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now