March 12, 2014 at 12:30 pm
I was part of an Oracle to SQL Server (2008 R2) conversion - the Oracle stored procedures used cursors a lot
I tried to convert to set based queries but this one stumped me - and it takes over 45 minutes to run as part of a large nightly process
legal told me to take it down:(
March 12, 2014 at 12:36 pm
Let's start with formatting this so we can read it.
ALTER PROCEDURE SlowStoredProcedure @FirstCUSTNO CHAR(6) = '000000'
,@LastCUSTNO CHAR(6) = '099999'
,@LowItem VARCHAR(30) = '0'
,@HighItem VARCHAR(30) = 'ZZZZZ'
AS
BEGIN
SET NOCOUNT ON
DECLARE @RightNow DATETIME
DECLARE @sum_shipqty INT
DECLARE @not_covered_qty INT
DECLARE @ATS_ITEM_NO VARCHAR(30)
DECLARE @ATS_CUSTNO CHAR(6)
DECLARE @ATS_QUANTITY INT
DECLARE @Availats TABLE (
ITEM_NO VARCHAR(30)
,CUSTNO CHAR(6)
,ATS_QTY INT
,ID INT IDENTITY(1, 1) PRIMARY KEY
)
DECLARE @ATS_Kount INT
DECLARE @ATS_Row_Kount INT
DECLARE @E_ROWID BIGINT
DECLARE @E_REQUIRED_QTY INT
DECLARE @E_SNR_STATUS VARCHAR(3)
DECLARE @Temp_Table TABLE (
REQUIRED_QTY INT
,SNR_STATUS VARCHAR(3)
,RowID BIGINT
,ID INT IDENTITY(1, 1) PRIMARY KEY
)
DECLARE @Kount INT
DECLARE @Row_Kount INT
BEGIN TRY
INSERT INTO @Availats (
CUSTNO
,ITEM_NO
,ATS_QTY
)
SELECT DISTINCT A.CUSTNO
,A.ITEM_NO
,OPN_QTY
FROM AVAILATS A
INNER JOIN ORDER_ITEMS_STAGE ESS ON A.ITEM_NO = ESS.ITEM_NO
AND A.CUSTNO = ESS.CUSTNO
WHERE A.CUSTNO BETWEEN @FirstCUSTNO
AND @LastCUSTNO
AND A.ITEM_NO BETWEEN @LowItem
AND @HighItem
SET @ATS_Row_Kount = @@ROWCOUNT
SET @ATS_Kount = 1
WHILE @ATS_Kount <= @ATS_Row_Kount
BEGIN
SELECT @ATS_ITEM_NO = ITEM_NO
,@ATS_CUSTNO = CUSTNO
,@ATS_QUANTITY = ATS_QTY
FROM @Availats
WHERE ID = @ATS_Kount
SET @sum_shipqty = @ATS_QUANTITY
INSERT INTO @Temp_Table (
REQUIRED_QTY
,SNR_STATUS
,RowID
)
SELECT REQUIRED_QTY
,SNR_STATUS
,RowID
FROM ORDER_ITEMS_STAGE
WHERE ITEM_NO = @ATS_ITEM_NO
AND CUSTNO = @ATS_CUSTNO
AND SNR_STATUS = ''
ORDER BY REQUIRED_DATE
IF @@ROWCOUNT > 0
BEGIN
SELECT @Kount = MIN(ID)
FROM @Temp_Table
SELECT @Row_Kount = MAX(ID)
FROM @Temp_Table
END
ELSE
BEGIN
SET @Kount = 999
SET @Row_Kount = 0
END
WHILE @sum_shipqty > 0
AND @Kount <= @Row_Kount
BEGIN
SELECT @E_REQUIRED_QTY = REQUIRED_QTY
,@E_SNR_STATUS = SNR_STATUS
,@E_ROWID = RowID
FROM @Temp_Table
WHERE ID = @Kount
IF @sum_shipqty < @E_REQUIRED_QTY
BEGIN
UPDATE ORDER_ITEMS_STAGE
SET REQUIRED_QTY = @sum_shipqty
,SNR_STATUS = 'ATS'
WHERE ROWID = @E_ROWID
SET @sum_shipqty = @E_REQUIRED_QTY - @sum_shipqty
INSERT INTO ORDER_ITEMS_STAGE (list OF columns)
SELECT list OF columns
FROM ORDER_ITEMS_STAGE
WHERE ROWID = @E_ROWID
SET @sum_shipqty = 0
END
ELSE
BEGIN
UPDATE ORDER_ITEMS_STAGE
SET SNR_STATUS = 'ATS'
WHERE ROWID = @E_ROWID
IF @sum_shipqty = @E_REQUIRED_QTY
BEGIN
SET @sum_shipqty = 0
END
ELSE -- @sum_shipqty > @E_REQUIRED_QTY
BEGIN
SET @sum_shipqty = @sum_shipqty - @E_REQUIRED_QTY
END
END
SET @Kount = @Kount + 1
END
SET @ATS_Kount = @ATS_Kount + 1
DELETE @Temp_Table
END
END TRY
BEGIN CATCH
DECLARE @ErrorMsg VARCHAR(MAX)
,@ErrorNumber INT
,@ErrorProc SYSNAME
,@ErrorLine INT
SELECT @ErrorMsg = ERROR_MESSAGE()
,@ErrorNumber = ERROR_NUMBER()
,@ErrorProc = ERROR_PROCEDURE()
,@ErrorLine = ERROR_LINE();
PRINT @ErrorMsg
PRINT CAST(@ErrorNumber AS VARCHAR) + ' ' + CAST(@ErrorProc AS VARCHAR) + ' ' + CAST(@ErrorLine AS VARCHAR)
PRINT @ATS_ITEM_NO + ' ' + @ATS_CUSTNO + ' ' + CAST(GetDate() AS VARCHAR)
END CATCH
END
Without any idea what this is supposed to be doing or ddl or sample data we don't have a whole lot to work with here. Please take a few minutes and read the article found by following the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2014 at 1:11 pm
thank you for making the stored procedure more readable
here's what the procedure does:
it takes a file of Available to Ship
with ItemNo CUSTNO and qty
then matches to another table (I renamed it to disguise my client - I think I called it MasterOrder or something like that) on ItemNo and Custno
then if it finds a match it marks the column SNR_STATUS with ATS
for rows until the qty from the ATS table is all used up,
reducing the running amount by REQUIRED_QTY
if the REQUIRED_QTY on the last row is greater than the remaining ATS_QTY
then it splits the Master Order table,
writing out a record with remainder with a blank SNR_QTY
instead of cursor(s) I pump my two tables into table variables with identity columns
and iterate through using WHILE loops
March 12, 2014 at 1:27 pm
Seggerman-675349 (3/12/2014)
thank you for making the stored procedure more readablehere's what the procedure does:
it takes a file of Available to Ship
with ItemNo CUSTNO and qty
then matches to another table (I renamed it to disguise my client - I think I called it MasterOrder or something like that) on ItemNo and Custno
then if it finds a match it marks the column SNR_STATUS with ATS
for rows until the qty from the ATS table is all used up,
reducing the running amount by REQUIRED_QTY
if the REQUIRED_QTY on the last row is greater than the remaining ATS_QTY
then it splits the Master Order table,
writing out a record with remainder with a blank SNR_QTY
instead of cursor(s) I pump my two tables into table variables with identity columns
and iterate through using WHILE loops
While loops are nothing but a cursor without any of the settings. 😉 Tough moving from Oracle where cursors are pretty fast to sql server where they are simply awful.
There is no chance I have of helping with nothing to work with. I can't possibly write any code against tables that I don't have. This can absolutely be done in a set based manner but without ddl and some sample data it is nearly impossible.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2014 at 6:33 pm
I'll second what Sean has said, but I wanted to add something.
It is good that you have recognized that the problem is the loops and cursors. And that a set-based solution is what you'll need to speed it up.
Now monitoring this thread so in case you can provide the DDL and sample data, maybe I too can help.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 13, 2014 at 3:57 am
At least try to get rid of inner loop
WHILE @sum_shipqty > 0.
1.Add running total column say RT to @Temp_Table.
2.After inserting to @Temp_Table compute RT. @Temp_Table is under your total control, so you can easily meet all the conditions and use "Quirky Update", see Jeff Moden's article http://qa.sqlservercentral.com/articles/T-SQL/68467/
3.UPDATE ORDER_ITEMS_STAGE
SET ...
,REQUIRED_QTY = CASE WHEN tmp.RT < @sum_shipqty
THEN REQUIRED_QTY ELSE tmp.RT - @sum_shipqty END
...
JOIN @Temp_Table tmp
ON ... AND (tmp.RT-tmp.REQUIRED_QTY < @sum_shipqty )
4. INSERT ORDER_ITEMS_STAGE ...
SELECT ...
FROM ORDER_ITEMS_STAGE
JOIN @Temp_Table tmp
ON ... AND (tmp.RT-tmp.REQUIRED_QTY < @sum_shipqty )
AND (tmp.RT > @sum_shipqty )
Rgds,
Serg
March 13, 2014 at 6:03 am
the real AVAILATS has 40K records - and the real ORDER_ITEMS_STAGE 3 million records
CREATE TABLE AVAILATS
(ITEM_NO VARCHAR(30) NULL,
CUSTNO CHAR(6) NULL,
OPN_QTY INT NULL)
INSERT AVAILATS VALUES('PARTNO001','000050',6)
INSERT AVAILATS VALUES('PARTNO002','000050',4)
INSERT AVAILATS VALUES('PARTNO003','000050',2)
INSERT AVAILATS VALUES('PARTNO004','002999',8)
CREATE TABLE ORDER_ITEMS_STAGE
(ITEM_NO VARCHAR(30) NULL,
CUSTNO CHAR(6) NULL,
REQUIRED_QTY INT,
SNR_STATUS VARCHAR(3),
RowID BIGINT IDENTITY(1,1))
INSERT INTO ORDER_ITEMS_STAGE VALUES ('PARTNO001','000050',2,'')
INSERT INTO ORDER_ITEMS_STAGE VALUES ('PARTNO001','000050',2,'')
INSERT INTO ORDER_ITEMS_STAGE VALUES ('PARTNO001','000050',4,'')
INSERT INTO ORDER_ITEMS_STAGE VALUES ('PARTNO002','000050',4,'')
INSERT INTO ORDER_ITEMS_STAGE VALUES ('PARTNO004','002999',4,'')
March 13, 2014 at 6:45 am
Noted REQUIRED_DATE is missing which defines the order of servicing.
March 13, 2014 at 7:12 am
right you are
CREATE TABLE AVAILATS
(ITEM_NO VARCHAR(30) NULL,
CUSTNO CHAR(6) NULL,
OPN_QTY INT NULL)
INSERT AVAILATS VALUES('PARTNO001','000050',6)
INSERT AVAILATS VALUES('PARTNO002','000050',4)
INSERT AVAILATS VALUES('PARTNO003','000050',2)
INSERT AVAILATS VALUES('PARTNO004','002999',8)
CREATE TABLE ORDER_ITEMS_STAGE
(ITEM_NO VARCHAR(30) NULL,
CUSTNO CHAR(6) NULL,
REQUIRED_DATE DATE NULL,
DPAS_RTNG CHAR(4) NULL,
REQUIRED_QTY INT,
SNR_STATUS VARCHAR(3),
RowID BIGINT IDENTITY(1,1))
INSERT INTO ORDER_ITEMS_STAGE
VALUES ('PARTNO001','000050','04/01/2014','',2,'')
INSERT INTO ORDER_ITEMS_STAGE
VALUES ('PARTNO001','000050','05/01/2014','RBAR',2,'')
INSERT INTO ORDER_ITEMS_STAGE
VALUES ('PARTNO001','000050','05/01/2014','DTOA',4,'')
INSERT INTO ORDER_ITEMS_STAGE
VALUES ('PARTNO002','000050','04/01/2014','',4,'')
INSERT INTO ORDER_ITEMS_STAGE
VALUES ('PARTNO004','002999','04/01/2014','',4,'')
March 13, 2014 at 7:57 am
We are getting close to an understanding. We now have the two tables (AVAILATS and ORDER_ITEMS_STAGE).
Can you explain what should happen here. I think that what you want for Item_No PARTNO0001 is to update the SNR_STATUS to ATS for ROWIDs 1 and 2? I am not totally clear what happens with the remaining 2. Do we update ROWID3 and change the quantity to 2 and mark is at ATS as well as create a new row with a qty of 2 and status is empty?
Also, do you do anything with the rows in AVAILATS? Do we update the OPN_QTY? If you could provide what these two tables should look like after this process runs and the business logic behind it we can help you figure this out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2014 at 8:52 am
after the run
ORDER_ITEMS_STAGE
VALUES ('PARTNO001','000050','04/01/2014','',2,'ATS')
ORDER_ITEMS_STAGE
VALUES ('PARTNO001','000050','05/01/2014','RBAR',2,'ATS')
ORDER_ITEMS_STAGE
VALUES ('PARTNO001','000050','05/01/2014','DTOA',2,'ATS')
ORDER_ITEMS_STAGE
VALUES ('PARTNO001','000050','05/01/2014','DTOA',2,'') - inserted
ORDER_ITEMS_STAGE
VALUES ('PARTNO002','000050','04/01/2014','',4,'ATS')
ORDER_ITEMS_STAGE
VALUES ('PARTNO004','002999','04/01/2014','',4,'ATS')
note no matching Order Item for PART003 - this is OK
March 13, 2014 at 9:13 am
I want to thank you guys (you are guys, right?) for all your help. I've got the running total working, now gotta get the rest of it working. I probably need to delete from @test_table rows above the first row where running total >= ship qty
March 13, 2014 at 9:16 am
Seggerman-675349 (3/13/2014)
I want to thank you guys (you are guys, right?) for all your help. I've got the running total working, now gotta get the rest of it working. I probably need to delete from @test_table rows above the first row where running total >= ship qty
Does your code have a cursor or a loop? If so, it isn't going to be any better.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2014 at 11:18 am
no, this stored procedure is running as part of a larger job run as part of a batch at night
March 13, 2014 at 12:49 pm
Seggerman-675349 (3/13/2014)
no, this stored procedure is running as part of a larger job run as part of a batch at night
OK. So do you still need help? If so, what do you need?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply