Re: SQL express performens lagg

Jacob Ipsen wrote:

Select 1 as DiscNiv ,DiscountQuantity,DiscountPercent,DiscountAm from ProdPrDcMat where ((DiscountPercent <> 0)or (DiscountAm <> 0)) and ((ProdNo = @ProdNo) and (barcode = @Barcode) and (DiscountQuantity <= @Quantity) and (FrDt < @DDate and ToDt > @DDate )) UNION
Select 2 as DiscNiv ,DiscountQuantity,DiscountPercent,DiscountAm from ProdPrDcMat where ((DiscountPercent <> 0)or (DiscountAm <> 0)) and ((ProdNo = @ProdNo) and (barcode = @Barcode) and (FrDt < @DDate and ToDt > @DDate )) UNION
Select 3 as DiscNiv ,DiscountQuantity,DiscountPercent,DiscountAm from ProdPrDcMat where ((DiscountPercent <> 0)or (DiscountAm <> 0)) and ((ProdNo = @ProdNo) and (barcode = @Barcode) and (DiscountQuantity <= @Quantity)) UNION
Select 4 as DiscNiv ,DiscountQuantity,DiscountPercent,DiscountAm from ProdPrDcMat where ((DiscountPercent <> 0)or (DiscountAm <> 0)) and ((ProdNo = @ProdNo) and (barcode = @Barcode)) UNION
Select 5 as DiscNiv ,DiscountQuantity,DiscountPercent,DiscountAm from ProdPrDcMat where ((DiscountPercent <> 0)or (DiscountAm <> 0)) and ((ProdNo = @ProdNo) and (DiscountQuantity <= @Quantity) and (FrDt < @DDate and ToDt > @DDate )) UNION
Select 6 as DiscNiv ,DiscountQuantity,DiscountPercent,DiscountAm from ProdPrDcMat where ((DiscountPercent <> 0)or (DiscountAm <> 0)) and ((ProdNo = @ProdNo) and (FrDt < @DDate and ToDt > @DDate )) UNION
Select 7 as DiscNiv ,DiscountQuantity,DiscountPercent,DiscountAm from ProdPrDcMat where ((DiscountPercent <> 0)or (DiscountAm <> 0)) and ((ProdNo = @ProdNo) and (DiscountQuantity <= @Quantity)) UNION
Select 8 as DiscNiv ,DiscountQuantity,DiscountPercent,DiscountAm from ProdPrDcMat where ((DiscountPercent <> 0)or (DiscountAm <> 0)) and (ProdNo = @ProdNo and (DiscountQuantity = 0))
Order by DiscountQuantity,DiscNiv asc

Why not replace this monstrosity with the following:

select
case
when barcode = @Barcode
and @Quantity >= DiscountQuantity
and @DDate between FrDt and ToDt
then 1
when barcode = @Barcode
and @Quantity < DiscountQuantity
and @DDate between FrDt and ToDt
then 2
when barcode = @Barcode
and @Quantity >= DiscountQuantity
and @DDate not between FrDt and ToDt
then 3
when barcode = @Barcode
and @Quantity < DiscountQuantity
and @DDate not between FrDt and ToDt
then 4
when barcode <> @Barcode
and @Quantity >= DiscountQuantity
and @DDate between FrDt and ToDt
then 5
when barcode <> @Barcode
and @Quantity < DiscountQuantity
and @DDate between FrDt and ToDt
then 6
when barcode <> @Barcode
and @Quantity >= DiscountQuantity
and @DDate not between FrDt and ToDt
then 7
when barcode <> @Barcode
and @Quantity < DiscountQuantity
and @DDate not between FrDt and ToDt
then 8
else -1
end as DiscNiv,
DiscountQuantity,
DiscountPercent,
DiscountAm
from ProdPrDcMat
where (DiscountPercent <> 0 or DiscountAm <> 0)
and ProdNo = @ProdNo
order by DiscountQuantity, DiscNiv
.

Relevant Pages

  • Re: SQL express performens lagg
    ... Select 2 as DiscNiv,DiscountQuantity,DiscountPercent,DiscountAm from ... when barcode = @Barcode ... and @DDate between FrDt and ToDt ... and @Quantity < DiscountQuantity ...
    (microsoft.public.sqlserver)
  • Re: SQL express performens lagg
    ... DECLARE @DDate as nvarchar; ... Select 1 as DiscNiv,DiscountQuantity,DiscountPercent,DiscountAm from ... SQL Server 2005 has the Database Engine Tuning Advisor tool, ... where I have some "problems" regarding to the way the SQL Express is ...
    (microsoft.public.sqlserver)
Loading