November 8, 2005 at 12:48 am
Is there any way to make this function faster
CREATE FUNCTION fn_Item_UpToDate_Cost (@p_Year integer, @p_date nvarchar(12), @p_connType nvarchar)
RETURNS @ItemUpToDateCost table
(
ItemId integer,
MatCode nvarchar(12),
ItemCode nvarchar(26),
ItemDesc nvarchar(100),
Balance float,
Cost float
)
AS
begin
declare @itemId as integer
declare @itemCode as nvarchar(26)
declare @MatCode as nvarchar(12)
declare @desc as nvarchar(100)
declare @Balance as float
declare srv_cursor cursor READ_ONLY for
SELECT view_item_current_balance.Vicb_ItemID, RawMat.rmt_itemcode ,
RawMat.rmt_matcode , RawMat.rmt_description ,
round(view_item_current_balance.vicb_balance ,3) AS Balance
FROM view_item_current_balance INNER JOIN RawMat WITH (NOLOCK) ON view_item_current_balance.Vicb_ItemID = RawMat.rmt_id
WHERE (view_item_current_balance.vicb_fin_year = @p_Year ) AND (RawMat.rmt_rmtype = @p_ConnType)
open srv_cursor
FETCH NEXT FROM srv_cursor INTO @itemId, @itemCode, @MatCode, @desc, @Balance
WHILE (@@FETCH_STATUS =0)
begin
declare @sum1 as float
declare @bal as float
declare @qty as float
declare @upjd as float
declare @flag as integer
declare srv_cursor1 cursor READ_ONLY for
SELECT isnull(History_Detail.hsd_ins_qty,0) ,
isnull(History_Detail.hsd_upjd,0)
FROM History_Header WITH (NOLOCK) INNER JOIN History_Detail WITH (NOLOCK) ON History_Header.hsh_trans_id = History_Detail.hsd_hsh_trans_id
WHERE History_Header.hsh_trans_type in ('SRV', 'RTV', 'OBT', 'ADJ')
AND History_Header.hsh_fin_year = @p_year
AND (History_Header.hsh_trans_date <= CONVERT(SMALLDATETIME,@p_date, 103))
and (History_Detail.hsd_ins_qty>0)
AND History_Detail.hsd_rmt_id = @itemId
order by History_Header.hsh_trans_date desc ,History_Header.hsh_trans_id desc
set @sum1=0
set @flag=0
OPEN srv_cursor1
FETCH NEXT FROM srv_cursor1 INTO @Qty, @UPJD
WHILE (@@FETCH_STATUS = 0) and @flag =0
BEGIN
if (@qty < @bal)
begin
set @sum1 = (@sum1 + ( @Qty * @UPJD ) )
FETCH NEXT FROM srv_cursor1 INTO @Qty, @UPJd
end
else
begin
set @sum1 = ( @sum1 + ( @bal * @UPJD ) )
set @flag=1
end
END
CLOSE srv_cursor1
deallocate srv_cursor1
insert into @ItemUpToDateCost
(ItemId, MatCode, ItemCode, ItemDesc, Balance, Cost) values
(@ItemId, @matCode,@itemCode,@desc,@balance,@sum1)
FETCH NEXT FROM srv_cursor INTO @itemId, @itemCode, @MatCode, @desc, @Balance
end
close srv_cursor
deallocate srv_cursor
return
end
November 9, 2005 at 3:04 am
Simple answer...yes...get rid of the cursors.
SELECT view_item_current_balance.Vicb_ItemID, RawMat.rmt_itemcode ,
RawMat.rmt_matcode , RawMat.rmt_description ,
round(view_item_current_balance.vicb_balance ,3) AS Balance
FROM view_item_current_balance INNER JOIN RawMat WITH (NOLOCK) ON view_item_current_balance.Vicb_ItemID = RawMat.rmt_id
WHERE (view_item_current_balance.vicb_fin_year = @p_Year ) AND (RawMat.rmt_rmtype = @p_ConnType)
should be joined to (all in the one statement)
SELECT isnull(History_Detail.hsd_ins_qty,0) ,
isnull(History_Detail.hsd_upjd,0)
FROM History_Header WITH (NOLOCK) INNER JOIN History_Detail WITH (NOLOCK) ON History_Header.hsh_trans_id = History_Detail.hsd_hsh_trans_id
WHERE History_Header.hsh_trans_type in ('SRV', 'RTV', 'OBT', 'ADJ')
AND History_Header.hsh_fin_year = @p_year
AND (History_Header.hsh_trans_date <= CONVERT(SMALLDATETIME,@p_date, 103))
and (History_Detail.hsd_ins_qty>0)
AND History_Detail.hsd_rmt_id = @itemId
order by History_Header.hsh_trans_date desc ,History_Header.hsh_trans_id desc
Then you need to add a GROUP BY clause and/or a CASE statement because you are doing some summarising and finally precede it with an INSERT statement to save the data.
This is called SET-BASED PROCESSING and is far better than cursors from a performance point of view. 99% of SQL activity can be done without cursors for a far better performance. Search for examples of same. Or post your effort here for peer review.
November 10, 2005 at 3:18 am
It will probably run faster as a proc
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply