SQL, how to improve the query running time?

Hi Experts, my query runs for 4 mins. Is there any way to improve the running time? Thanks. Please see my result table below.
Thanks,




 
declare @StartDate date
declare @EndDate date
set @StartDate ='2015-06-01'
set @EndDate='2015-06-30'

select 
sum (case when do.offertype=0 and d.VehicleStatus ='New' then 1 else 0 end) as TotalfinanceDeals,
sum (case when do.offertype=1 then 1 else 0 end) as TotalLeaseDeals, 
sum(case when do.offertype=1 and dv.ArchiveDealStatus = 'CustomerSold'then 1 else 0 end )as TotalSoldLeaseArchived,
sum(case when do.offertype=0 and dv.ArchiveDealStatus = 'CustomerSold' and d.vehiclestatus = 'New' then 1 else 0 end )as TotalSoldfinanceArchived

from  mmd_feed..Deal d
inner join mmd_feed..DealResponse  doi on d.dealid=doi.dealid
inner join makemydeal_com..dealoffer do on do. dealofferid=doi.dealofferid
inner join mmd_feed.[dbo].[DealResponseMinAndMaxShopper] dms on dms.dealid=doi.dealid and dms.FirstMessage=doi.DealResponseID
inner join mmd_feed..dealview dv on doi.dealid=dv.dealid
where
cast(doi.timestamp as date)>=@StartDate and cast(doi.timestamp as date)<=@EndDate
and dv.alternatedeal='No' and dv.manualdeal='No'
and dv.dealstatus='sent'
and dv.dealerid in 
(select distinct dv.dealerid
from makemydeal_com..dealoffer do
inner join mmd_feed..DealResponse  doi  on do. dealofferid=doi.dealofferid
inner join mmd_feed..dealview dv on doi.dealid=dv.dealid
where offertype='1'
and cast(doi.timestamp as date)>=@StartDate and cast(doi.timestamp as date)<=@EndDate
group by dv.dealerid)

Open in new window

Capture.JPG
tanj1035Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Please post your execution plan and any relevant indexes you have on these tables.  Your solution is going to be reached by making sure the correct indexes are in place.
0
tanj1035Author Commented:
Thanks for your reply. Can you give me an example of execution plan and relevant indexes to help me to understand? Sorry, I am new to sql, so I am not able to catch what experts recommend sometimes. Thanks.
0
ste5anSenior DeveloperCommented:
Your CAST(doi.[timestamp] AS DATE) should result in a non-sargeable predicate, thus no index could be used for evaluating this. I

Use:
/* 
INSTEAD OF THIS ::

DECLARE @StartDate DATE = '20150606';
DECLARE @EndDate DATE = '20150821';

SELECT  *
FROM    #Test
WHERE   CAST([timestamp] AS DATE) >= @StartDate
        AND CAST([timestamp] AS DATE) <= @EndDate;

USE THIS:
*/

DECLARE @StartDate DATE = '20150606';
DECLARE @EndDate DATE = '20150822';

SELECT  *
FROM    #Test
WHERE   [timestamp] >= @StartDate
        AND [timestamp]  < @EndDate;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Perform this action before executing the query and after execution save the query plan and post it here.
IncludeExecutionPlan.PNG
0
ste5anSenior DeveloperCommented:
The following indices should exist

mmd_feed..dealview ( alternatedeal, manualdeal , dealstatus , dealerid, dealid);
mmd_feed.dbo.DealResponseMinAndMaxShopper ( dealid , FirstMessage );
makemydeal_com..dealoffer (  dealofferid, offertype );
mmd_feed..DealResponse ( dealid, dealofferid, DealResponseID, [timestamp] );
mmd_feed..Deal ( dealid, VehicleStatus );

Column order may vary.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.