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.
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;
Also, instead of doing a SELECT DISTINCT, please try and rewrite to use a GROUP BY instead
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.