November 3, 2009 at 12:44 pm
:-D:-D I've tried the DTW (Database Tuning Wizard) and it has no recommendations for tuning this query and query plans show low percetages on cost. It's a pretty ugly query. Any suggestions or help would be greatly appreciated. Thanks in advance and beware below :-D:
/****** Object: StoredProcedure [dbo].[Report_SpendPerHeadDetail] Script Date: 11/03/2009 13:23:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[Report_SpendPerHeadDetail]
@MerchantID int,
@StockOwnerID int = 87, -- Need to be modified later
@Start datetime,
@End datetime,
@EPOSManual varchar(10),
@Unit varchar(max) = '-1'
as
---- For Debugging Begin --
--declare @MerchantID int
--declare @StockOwnerID int
--declare @Start datetime
--declare @End datetime
--declare @EPOSManual varchar(10)
--declare @Unit varchar(max)
--
--set @MerchantID = 89
--set @StockOwnerID = 87
--set @Start = '05/01/2009'
--set @End = '05/10/2009'
--set @EPOSManual = 'EPOS'
--set @Unit = '-1'
---- For Debugging End --
create table #temp (Country varchar(50), CountryID int, AirportCode varchar(10), AirportName varchar(50), Number decimal(18,2), DataType nvarchar(max), I int, i1 int identity)
create table #temp1 (i int identity, Airport varchar(10), AirportName varchar(50))
create table #Unit (AirportID int)
Create table #tempCategory (AirportID int, AirportCode varchar(10), AirportName varchar(50), CountryID int, Country varchar(50),
NetSales decimal(18,2), Pax int, CategoryID int, CategoryName varchar(50), BrandName varchar(50),ID int)
Create table #tempCat(AirportID int,AirportCode varchar(10),AirportName varchar(50),CountryID int,Country varchar(50),CategoryID int,CategoryName varchar(50),NetSales decimal(18,2),Pax int,SpendPerHead decimal(18,8))
--Recursive table
create table #tempRecursive (CategoryID int,CategoryName varchar(50), ID int, [Level] int)
--Tilting table
create table #tempC (Country varchar(50), CountryID int,AirportCode varchar(10),AirportName varchar(50),CategoryName varchar(50),BrandName varchar(50),Number decimal(18,8),DataType nvarchar(max),I int,i1 int identity)
declare @sql nvarchar(max), @sql1 nvarchar(max)
declare @i int, @j-2 int
set @i = 1
--This is to find out if any Warehouse has been selected.
if @Unit = '-1'
begin
insert into #Unit select AirportID from Base
end
--This is to strip the Unit's
else
begin
while charindex(',', @unit) > 0
begin
insert into #Unit values (convert(int, substring(@Unit, 1, charindex(',', @unit)-1)))
set @Unit = substring(@Unit, charindex(',', @unit)+1, len(@Unit) - charindex(',', @unit))
end
insert into #Unit select(convert(int, @Unit))
end
--This is the 1st Part
select A.*
into #temp2
from
(select distinct fr.originatingairportid as AirportID, a.AirportCode, a.AirportName, c.CountryId, c.Name as COuntry,
convert(decimal(18,2), GS.GrossSales - D.Discount) as [Net Sales],
(GS.EPOSSales - D.EPOSDiscount) as EPOSNetSales,
convert(decimal(18,2), UnitsSold) as [Units Sold],
convert(decimal(18,2), NoOfEPOSTransactions) as [No. Of EPOS Transactions],
convert(decimal(18,2), pax) as PAX,
case when Pax = 0 then 0 else convert(decimal(18,2),(GS.GrossSales - D.Discount)/pax) end as [Actual SPH],
convert(decimal(18,2), 0) as [Forecast SPH],
convert(decimal(18,2), 0) as [Crew Target SPH],
convert(decimal(18,2), 0) as [Variance (Act Vs Crew)],
convert(decimal(18,2), 0) as [Variance (Act Vs ForeCast)],
case when NoOfEPOSTransactions = 0 then 0 else convert(decimal(18,2), (GS.EPOSSales - D.EPOSDiscount)/NoOfEPOSTransactions) end as [Average Transaction Value EPOS],
case when NoOfEPOSTransactions = 0 then 0 else convert(decimal(18,2), convert(decimal(18,2), EPOSUnitsSold)/convert(decimal(18,2), NoOfEPOSTransactions)) end as [Average Number Of Items Per Transaction EPOS],
EPOSUnitsSold
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID
join Airport a with (nolock) on a.AirportID=fr.OriginatingAirportID
join Country c with (nolock) on c.CountryID=a.AirportCountry
join StockOwnerAirline soa with (nolock) on soa.AirlineID=fr.AirlineID
--Gross Sales and Units Sold
join
(select distinct fr.OriginatingAirportID,
case when EPOSSales is null then 0 else EPOSSales end +
case when ManualSales is null then 0 else ManualSales end +
case when VirtualProductSales is null then 0 else VirtualProductSales end as GrossSales,
case when EPOSUnitsSold is null then 0 else EPOSUnitsSold end +
case when ManualUnitsSold is null then 0 else ManualUnitsSold end +
case when VirtualUnitsSold is null then 0 else VirtualUnitsSold end as UnitsSold,
NoOfEPOSTransactions,
case when EPOSSales is null then 0 else EPOSSales end as EPOSSales,
case when EPOSUnitsSold is null then 0 else EPOSUnitsSold end as EPOSUnitsSold
from
BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID
--Manual Sales--
left outer join
(select sum(case when soa.Virtual=0 then (wc.StartCount-wc.EndCount)*aap.BasePrice else 0 end) as ManualSales,
sum(case when soa.Virtual=0 then (wc.StartCount-wc.EndCount) else 0 end)as ManualunitsSold,fr.OriginatingAirportID
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join WarehouseCount wc with (nolock) on bi.BarsetInstanceID=wc.BarsetInstanceID
join AirlineArticle aa with (nolock) on aa.AirlineArticleID=wc.AirlineArticleID
join StockOwnerArticle soa with (nolock) on soa.StockOwnerArticleID=aa.StockOwnerArticleID
join AirlineArticlePrice aap with (nolock) on aa.AirlineArticleID=aap.AirlineArticleID
and aap.StartDate <= bi.FlightDate
and aap.EndDate >= bi.FlightDate
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('Manual','All')
and bi.IsManual = 1
group by fr.OriginatingAirportID
) A
on fr.OriginatingAirportID=A.OriginatingAirportID
--Manual Sales End--
--Virtual Product Sales--
left outer join
(select sum(mvp.qty * aap.BasePrice) as VirtualProductSales,
sum(mvp.qty)as VirtualUnitsSold,fr.OriginatingAirportID
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID
join ManualVirtualProduct mvp with (nolock) on cb.CashBagID=mvp.CashBagID
join AirlineArticle aa with (nolock) on aa.AirlineArticleID=mvp.AirlineArticleID
join AirlineArticlePrice aap with (nolock) on aa.AirlineArticleID=aap.AirlineArticleID
and aap.StartDate<=bi.FlightDate
and aap.EndDate>=bi.FlightDate
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('Manual','All')
group by fr.OriginatingAirportID
) C
on fr.OriginatingAirportID=C.OriginatingAirportID
--Virtual Product Sales--
--Auto Sales--
left outer join
(select sum(TotalAmount) as EPOSSales,
sum(Qty) as EPOSUnitsSold,
fr.OriginatingAirportID,
count(distinct t.transactionid) as NoOfEPOSTransactions
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join FlightBarset fb with (nolock) on bi.BarsetInstanceID=fb.BarsetInstanceID
join [Transaction] t with (nolock) on fb.FlightInstanceID=t.FlightInstanceID
join TransactionType tt with (nolock) on tt.TypeID=t.TypeID
join TransactionArticle ta with (nolock) on t.TransactionID=ta.TransactionID
join AirlineArticle aa with (nolock) on aa.AirlineArticleID=ta.AirlineArticleID
join StockOwnerArticle soa with (nolock) on soa.StockOwnerArticleID=aa.StockOwnerArticleID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('EPOS','All')
and tt.TypeName='SALE'
and t.Voided=0
and bi.IsManual=0
group by fr.OriginatingAirportID
) E
on fr.OriginatingAirportID=E.OriginatingAirportID
--Auto Sales--
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
) GS
on fr.OriginatingAirportID=GS.OriginatingAirportID
--Gross Sales End--
--Discount --
join
(select distinct fr.OriginatingAirportID,
case when A.EPOSDiscount is null then 0 else A.EPOSDiscount end +
case when B.ManualDiscount is null then 0 else B.ManualDiscount end as Discount,
case when A.EPOSDiscount is null then 0 else A.EPOSDiscount end as EPOSDiscount
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID
--Auto Discount --
left outer join
(select sum(case when pt.PromotionTypeName='General Promotion' then p.Discount end) as EPOSDiscount,
fr.OriginatingAirportID
from [Transaction] t with (nolock)
join TransactionType tt with (nolock) on tt.TypeID=t.TypeID
join TransactionPromotion tc with (nolock) on t.TransactionID=tc.TransactionID
join Promotion p with (nolock) on p.PromotionID=tc.PromotionID
join PromotionType pt with (nolock) on pt.PromotionTypeID=p.PromotionTypeID
join flightbarset fb with (nolock) on fb.flightinstanceid = t.flightinstanceid
join barsetinstance bi with (nolock) on bi.barsetinstanceid = fb.barsetinstanceid
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('EPOS','All')
and p.AirlineID = @MerchantID
and tt.TypeName='SALE'
and t.Voided=0
and bi.IsManual=0
group by fr.OriginatingAirportID
) A
on fr.OriginatingAirportID=A.OriginatingAirportID
--Auto Discount End--
--Manual Discount --
left outer join
(select distinct sum(case when pt.PromotionTypeName='General Promotion' then p.Discount*mp.Qty end) as ManualDiscount,
fr.OriginatingAirportID
from barsetinstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID
and bi.FlightDate >= @Start
and bi.FlightDate < @End+1
join ManualPromotion mp with (nolock) on cb.CashBagID = mp.CashBagID
join Promotion p with (nolock) on p.PromotionID=mp.PromotionID
join PromotionType pt with (nolock) on pt.PromotionTypeID=p.PromotionTypeID
and p.StartDate<=bi.FlightDate
and p.EndDate>=bi.FlightDate
where bi.IsManual=1
and @EPOSManual in ('Manual','All')
group by fr.OriginatingAirportID
) B
on fr.OriginatingAirportID=B.OriginatingAirportID
--Manual Discount End--
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
) D
on fr.OriginatingAirportID=D.OriginatingAirportID
--Discount End--
--Pax Count--
join
(select distinct fr.OriginatingAirportID,
case when EPOSPax is null then 0 else EPOSPax end +
case when ManualPax is null then 0 else ManualPax end as Pax
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID
--Auto Pax Count--
left outer join
(select fr.OriginatingAirportID,
sum(fi.PassengerCount) as EPOSPax
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join FlightBarset fb with (nolock) on bi.BarsetInstanceID=fb.BarsetInstanceID
join FlightInstance fi with (nolock) on fi.FlightInstanceID=fb.FlightInstanceID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('EPOS','All')
and bi.IsManual=0
group by fr.OriginatingAirportID
) A
on fr.OriginatingAirportID=A.OriginatingAirportID
--Auto Pax Count End--
--Manual Pax Count--
left outer join
(select fr.OriginatingAirportID,
sum(mfi.PassengerCount) as ManualPax
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID
join ManualFlightInstance mfi with (nolock) on cb.CashBagID=mfi.CashBagID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('Manual','All')
and bi.IsManual=1
group by fr.OriginatingAirportID
) B
on fr.OriginatingAirportID=B.OriginatingAirportID
--Manual Pax Count End--
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
)P
on fr.OriginatingAirportID=P.OriginatingAirportID
--Pax Count End--
where soa.AirlineID = @MerchantID
and soa.StockOwnerID = @StockOwnerID
and bi.FlightDate >= @Start
and bi.FlightDate < @End+1
) A
--EU
insert into #temp2 (AirportID,AirportCode,AirportName,CountryId,COuntry,
[Net Sales], [Units Sold],[No. Of EPOS Transactions], PAX,[Actual SPH], [Forecast SPH],[Crew Target SPH],
[Variance (Act Vs Crew)], [Variance (Act Vs ForeCast)], [Average Transaction Value EPOS],
[Average Number Of Items Per Transaction EPOS])
select 0,'EU','','','Europe',sum([Net Sales]),sum([Units Sold]),sum([No. Of EPOS Transactions]), sum(PAX),
case when sum([PAX]) = 0 then 0 else sum([Net Sales])/sum([PAX]) end,
0,0,0,0,
case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum([EPOSNetSales])/sum([No. Of EPOS Transactions]) end,
case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum(EPOSUnitsSold)/sum([No. Of EPOS Transactions]) end
from #temp2
where Country not in ('United Kingdom','Switzerland')
--UK
insert into #temp2 (AirportID,AirportCode,AirportName,CountryId,COuntry,
[Net Sales], [Units Sold],[No. Of EPOS Transactions], PAX,[Actual SPH], [Forecast SPH],[Crew Target SPH],
[Variance (Act Vs Crew)], [Variance (Act Vs ForeCast)], [Average Transaction Value EPOS],
[Average Number Of Items Per Transaction EPOS])
select 0,'UK','','',Country,sum([Net Sales]),sum([Units Sold]),sum([No. Of EPOS Transactions]), sum(PAX),
case when sum([PAX]) = 0 then 0 else sum([Net Sales])/sum([PAX]) end,
0,0,0,0,
case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum([EPOSNetSales])/sum([No. Of EPOS Transactions]) end,
case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum(EPOSUnitsSold)/sum([No. Of EPOS Transactions]) end
from #temp2
where Country in ('United Kingdom')
group by COuntry
--Swiss
insert into #temp2 (AirportID,AirportCode,AirportName,CountryId,COuntry,
[Net Sales], [Units Sold],[No. Of EPOS Transactions], PAX,[Actual SPH], [Forecast SPH],[Crew Target SPH],
[Variance (Act Vs Crew)], [Variance (Act Vs ForeCast)], [Average Transaction Value EPOS],
[Average Number Of Items Per Transaction EPOS])
select 0,'SWISS','','',Country,sum([Net Sales]),sum([Units Sold]),sum([No. Of EPOS Transactions]), sum(PAX),
case when sum([PAX]) = 0 then 0 else sum([Net Sales])/sum([PAX]) end,
0,0,0,0,
case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum([EPOSNetSales])/sum([No. Of EPOS Transactions]) end,
case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum(EPOSUnitsSold)/sum([No. Of EPOS Transactions]) end
from #temp2
where Country in ('Switzerland')
group by COuntry
--Total
insert into #temp2 (AirportID,AirportCode,AirportName,CountryId,Country,
[Net Sales], [Units Sold],[No. Of EPOS Transactions], PAX,[Actual SPH], [Forecast SPH],[Crew Target SPH],
[Variance (Act Vs Crew)], [Variance (Act Vs ForeCast)], [Average Transaction Value EPOS],
[Average Number Of Items Per Transaction EPOS])
select 0,'Total','','','',sum([Net Sales]),sum([Units Sold]),sum([No. Of EPOS Transactions]), sum(PAX),
case when sum([PAX]) = 0 then 0 else sum([Net Sales])/sum([PAX]) end,
0,0,0,0,
case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum([EPOSNetSales])/sum([No. Of EPOS Transactions]) end,
case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum(EPOSUnitsSold)/sum([No. Of EPOS Transactions]) end
from #temp2
where AirportCode not in ('EU','UK','SWISS')
--Updating the null values
update #temp2 set [Net Sales]=0 where [Net Sales] is null
update #temp2 set [Units Sold]=0 where [Units Sold] is null
update #temp2 set [No. Of EPOS Transactions]=0 where [No. Of EPOS Transactions] is null
update #temp2 set PAX=0 where PAX is null
update #temp2 set [Actual SPH]=0 where [Actual SPH] is null
update #temp2 set [Forecast SPH]=0 where [Forecast SPH] is null
update #temp2 set [Crew Target SPH]=0 where [Crew Target SPH] is null
update #temp2 set [Variance (Act Vs Crew)]=0 where [Variance (Act Vs Crew)] is null
update #temp2 set [Variance (Act Vs ForeCast)]=0 where [Variance (Act Vs ForeCast)] is null
update #temp2 set [Average Transaction Value EPOS]=0 where [Average Transaction Value EPOS] is null
update #temp2 set [Average Number Of Items Per Transaction EPOS]=0 where [Average Number Of Items Per Transaction EPOS] is null
insert into #temp(Country,CountryID,AirportCode,AirportName,Number,DataType,i)
SELECT Country,CountryID,AirportCode,AirportName,Number,DataType,
case when DataType='Net Sales' then 1
when DataType='Units Sold' then 2
when DataType='No. Of EPOS Transactions' then 3
when DataType='PAX' then 4
when DataType='Actual SPH' then 5
when DataType='Forecast SPH' then 6
when DataType='Crew Target SPH' then 7
when DataType='Variance (Act Vs Crew)' then 8
when DataType='Variance (Act Vs ForeCast)' then 9
when DataType='Average Transaction Value EPOS' then 10
when DataType='Average Number Of Items Per Transaction EPOS' then 11
end as I
from #temp2
UNPIVOT
(Number FOR DataType IN
([Net Sales],
[Units Sold]
, [No. Of EPOS Transactions],[PAX]
, [Actual SPH],[Forecast SPH],[Crew Target SPH],[Variance (Act Vs Crew)],[Variance (Act Vs ForeCast)],
[Average Transaction Value EPOS], [Average Number Of Items Per Transaction EPOS]
)) u
order by countryid desc
--This is the 2nd part--
insert into #tempCategory (AirportID ,AirportCode ,AirportName ,CountryID ,Country ,
NetSales ,Pax ,ID )
select distinct fr.OriginatingAirportID,a.AirportCode,a.AirportName, c.CountryId, c.Name as Country,
GS.grossSales - D.Discount, p.Pax, GS.CategoryID
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join StockOwnerAirline soa with (nolock) on soa.AirlineID=fr.AirlineID
join
--GrossSales--
(select distinct
case when @EPOSManual = 'EPOS' then isnull(A.Sales, 0)
when @EPOSManual = 'Manual' then isnull(B.ManualSales, 0) +
isnull(VirtualProductSales, 0)
when @EPOSManual = 'All' then isnull(A.Sales, 0) +isnull(B.ManualSales, 0) +
isnull(VirtualProductSales, 0) end
as GrossSales,
A.Sales as ESales,
fr.OriginatingAirportID,B.CategoryID,
isnull(B.ManualSales, 0) +
isnull(VirtualProductSales, 0)
as MSales
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID
--Manual Gross Sales--
join
(select sum(case when bi.ismanual=1 then (case when soa.Virtual=0 then (isnull(wc.StartCount, 0)-isnull(wc.EndCount, 0))* isnull(aap.BasePrice, 0) end) end) as ManualSales
, soa.CategoryID, fr.OriginatingAirportID
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join WarehouseCount wc with (nolock) on bi.BarsetInstanceID=wc.BarsetInstanceID
join AirlineArticle aa on aa.AirlineArticleID = wc.AirlineArticleID
join AirlineArticlePrice aap with (nolock) on wc.AirlineArticleID=aap.AirlineArticleID
join StockOwnerArticle soa on aa.StockOwnerArticleID = soa.StockOwnerArticleID
and aap.StartDate<=bi.FlightDate
and aap.EndDate>=bi.FlightDate
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('Manual','All','EPOS')
group by soa.CategoryID, fr.OriginatingAirportID
) B
on fr.OriginatingAirportID=B.OriginatingAirportID
--EPOS Gross Sales--
left outer join
(select sum(TotalAmount) as Sales,
soa.CategoryID, fr.OriginatingAirportID
from transactionarticle ta with (nolock)
join [transaction] tr with (nolock) on tr.transactionid = ta.transactionid
join flightbarset fb with (nolock) on fb.flightinstanceid = tr.flightinstanceid
join barsetinstance bi with (nolock) on bi.barsetinstanceid = fb.barsetinstanceid
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join AirlineArticle aa with (nolock) on aa.AirlineArticleID=ta.AirlineArticleID
join TransactionType tt with (nolock) on tt.TypeID=tr.TypeID
and tr.Voided=0
and tt.TypeName='SALE'
join StockOwnerArticle soa with (nolock) on aa.StockOwnerArticleID = soa.StockOwnerArticleID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and bi.IsManual=0
and @EPOSManual in ('EPOS','All')
group by soa.CategoryID,fr.OriginatingAirportID
) A
on fr.OriginatingAirportID=A.OriginatingAirportID
and B.CategoryID=A.CategoryID
--Manual Virtual product Gross Sales--
left outer join
(select sum(mvp.qty*aap.BasePrice)as VirtualProductSales,
soa.categoryID, fr.OriginatingAirportID
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID
join ManualVirtualProduct mvp with (nolock)on cb.CashBagID=mvp.CashBagID
join AirlineArticle aa with (nolock) on aa.AirlineArticleID=mvp.AirlineArticleID
join AirlineArticlePrice aap with (nolock) on aa.AirlineArticleID=aap.AirlineArticleID
and aap.StartDate<=bi.FlightDate
and aap.EndDate>=bi.FlightDate
join StockOwnerArticle soaon aa.StockOwnerArticleID = soa.StockOwnerArticleID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('Manual','All')
group by soa.CategoryID, fr.OriginatingAirportID
) VP
on fr.OriginatingAirportID=VP.OriginatingAirportID
and B.CategoryID=VP.CategoryID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
) GS
on fr.OriginatingAirportID=GS.OriginatingAirportID
-- Gross Sales End--
--Discount--
join
(select sum((case when (bi.ISManual = 1 and @EPOSManual in ('Manual','All')) then isnull(MD.ManualDiscount,0) else 0 end) +
(case when (bi.ISManual = 0 and @EPOSManual in ('EPOS','All')) then isnull(ED.EPOSDiscount ,0) else 0 end)
) as Discount,
MD.CategoryID, fr.OriginatingAirportID
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID
join
--Manual Discount--
(select sum(case when bi.IsManual=1 then ManualTotalDiscountAmountApplied end) as ManualDiscount,
soa.CategoryID,bi.barsetinstanceid, fr.OriginatingAirportID
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join WarehouseCount wc with (nolock) on bi.BarsetInstanceID=wc.BarsetInstanceID
join AirlineArticle aa with (nolock) on aa.AirlineArticleID=wc.AirlineArticleID
join StockOwnerArticle soa with (nolock) on soa.StockOwnerArticleID=aa.StockOwnerArticleID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('Manual','All','EPOS')
group by soa.CategoryID,bi.barsetinstanceid, fr.OriginatingAirportID
) MD
on bi.BarsetInstanceID=MD.BarsetInstanceID
--Manual Discount End--
left outer join
--EPOS Discount--
(select sum(TotalDiscountAmountApplied) as EPOSDiscount,
soa.CategoryID,bi.barsetinstanceid,fr.OriginatingAirportID
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join FlightBarset fb with (nolock) on bi.BarsetInstanceID=fb.BarsetInstanceID
join FlightInstance fi with (nolock) on fi.FlightInstanceID=fb.FlightInstanceID
join [Transaction] t with (nolock) on fb.FlightInstanceID=t.FlightInstanceID
join TransactionType tt with (nolock) on tt.TypeID=t.TypeID
join TransactionArticle ta with (nolock) on t.TransactionID=ta.TransactionID
join AirlineArticle aa with (nolock) on aa.AirlineArticleID=ta.AirlineArticleID
join StockOwnerArticle soa with (nolock) on soa.StockOwnerArticleID=aa.StockOwnerArticleID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and tt.TypeName='SALE'
and t.Voided=0
and bi.Ismanual=0
and @EPOSManual in ('EPOS','All')
group by soa.CategoryID,bi.barsetinstanceid, fr.OriginatingAirportID
) ED
on bi.BarsetInstanceID=ED.BarsetInstanceID
and MD.CategoryID=ED.CategoryID
--EPOS DiscountEnd--
group by MD.CategoryID, fr.OriginatingAirportID
) D
on fr.OriginatingAirportID=D.OriginatingAirportID
and GS.CategoryID=D.CategoryID
--Discount End--
join
--Pax Count--
(select distinct fr.OriginatingAirportID,
case when EPOSPax is null then 0 else EPOSPax end +
case when ManualPax is null then 0 else ManualPax end as Pax
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID
--Auto Pax Count--
left outer join
(select fr.OriginatingAirportID,sum(fi.PassengerCount) as EPOSPax
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join FlightBarset fb with (nolock)on bi.BarsetInstanceID=fb.BarsetInstanceID
join FlightInstance fi with (nolock)on fi.FlightInstanceID=fb.FlightInstanceID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('EPOS','All')
and bi.IsManual=0
group by fr.OriginatingAirportID
) A
on fr.OriginatingAirportID=A.OriginatingAirportID
--Auto Pax Count End--
--Manual Pax Count--
left outer join
(select fr.OriginatingAirportID,
sum(mfi.PassengerCount) as ManualPax
from BarsetInstance bi with (nolock)
join BarsetInstanceFlightSchedule bifs with (nolock)
on bi.BarsetInstanceID=bifs.BarsetInstanceID
join FlightSchedule fs with (nolock)
on fs.FlightScheduleID=bifs.FlightScheduleID
join FlightRoute fr with (nolock)
on fr.FlightRouteID=fs.FlightRouteID
join CashBag cb with (nolock)on bi.BarsetInstanceID=cb.BarsetInstanceID
join ManualFlightInstance mfi with (nolock)on cb.CashBagID=mfi.CashBagID
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
and @EPOSManual in ('Manual','All')
and bi.IsManual=1
group by fr.OriginatingAirportID
) B
on fr.OriginatingAirportID=B.OriginatingAirportID
--Manual Pax Count End--
where bi.FlightDate >= @Start
and bi.FlightDate < @End+1
) P
on fr.OriginatingAirportID=P.OriginatingAirportID
--Pax End--
join Airport a with (nolock) on a.AirportID=fr.OriginatingAirportID
join Country c with (nolock) on c.CountryID=a.AirportCountry
where soa.AirlineID = @MerchantID
and soa.StockOwnerID = @StockOwnerID
and bi.FlightDate >= @Start
and bi.FlightDate < @End+1
--Recursive function to get BrandName and CategoryName--
declare @ID int
declare @ID1 int, @CATID int
declare @CategoryName varchar(max)
declare c cursor for
select distinct ID from #tempCategory
open c
fetch next from c into @ID
while @@fetch_status=0
begin
WITH CategoryChart (CategoryID,CategoryName,[Level],SortOrder) AS
(
-- Create the anchor query. This establishes the starting point --
SELECT a.CategoryID,a.Name,[Level],a.SortOrder
FROM dbo.Category a
join CategoryType ct on ct.TypeID=a.TypeID
where a.CategoryID=@ID
UNION ALL
-- Create the recursive query. This query will be executed until it returns no more rows--
SELECT c.CategoryID,c.Name,ct.[Level],c.SortOrder
FROM dbo.Category a
join Category c on a.ParentCategoryID=c.CategoryID
join CategoryType ct on ct.TypeID=c.TypeID
INNER JOIN CategoryChart b ON a.CategoryID = b.CategoryID
where a.CategoryID<>a.ParentCategoryID
)
insert into #tempRecursive
select CategoryID, CategoryName, @ID ,[Level] from categorychart
fetch next from c into @ID
end
close c
deallocate c
--Getting the CategoryName
Update #tempCategory
Set CategoryName=tr.CategoryName ,
CategoryID=tr.CategoryID
from #temprecursive tr
Where #tempCategory.ID=tr.ID
and [level]=1
--Getting the Brand Name
Update #tempCategory
Set BrandName= tr.CategoryName
from #temprecursive tr
Where #tempCategory.ID=tr.ID
and [level]=2
--Grouping the data by Category
insert into #tempCat(AirportID,AirportCode,AirportName,CountryID,Country,CategoryID,CategoryName,NetSales,Pax,SpendPerHead)
select AirportID, AirportCode, AirportName, CountryID, Country, CategoryID, CategoryName, sum(NetSales), Pax,
case when Pax = 0 then 0 else sum(netsales)/Pax end
from #tempCategory
group by AirportID,AirportCode,AirportName,CountryID,Country,CategoryName,Pax,CategoryID
--EU
insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country ,
NetSales ,Pax ,SpendPerHead,CategoryName,CategoryID)
select 0,'EU','','','Europe',sum([NetSales]),t2.Pax,
case when t2.Pax = 0 then 0 else sum([NetSales])/t2.Pax end,
CategoryName,CategoryID
from #tempcat tc join #temp2 t2
on 'EU'=t2.AirportCode
where tc.Country not in ('United Kingdom','Switzerland')
group by CategoryName,CategoryID,t2.Pax
--If any Category is missing for EU make it 0
insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country ,
NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName)
select distinct 0,'EU','','','Europe',0,0,0,tr.CategoryID,tc1.CategoryName
from #tempcat tc
right outer join #temprecursive tron tc.CategoryID=tr.CategoryID
and AirportCode in ('EU')
join #tempcat tc1on tc1.CategoryID=tr.CategoryID
where tc.CategoryID is null
--UK
insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country ,
NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName)
select 0,'UK','','',tc.Country,sum([NetSales]),t2.pax ,
case when t2.pax = 0 then 0 else sum([NetSales])/t2.pax end,
CategoryID,CategoryName
from #tempcat tc join #temp2 t2
on 'UK'=t2.AirportCode
where tc.Country in ('United Kingdom')
group by tc.Country,categoryid,CategoryName,t2.pax
--
--If any Category is missing for UK make it 0
insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country ,
NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName)
select distinct 0,'UK','','','United Kingdom',0,0,0,tr.CategoryID,tc1.CategoryName
from #tempcat tc
right outer join #temprecursive tr on tc.CategoryID=tr.CategoryID
and AirportCode in ('UK')
join #tempcat tc1 on tc1.CategoryID=tr.CategoryID
where tc.CategoryID is null
--
--
----Swiss
insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country ,
NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName)
select 0,'SWISS','','',tc.Country,sum([NetSales]),t2.pax ,
case when t2.pax = 0 then 0 else sum([NetSales])/t2.pax end,
CategoryID,CategoryName
from #tempcat tc join #temp2 t2
on 'SWISS'=t2.AirportCode
where tc.Country in ('Switzerland')
group by tc.Country,categoryid,CategoryName,t2.pax
--
--If any Category is missing for SWISS make it 0
insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country ,
NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName)
select distinct 0,'SWISS','','','Switzerland',0,0,0,tr.CategoryID,tc1.CategoryName
from #tempcat tc
right outer join #temprecursive tr on tc.CategoryID=tr.CategoryID
and AirportCode in ('SWISS')
join #tempcat tc1 on tc1.CategoryID=tr.CategoryID
where tc.CategoryID is null
--Total
insert into #tempcat (AirportID ,AirportCode ,AirportName ,CountryID ,Country ,
NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName)
select 0,'Total','','','',sum([NetSales]),t2.pax,
case when t2.Pax = 0 then 0 else sum([NetSales])/t2.pax end,
CategoryID,CategoryName
from #tempcat tc join #temp2 t2
on 'Total'=t2.AirportCode
where tc.AirportCode not in ('EU','UK','SWISS')
group by categoryid,CategoryName,t2.Pax
--Converting row into column and vice versa
insert into #tempC(Country,CountryID,AirportCode,AirportName,CategoryName,Number,DataType,i)
SELECT Country,CountryID,AirportCode,AirportName,CategoryName,Number,DataType,
case when DataType='SpendPerHead' then 1
end as I
from #tempCat
UNPIVOT
(Number FOR DataType IN
([SpendPerHead]
)) u
order by countryid desc
-- Getting the Data in the order (Bases,UK,Europe,Total)
insert into #temp1 (Airport,AirportName)
select t.AirportCode,t.AirportName
from #temp t
join
(select AirportCode,max(i1) as i1 from #temp
group by AirportCode
) A
on t.i1=A.i1
--Get the count
select @j-2=count(Airport) from #temp1
set @sql='select DataType'
set @sql1='select CategoryName'
while (@i<=@j)
begin
select @sql=@sql+',isnull(max(case when AirportCode='''+Airport+''' then Number end),0) as ['+Airport+' '+AirportName+']' from #temp1 where i=@i
select @sql1=@sql1+',isnull(max(case when AirportCode='''+Airport+''' then Number end),0) as ['+Airport+' '+AirportName+']' from #temp1 where i=@i
set @i=@i+1
end
set @sql=@sql+' from #temp group by I,DataType order by I'
set @sql1=@sql1+' from #tempC group by I,CategoryName order by I'
EXEC sp_executesql @sql
EXEC sp_executesql @sql1
----
drop table #temp
drop table #temp1
drop table #Unit
drop table #tempCategory
drop table #tempRecursive
drop table #tempC
drop table #temp2
drop table #tempcat
November 3, 2009 at 12:48 pm
Before I could even begin to tune this, I would need the create scripts for all the source tables, insert statements for some sample data for each, and the expected inputs and output for it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 12:51 pm
Alright let me work on that...thanks.
November 3, 2009 at 4:52 pm
Please post table, index definitions and execution plans. Take a look through this article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Also, take a look at that article, about narrowing down exactly where the performance problems speciofically are. Can you narrow the problem area down?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2009 at 7:28 pm
Gail,
Thanks for the tip on the article. It was good reading and good advise. I'll definitely use that approach this go around.
Thanks,
David
November 3, 2009 at 10:43 pm
I've attached the table defs to this query as well as the input and output in an Excel sheet. Let me know what you guys think! Thanks!
November 4, 2009 at 12:16 am
Execution plan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2009 at 1:07 am
This condition , and the others like it, are jumping out at me
@EPOSManual in ('Manual','All')
What are the possible options of @EPosManual ?
This type of coding will cause massive scans.
There doesent seem to be any need for you cursor 'c', that can easily be removed.
The inserts to #tempCat look like they should be optimized into one statement.
All this
update #temp2 set [Net Sales]=0 where [Net Sales] is null
update #temp2 set [Units Sold]=0 where [Units Sold] is null
update #temp2 set [No. Of EPOS Transactions]=0 where [No. Of EPOS Transactions] is null
update #temp2 set PAX=0 where PAX is null
update #temp2 set [Actual SPH]=0 where [Actual SPH] is null
update #temp2 set [Forecast SPH]=0 where [Forecast SPH] is null
update #temp2 set [Crew Target SPH]=0 where [Crew Target SPH] is null
update #temp2 set [Variance (Act Vs Crew)]=0 where [Variance (Act Vs Crew)] is null
update #temp2 set [Variance (Act Vs ForeCast)]=0 where [Variance (Act Vs ForeCast)] is null
update #temp2 set [Average Transaction Value EPOS]=0 where [Average Transaction Value EPOS] is null
update #temp2 set [Average Number Of Items Per Transaction EPOS]=0 where [Average Number Of Items Per Transaction EPOS] is null
certainly can to..
November 4, 2009 at 7:18 am
1. For '@EPOSManual' I believe the only available options are "'Manual','All','EPOS'".
2. Can you tell me in more detail why cursor C is not needed in the code? It's used for a recursive function to get brandName, and CategoryName.
3. This might sound like a rookie question, but how would I consolidate the update statements in 1 statement with all the "where" clauses in the individual statements?
Thanks for you help!!!
November 4, 2009 at 7:20 am
Gail,
The execution plan is on it's way. I had to re-run the query since I didn't set the options last time I ran it. It'll take almost 5 hours to process.
Thanks,
David
November 4, 2009 at 8:04 am
1. The problem with this is that sqlServer will scan every row testing the logic, it wont shortcuit.
2. This should replace your cursor, though i cant test it...
with DistinctCategoryList
(
select distinct ID from #tempCategory
),
CategoryChart (RootCategoryId,CategoryID,CategoryName,[Level],SortOrder) AS
(
-- Create the anchor query. This establishes the starting point --
SELECT a.CategoryID a.CategoryID,a.Name,[Level],a.SortOrder
FROM DistinctCategoryList
join dbo.Category a
on a.CategoryID = DistinctCategoryList.ID
join CategoryType ct on ct.TypeID=a.TypeID
UNION ALL
-- Create the recursive query. This query will be executed until it returns no more rows--
SELECT b.RootCategoryId,c.CategoryID,c.Name,ct.[Level],c.SortOrder
FROM dbo.Category a
join Category c on a.ParentCategoryID=c.CategoryID
join CategoryType ct on ct.TypeID=c.TypeID
INNER JOIN CategoryChart b ON a.CategoryID = b.CategoryID
where a.CategoryID<>a.ParentCategoryID
)
insert into #tempRecursive
select CategoryID, CategoryName, RootCategoryId ,[Level] from categorychart
3. Try
Update #temp2
set [Net Sales] = isnull([Net Sales],0),
[Units Sold] = isnull([Units Sold],0),
[No. Of EPOS Transactions] = isnull([No. Of EPOS Transactions],0),
where [Net Sales] is null or
[Units Sold] is null or
[No. Of EPOS Transactions] is null or....
etc....
You might ( and probably will ) find that the overhead of rewritting the data will be less than the multiple scans required.
November 4, 2009 at 11:17 am
Dave Ballantyne (11/4/2009)
1. The problem with this is that sqlServer will scan every row testing the logic, it wont shortcuit.
Providing there's an appropriate index, Column IN (Value1, Value2) will be evaluated as an index seek with 2 seek predicates. It won't cause a scan. NOT IN will cause a scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2009 at 11:41 am
GilaMonster (11/4/2009)
Providing there's an appropriate index, Column IN (Value1, Value2) will be evaluated as an index seek with 2 seek predicates. It won't cause a scan. NOT IN will cause a scan.
Its not a column though, its a variable. Better still, a parameter, fun with sniffing yet to come:-D
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply