How to use temp table in my query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to use temp table in my query

Author

Topic

amy2502
Starting Member

17 Posts

Posted - 2014-09-03 : 13:00:34

my version:

DECLARE @MyTempTable table ( firstname varchar(20),siteid varchar(20) , productcatagory varchar(50),
SiteNetSales float,SiteQtyCase float,TicketDueDate date,RouteNetSales float,RouteSalesPercent float )
with cte1
as (select Distinct siteid
,productcatagory

,sum(NETSALES) as SiteNetSales
,sum(QtyCase) as SiteQtyCase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by siteid
,productcatagory

)
,cte2
as (select Distinct productcatagory
,mitrouteid
,sum(isnull(netsales,0)) as NetSales
,sum(isnull(qtycase,0)) as QtyCase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()and MITRouteID='4301'
group by productcatagory
,mitrouteid

)
,cte3
as (select MITRouteID
,sum(netsales) as RouteNetSales
,sum(qtycase) as QtyCase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()and MITRouteID='4301'
group by MITRouteID
)

select Distinct d.firstname
,a.siteid
,a.productcatagory as ProductCategory

,a.SiteNetSales
,a.SiteQtyCase
,d.ticketduedate as TicketDueDate

,c.RouteNetSales
,case
when b.netsales=0
then 0
else b.netsales*100/c.routenetsales
end as RouteSalesPercent
INTO @MyTempTable
from cte1 as a
inner join dbo.vw_OpenDeliveryTickets as d
on d.siteid=a.siteid
and d.TicketDueDate>=dateadd(dd,2,getdate())
and d.TicketDueDate<dateadd(dd,10,getdate())
inner join cte2 as b
on b.ProductCatagory=a.ProductCatagory
inner join cte3 as c
on c.MITRouteID=b.MITRouteID
where d.FirstName='route4301'

Drop @MyTempTable

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-03 : 13:52:41

Your query is using a table variable, not a temp table. They are similar but different. Temp tables start with # in the name.

Why do you want to use a temp table or a table variable for this? What are you trying to achieve? We need more info...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

amy2502
Starting Member

17 Posts

Posted - 2014-09-03 : 13:57:36

When I run the below query without the hard coding for MITROUTEID , the query takes more than an hr to execute. So I thought including a temp table could help me execute it without disturbing performance of other systems in my dept. Please let me know if you have any other work around.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-03 : 15:24:24

"When I run the below query"--> what are you referring to?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

amy2502
Starting Member

17 Posts

Posted - 2014-09-03 : 15:36:24

Referring to this query :

with cte1
as (select Distinct siteid
,productcatagory

,sum(NETSALES) as SiteNetSales
,sum(QtyCase) as SiteQtyCase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by siteid
,productcatagory

)
,cte2
as (select Distinct productcatagory
,mitrouteid
,sum(isnull(netsales,0)) as NetSales
,sum(isnull(qtycase,0)) as QtyCase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by productcatagory
,mitrouteid

)
,cte3
as (select MITRouteID
,sum(netsales) as RouteNetSales
,sum(qtycase) as QtyCase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by MITRouteID
)

select Distinct d.firstname
,a.siteid
,a.productcatagory as ProductCategory

,a.SiteNetSales
,a.SiteQtyCase
,d.ticketduedate as TicketDueDate

,c.RouteNetSales
,case
when b.netsales=0
then 0
else b.netsales*100/c.routenetsales
end as RouteSalesPercent

from cte1 as a
inner join dbo.vw_OpenDeliveryTickets as d
on d.siteid=a.siteid
and d.TicketDueDate>=dateadd(dd,2,getdate())
and d.TicketDueDate<dateadd(dd,10,getdate())
inner join cte2 as b
on b.ProductCatagory=a.ProductCatagory
inner join cte3 as c
on c.MITRouteID=b.MITRouteID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-03 : 15:41:53

Show us the execution plan of it. Also show us the statistics io and statistics time.

For stats io/time, add this to your query in Management Studio and then send us the output (not the data returned):
SET STATISTICS IO ON
SET STATISTICS TIME ON

Your query goes here.

I would bet you are missing indexes.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-04 : 01:11:22

You are reading the vv_sv_invoicedetail table 3 times. I bet is't faster to read once and then sum the sums - something like:

with cte0
 as (select siteid
           ,productcatagory
           ,mitrouteid
           ,sum(NETSALES) as SiteNetSales
           ,sum(QtyCase) as SiteQtyCase
       from dbo.vw_sv_invoicedetail
      where invoicedate>=dateadd(dd,-90,getdate())
        and invoicedate<getdate()
      group by siteid
              ,productcatagory
              ,mitrouteid
 )
 ,cte1
 as (select siteid
           ,productcatagory
           ,sum(NETSALES) as SiteNetSales
           ,sum(QtyCase) as SiteQtyCase
       from cte0
      group by siteid
              ,productcatagory
 )
 ,cte2
 as (select productcatagory
           ,mitrouteid
           ,sum(isnull(netsales,0)) as NetSales
           ,sum(isnull(qtycase,0)) as QtyCase
       from cte0
      group by productcatagory
              ,mitrouteid

 )
 ,cte3
 as (select MITRouteID
           ,sum(netsales) as RouteNetSales
           ,sum(qtycase) as QtyCase
       from cte2
      group by MITRouteID 
 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources