Need a set based solution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need a set based solution

Author

Topic

dadunn
Yak Posting Veteran

67 Posts

Posted - 2001-09-24 : 16:27:06

I have been banging my head trying to develop a set based solution (or even a faster cursor based solution) for the following problem. This update takes nearly 40 minutes to run and kills the performance on the server.

I build a temporary table with the Locations that exist in the system and then run the import table through a cursor so that I can perform the tests on each row and assign the correct Lot number to the row.

Then I delete that lot number from the temporary table and move to the next row.

Sorry for the long post. Her's the code and thanks.

/******INSERT LOTTABLES INTO IMPORT940 TABLE********************/
CREATE table #INSERTLOTTABLE
(
editdate char(10),
lottable01 char(50),
sku char(20),
loc char(10)
)

INSERT into #INSERTLOTTABLE
SELECT DISTINCT convert(char,b.EditDate,105) as EditDate, c.Lottable01,b.SKU,b.loc
FROM
IMPORT940 a,
LOTXLOCXID b,
LOTATTRIBUTE c,
RECEIPTDETAIL d

WHERE
(a.d_StorerKey = b.StorerKey AND
a.d_SKU = b.SKU) AND
(LEFT(b.ID,10) = d.ReceiptKey AND
RIGHT(rtrim(b.ID),5) = d.ReceiptLineNumber) AND
(b.Lot = c.Lot AND
c.Lottable01 = d.Lottable01) AND
b.Qty > 0 AND
c.Lottable01 NOT IN (SELECT DISTINCT Lottable01
FROM ORDERDETAIL
WHERE StorerKey = "123")
ORDER BY d.EditDate,b.loc, c.Lottable01

/***************INSERT LOTTABLES INTO IMPORT940 TABLE**************************/
DECLARE @d_susr5 char(30),
@d_sku char(20)

DECLARE CURSOR_IMPORT940LOTTABLES CURSOR for
SELECT d_susr5,d_sku FROM import940 ORDER BY d_orderkey,d_sku

Open CURSOR_IMPORT940LOTTABLES
Fetch NEXT FROM CURSOR_IMPORT940LOTTABLES into @d_susr5,@d_sku

WHILE @@FETCH_STATUS = 0
BEGIN
update import940 set d_lottable01 =
(SELECT top 1 lottable01 FROM #INSERTLOTTABLE
WHERE sku = @d_sku ORDER BY editdate,lottable01)

WHERE d_sku = @d_sku and d_susr5 = @d_susr5

DELETE FROM #INSERTLOTTABLE WHERE lottable01 =
(SELECT top 1 lottable01 FROM #INSERTLOTTABLE
WHERE sku = @d_sku
ORDER BY editdate,lottable01)

Fetch NEXT FROM CURSOR_IMPORT940LOTTABLES into @d_susr5,@d_sku
END

close CURSOR_IMPORT940LOTTABLES
Deallocate CURSOR_IMPORT940LOTTABLES
Drop table #INSERTLOTTABLE

UPDATE import940 set d_lottable01 = 'NONE'
WHERE d_lottable01 is null

>>>>>>>>>>>>>>>>>>>>>>>
Just sittin' around waiting for the servers to crash.

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources