SQL QUERY TUNING

Hi Experts,

I need some help my query to run faster. I knew that I have used a lot of temp tables. I do not know if there is any way to shorten my query and improve the running time. Please find attached result table which is what I want to keep it as.
if object_id('tempdb..#leasingdealers')is not null 
drop table #leasingdealers
if object_id('tempdb..#leasecompare1')is not null 
drop table #leasecompare1
if object_id('tempdb..#leasecompare2')is not null 
drop table #leasecompare2
if object_id('tempdb..#financecompare1')is not null 
drop table #financecompare1
if object_id('tempdb..#financecompare2')is not null 
drop table #financecompare2
if object_id('tempdb..#deals')is not null 
drop table #deals;



----------------------leasingdealers--------------

with cte as (select distinct dv.dealerid,dv.dealershipname
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 do.offertype='1'
and cast(doi.timestamp as date)>=@StartDate and cast(doi.timestamp as date)<=@EndDate
and dv.dealerid in (select dealerid from mmd_feed..dart))

select cte.dealerid,cte.dealershipname,sum(ad.[WidgetInteraction]) as Pencils,
sum(ad.[WidgetLoad]) as Impressions
into #leasingdealers
from cte cte
inner join MakeMyDeal_com.[dbo].[adobe_Standard] ad on cte. dealerid=ad.dealerid
where cast(ad.date as date) between @StartDate and @EndDate
group by cte.dealerid, cte.dealershipname

---------------lease compare----------------------

select dv.dealid
,do.dealofferid, dv.dealerid, dv.dealershipname,  ds.[seqnum],
do.offertype,dv.submittedtimestamp 
into #leasecompare1 
from mmd_feed..DealresponseSequence ds
inner join mmd_feed..dealview dv on ds.dealid=dv.dealid
inner join mmd_feed..DealResponse  doi on doi.dealid=dv.dealid
inner join makemydeal_com..dealoffer do on do. dealofferid=doi.dealofferid
where ds.[seqnum]>'1' 
and do.offertype='1'
and dv.alternatedeal='No' and dv.manualdeal='No'
and dv.dealstatus='sent'
and cast(doi.timestamp as date) between @StartDate and @EndDate


select count (distinct dealid) as leasecompare, dealerid 
into #leasecompare2
from #leasecompare1
where dealid not in 
(select  d.dealid
from makemydeal_com..dealoffer do
inner join mmd_feed..DealresponseSequence ds on do. dealofferid=ds.dealofferid
inner join mmd_feed..Deal d on d.dealid=ds.dealid
inner join mmd_feed..dealview dv on dv.dealid=ds.dealid
inner join mmd_feed.[dbo].[DealResponseMinAndMaxShopper] dms on dms.dealid=d.dealid and dms.FirstMessage=ds.DealResponseID
where do.offertype=1 
)
group by dealerid

-------------------------Finance Compare----------------------

select dv.dealid
,do.dealofferid, dv.dealerid, dv.dealershipname,ds.seqnum,
do.offertype,dv.submittedtimestamp 
into #financecompare1 
from mmd_feed..DealresponseSequence ds
inner join mmd_feed..dealview dv on ds.dealid=dv.dealid
inner join mmd_feed..DealResponse  doi on doi.dealid=dv.dealid
inner join makemydeal_com..dealoffer do on do. dealofferid=doi.dealofferid
inner join mmd_feed..Deal d on d.dealid=dv.dealid
where ds.seqnum>1 
and do.offertype=0
and  d.VehicleStatus ='New'
and dv.AlternateDeal='No' and dv.ManualDeal='No'
and dv.dealstatus='Sent'
and cast(doi.timestamp as date) between @StartDate and @EndDate

select count(distinct dealid) as financecompare, dealerid 
into #financecompare2
from #financecompare1
where dealid not in 
(select  d.dealid
from makemydeal_com..dealoffer do
inner join mmd_feed..DealresponseSequence ds on do. dealofferid=ds.dealofferid
inner join mmd_feed..Deal d on d.dealid=ds.dealid
inner join mmd_feed..dealview dv on dv.dealid=ds.dealid
inner join mmd_feed.[dbo].[DealResponseMinAndMaxShopper] dms on dms.dealid=d.dealid and dms.FirstMessage=ds.DealResponseID
where do.offertype=0
and d.VehicleStatus='New'
)
Group by dealerid

