Re: SQL express performens lagg

Hey

This works like a charm Ed :)

but sadly this still dosent solve my overall problem,
with the lagg.

the server still laggs some times... i allmoste feels like the server is
clering cashe or somthing.. it runs rely slow for aboudt 5-7 min and
after that it runs fine again...

dos any one have a clue to what the server dos doing this "lag" time ??

The Db is configured to run with simple log mode
the server is configured as defaulte, the only modification we have made to
the configuration is
we defined a max memory loft, otherwise the sql express swolloved all the
512 memory that ouer system runs with.

keep in mind that this system is only running SQL express and My Custom
Shell
on a Win XPe

Jacob

"Ed Murphy" <emurphy42@xxxxxxxxxxxx> skrev i en meddelelse
news:gsgZg.8078$8C4.5222@xxxxxxxxxxxxxxxxxxxxxxx
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


.

Loading