[b] Simple Function Using Tables Variables Painfully Slow[/b]

[Nassy]

Hi

Background Info

I am currently in the process of writing code to load a Fact Table that stores information on supply and demand of crops for a company that sells bouquets of flowers.

The code I had originally written to do this was a mess of many temporary tables all bunged together. In order to make everything more modular and re-useable I have decided to make use of stored procedures and user defined functions.

The Problem I'm Having

I have written a UDF that works out what the demand is for a particular crop. The UDF returns a particular demand value based on the crop, product (the crop goes in to) and week.

The code is listed underneath.


Alter FUNCTION fn_GetDemand (@StockNumber nvarchar(100),
@WeekStartDate datetime, @ProductCategory nvarchar(100)
)
RETURNS int
/*Returns the demand associated with a particular stock.*/
AS
BEGIN

DECLARE @Demand int

DECLARE @onandbeforedates TABLE (
WeekSDate datetime,
Product nvarchar(100),
LiveDate datetime,
StockNumber nvarchar(100),
QtyStems int,
ForecastQty int
)


INSERT @onandbeforedates
Select
fcy.FCY_DATE,
gfo.GFO_STOCK_CATEGORY,
gfo.GFO_DATE_FROM,
gfo.GFO_STOCK_CODE,
gfo.GFO_QTY_PER,
fcy.FCY_FORECAST_QTY
from WorldFlowersStaging.dbo.GFO_REC gfo
inner join
WorldFlowersStaging.dbo.FCY_REC fcy
on gfo.GFO_STOCK_CATEGORY = fcy.FCY_STOCK_FC_CATEGORY
where
--@WeekStartDate >= gfo.GFO_DATE_FROM
fcy.FCY_DATE >= gfo.GFO_DATE_FROM

DECLARE @latestdates TABLE (
LatestLiveDate datetime,
Product nvarchar(100),
WeekSDate DateTime
)

INSERT @latestdates
Select
Max(LiveDate),
Product,
WeekSDate
from @onandbeforedates
group by WeekSDate, Product


DECLARE @Demands TABLE (
WeekSDate datetime,
Product nvarchar(100),
StockNumber nvarchar(100),
Demand int
)

INSERT @Demands
Select
ld.WeekSDate,
ld.Product,
obd.StockNumber,
obd.QtyStems * ForecastQty
from @LatestDates ld
inner join
@OnandBeforeDates obd
on
ld.WeekSDate = obd.WeekSDate
AND
ld.Product = obd.Product
AND
ld.LatestLiveDate = obd.LiveDate


/* I have debugged the function up until this point and creating the temporary tables is reasonably fast.

I believe it is the part underneath that is causing the problems - may be due to the number of parameters in the Where clause ???
*/

Select @Demand = Demand
from Demands
where WeekSDate = @WeekStartDate
and Product = @ProductCategory
and StockNumber = @StockNumber


RETURN(@Demand)

END
GO


My thoughts on this

I can see why using a function to update a column in a table would produce slower results than just using a set based operation but I have used very similar type functions before and this particular one runs like a dog with only one leg! To process 70 columns it takes over 10 minutes!!! I have checked that the function returns values ok and it does.

If I go back to my old code which relies 100% on temp tables it is a lot quicker.

All in all I would rather use functions if it is going to degrade performance only slightly.
If anyone can provide any advice on the use of functions in general and if possible on my particular function I would be extremely grateful.



Nassy

 

[SQLSister]

udf's are notoriously slow. They should be avoided where possible if you are going to process large sets of data through them.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Nassy]

Cheers - I have read up on this and will rewrite my code not to use this scalar function.

Nassy