----------------------------deals------------------------------
select dv.dealershipname,
	d.[dealerid],
	(select min(date)from [MMD_Feed].[dbo].[DealsPerDayHistoricalByType] t where t.dealerid = d.dealerid and t.leasingactive = 'Y') as activatedate,
	sum (case when do.offertype= 1 then 1 else 0 end) as TotalLease,
	sum (case when do.offertype=0 and d.vehiclestatus = 'New' then 1 else 0 end ) as NewTotalFinanceDeals,
	Sum(case when dv.[ArchiveDealStatus]= 'CustomerSold' and d.vehiclestatus = 'New' then 1 else 0 end) as 'SoldArchived'
into #deals
from mmd_feed..Deal d
	 inner join mmd_feed..DealResponse  doi on d.DealID=doi.dealid
	 inner join makemydeal_com..dealoffer do on doi.dealofferid=do.dealofferid
	 inner join mmd_feed..dealview dv on dv.dealid=d.dealid
	 inner join mmd_feed..dealer de on de.dealerid=d.dealerid
	 inner join mmd_feed.[dbo].[DealResponseMinAndMaxShopper] dms on dms.dealid=d.dealid and dms.FirstMessage=doi.DealResponseID
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'
 group by d.[dealerid],dv.dealershipname 

---------------------Table join------------

SELECT  ld.dealershipname ,
        ld.dealerid ,
        impressions ,
        pencils ,
		activatedate,
        ISNULL(leasecompare, 0) AS Leasecompare ,
        ISNULL(totallease, 0) as totallease,
        ISNULL(newtotalfinancedeals, 0) as totalfinancenew ,
        ISNULL(financecompare, 0) AS Financecompare ,
        ISNULL(soldarchived, 0) as soldarchived ,
        CASE WHEN ISNULL(totallease, 0) = 0 THEN 0
             ELSE CAST (totallease AS FLOAT) / ( CASE WHEN ISNULL(totallease, 0) = 0 THEN 0
                                                      ELSE CAST (totallease AS FLOAT)
                                                 END + CASE WHEN ISNULL(newtotalfinancedeals, 0) = 0 THEN 0
                                                            ELSE CAST (newtotalfinancedeals AS FLOAT)
                                                       END )
        END AS Leasepercentage ,
        CASE WHEN ISNULL(financecompare, 0) = 0 THEN 0
             ELSE CAST(financecompare AS FLOAT) / CAST(totallease AS FLOAT)
        END AS Leaseswitch
FROM    #leasingdealers ld
        LEFT JOIN #leasecompare2 lc ON ld.dealerid = lc.dealerid
        LEFT JOIN #financecompare2 fc ON fc.dealerid = lc.dealerid
        LEFT JOIN #deals d ON ld.dealerid = d.dealerid
ORDER BY ld.dealershipname ASC; 

Open in new window


Capture.JPG
tanj1035Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Vitor MontalvãoMSSQL Senior EngineerCommented:
That's very hard to answer.
How much time is taking now?
Can you know the execution time for each SELECT?
The only thing that I can say only by the code is to avoid the use of CAST since if the fields are indexed then it won't use the index to perform the query.
Barry CunneyCommented:
Also, instead of doing a SELECT DISTINCT, please try and rewrite to use a GROUP BY instead
Scott PletcherSenior DBACommented:
Never use a function on a column if you can avoid it.  Therefore, change WHERE conditions to method below instead.

Also, research clustering the DealResponse table on timestamp first.

...
where do.offertype='1'
and doi.timestamp >= @StartDate
and doi.timestamp < dateadd(day, 1, @EndDate)
...

...
where ad.date >= @StartDate and ad.date < dateadd(day, 1, @EndDate)
...

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
ste5anSenior DeveloperCommented:
hmm, haven't we already met?

Use always fully qualified object names. Thus don't omit the schema name. Always use a table alias when using column names.

I already wrote, how you can shorten your statement. I also told you, that your CASE uses the wrong condition to prevent a division by zero error.
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.