SP Times Out - Need Optimization

[gregmoser]

Can Someone please help me restucture the way that i am doing this query? I am creating a table and for each record of thousands is running a function that has MANY select staments... Is there a better way. Below is the code for the procedure, and then the function:

ALTER PROCEDURE [dbo].[spCreateInventoryTable]
AS
DROP TABLE dbo.InventoryUpdate
SELECT
icupc.UPC_CODE As 'UPC_CODE',
dbo.AtsCalculation(icupc.UPC_CODE) - ISNULL(0, ivwsdd.IVWD_QTY) As 'QTY'
INTO
dbo.InventoryUpdate
FROM
icupc
LEFT JOIN
ivwsdd
ON icupc.UPC_CODE = ivwsdd.IVWD_UPC INNER
JOIN
icmst
ON icupc.UPC_STY = icmst.STY_NUM
WHERE
ISNULL(icmst.STY_DIS, '1/1/1900')='1/1/1900'





ALTER FUNCTION [dbo].[AtsCalculation]
(
@UPC CHAR(12)
)
RETURNS int
AS
BEGIN
DECLARE
@StyleNumber varchar(10),
@SizeGroup varchar(10),
@Size varchar(10),
@Color varchar(10),
@SizeCheck varchar(10),
@i int,
@MyReturn varchar(200)

SET @i = 0

SELECT
@StyleNumber = icmst.STY_NUM,
@SizeGroup = icmst.STY_SIZR,
@Size = icupc.UPC_SIZ,
@Color = icupc.UPC_CLR
FROM
icupc
INNER JOIN
icmst
ON icmst.STY_NUM = icupc.UPC_STY
WHERE
icupc.UPC_CODE = @UPC

WHILE @i<19
BEGIN
SET @i = @i+1
IF (
SELECT
CASE @i
WHEN 1 THEN IC_SZ1
WHEN 2 THEN IC_SZ2
WHEN 3 THEN IC_SZ3
WHEN 4 THEN IC_SZ4
WHEN 5 THEN IC_SZ5
WHEN 6 THEN IC_SZ6
WHEN 7 THEN IC_SZ7
WHEN 8 THEN IC_SZ8
WHEN 9 THEN IC_SZ9
WHEN 10 THEN IC_SZ10
WHEN 11 THEN IC_SZ11
WHEN 12 THEN IC_SZ12
WHEN 13 THEN IC_SZ13
WHEN 14 THEN IC_SZ14
WHEN 15 THEN IC_SZ15
WHEN 16 THEN IC_SZ16
WHEN 17 THEN IC_SZ17
WHEN 18 THEN IC_SZ18
END
FROM
icszh
WHERE
icszh.IC_SZ_CD = @SizeGroup
AND
@Size=
CASE @i
WHEN 1 THEN IC_SZ1
WHEN 2 THEN IC_SZ2
WHEN 3 THEN IC_SZ3
WHEN 4 THEN IC_SZ4
WHEN 5 THEN IC_SZ5
WHEN 6 THEN IC_SZ6
WHEN 7 THEN IC_SZ7
WHEN 8 THEN IC_SZ8
WHEN 9 THEN IC_SZ9
WHEN 10 THEN IC_SZ10
WHEN 11 THEN IC_SZ11
WHEN 12 THEN IC_SZ12
WHEN 13 THEN IC_SZ13
WHEN 14 THEN IC_SZ14
WHEN 15 THEN IC_SZ15
WHEN 16 THEN IC_SZ16
WHEN 17 THEN IC_SZ17
WHEN 18 THEN IC_SZ18
END
)<>''
BEGIN


SELECT
@MyReturn =
CASE @i
WHEN 1 THEN oh.ICQ_Q1-so.ICQ_Q1
WHEN 2 THEN oh.ICQ_Q2-so.ICQ_Q2
WHEN 3 THEN oh.ICQ_Q3-so.ICQ_Q3
WHEN 4 THEN oh.ICQ_Q4-so.ICQ_Q4
WHEN 5 THEN oh.ICQ_Q5-so.ICQ_Q5
WHEN 6 THEN oh.ICQ_Q6-so.ICQ_Q6
WHEN 7 THEN oh.ICQ_Q7-so.ICQ_Q7
WHEN 8 THEN oh.ICQ_Q8-so.ICQ_Q8
WHEN 9 THEN oh.ICQ_Q9-so.ICQ_Q9
WHEN 10 THEN oh.ICQ_Q10-so.ICQ_Q10
WHEN 11 THEN oh.ICQ_Q11-so.ICQ_Q11
WHEN 12 THEN oh.ICQ_Q12-so.ICQ_Q12
WHEN 13 THEN oh.ICQ_Q13-so.ICQ_Q13
WHEN 14 THEN oh.ICQ_Q14-so.ICQ_Q14
WHEN 15 THEN oh.ICQ_Q15-so.ICQ_Q15
WHEN 16 THEN oh.ICQ_Q16-so.ICQ_Q16
WHEN 17 THEN oh.ICQ_Q17-so.ICQ_Q17
WHEN 18 THEN oh.ICQ_Q18-so.ICQ_Q18
END
FROM
icqtys oh
INNER JOIN
icqtys so
ON oh.ICQ_STYLE=so.ICQ_STYLE
WHERE
oh.ICQ_STYLE = @StyleNumber
AND
oh.ICQ_COLOR = @Color
AND
oh.ICQ_QTYTYPE = 'OH'
AND
so.ICQ_QTYTYPE = 'SO'

SET @i=20
END
END

SET @MyReturn = ISNULL(@MyReturn,0)
RETURN @MyReturn
END

 

[mrdenny]

Oh my.

As written that is going to be slow, very slow.

First I would recommend doing a CREATE TABLE command instead of a SELECT INTO command. This will prevent locking on the system objects of your database while this command is running. That or simply truncate the table and reload it.

The loop is killing you. For each record you are processing you are doing between 2 and 19 select statements.

If this was my system, I'd normalize the tables into either table variables or temp tables so make the joining easier. If done correctly you could probably get rid of the function all together.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gregmoser]

OK, i relize that the loop is killing me, but how do i create table variables or temp tables? i guess i'm just not that farmiliar with the concepts... and how to use them in this instance.

Is there any article i could read online for this type of thing?

 

[mrdenny]

There is lots of good into in Books OnLine.

Temp tables can be found under "temporary tables" in the index.
Table variables can be found under "table variables" in the index.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com