Problem with optimizing update query

Question

Problem with optimizing update query

danielc-651043

SSC Rookie

Points: 39
More actions
June 12, 2008 at 5:46 am

#183428

Hi...
This is my first post......
i have an update query which calls an user defined function for every row...
i have to update around 16000 rows in a temporary table by calling that function which returns the value to be updated....
Problem is it takes hell lot of time.........like for 16000 rows it takes around 8 mins...
Is there any work around for optimizing it???
Heres the update query and function....
UPDATE #TEMPINVENTORYHISTORY
SET
ORIGCONTAINERNO = DBO.UFN_SCH_INVENTORY_TRACKING( TRANSID, 'CONTAINER'),
ORIGPACKAGENO = DBO.UFN_SCH_INVENTORY_TRACKING( TRANSID, 'PACKAGE')
WHERE
TRANSTYPE = 'REPACKAGE'
CREATE FUNCTION Ufn_SCH_Inventory_Tracking
(
@TRANSID VARCHAR(50),
@COLUMNNAME VARCHAR(50)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @ReturnValue VARCHAR(500)
IF(@COLUMNNAME = 'CONTAINER')
BEGIN
SELECT @ReturnValue = ISNULL(@ReturnValue + ',', '') + A.OrigContainerNo FROM (SELECT DISTINCT ORIGCONTAINERNO FROM TRANSREPACKAGING WHERE TRANSID = @TRANSID) A
END
ELSE
BEGIN
SELECT @ReturnValue = ISNULL(@ReturnValue + ',','') + CAST(A.ORIGPACKAGENO AS VARCHAR) FROM (SELECT DISTINCT ORIGPACKAGENO FROM TRANSREPACKAGING WHERE TRANSID = @TRANSID)A
END
RETURN @ReturnValue
END
Any help will be appreciated.
Regards.
Daniel.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Gut feel is that it's the UDF at fault here. It has to run for each row of the update. It's essentially a hidden cursor.

Is there any way you can remove the UDF or convert it to a table-valued UDF?

If not, what's the indexing on the table TRANSREPACKAGING like?

Is TRANSID in that table unique?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Janrith SSC-Addicted Points: 404 More actions · Answer 2

Took a stab at converting your code to a simple update statement. Is there any reason the following won't work for you?

UPDATE #TEMPINVENTORYHISTORY

SET ORIGCONTAINERNO = ORIGCONTAINERNO,

SET ORIGPACKAGENO = CAST(ORIGPACKAGENO as VARCHAR)

FROM TRANSREPACKAGING WHERE

WHERE TRANSTYPE = 'REPACKAGE'

AND TRANSID = @TRANSID

As an aside, Are you truly storing the ORIGCONTAINERNO and ORIGPACKAGNO as VARCHARS in the Temp table? Any reason not to just store them as INTs? If so, you can toss out the cast operation as well.

Hope that works for you.

-=Janrith

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 3

How about this?

UPDATE TIH--#TEMPINVENTORYHISTORY

SET

ORIGCONTAINERNO = TP.ORIGCONTAINERNO,

ORIGPACKAGENO = CAST(TP.ORIGPACKAGENO AS VARCHAR)

FROM #TEMPINVENTORYHISTORY TIH

INNER JOIN TRANSREPACKAGING TP ON TIH.TRANSID = TP.TRANSID

WHERE

TIH.TRANSTYPE = 'REPACKAGE'

******************

Janrith, there is NO @TRANSID.:)

Janrith SSC-Addicted Points: 404 More actions · Answer 4

Good point. That looks much cleaner 🙂

-=Janrith

danielc-651043 SSC Rookie Points: 39 More actions · Answer 5

Sorry for getting late to reply..........

TransID will be a foreignkey and TransRepackageId will be the primary key..

Regards,

Daniel.