Link to home
Start Free TrialLog in
Avatar of tanj1035
tanj1035

asked on

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


User generated image
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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.
Also, instead of doing a SELECT DISTINCT, please try and rewrite to use a GROUP BY instead
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.