August 20, 2012 at 8:06 am
I've inherited some very slow code in a stored procedure that uses cursor logic to read a table of purchase order receipts, check for the existing highest id in a key cross reference table, create new key with highest found key + 1 or start at 5000 for new. It then inserts the read record into the key cross reference table and fetches another row.
This runs for hours and I'm not quite sure how to convert to something faster. I changed it to an insert which was fast but I forgot about having to check for an existing row first. I've done some sql but not like you guys.
*** tables used ***
[dbo].[xx_ordid_tracker](
[id] [int] IDENTITY(1,1) NOT NULL,
[poid] [nchar](50) NULL,
[recptkey] [nchar](50) NULL,
[ord_id] [int] NULL,
[datecreated] [datetime] NULL,
[lineseq] [nchar](50) NULL
[dbo].[XX_PM_RECEIPTS](
[ENVCD] [varchar](1) NOT NULL,
[ORDNO] [varchar](7) NOT NULL,
[ITNBR] [varchar](15) NOT NULL,
[HOUSE] [varchar](3) NOT NULL,
[TRQTY] [varchar](15) NOT NULL,
[TRNDAT] [varchar](6) NOT NULL,
[REFNO] [varchar](10) NOT NULL,
[LGWNO] [varchar](26) NOT NULL,
[FISYR] [varchar](2) NOT NULL,
[FISPD] [varchar](2) NOT NULL,
[ORD_ODA_ID] [varchar](100) NOT NULL,
[ID] [numeric](18, 0) NULL,
[FK_ID] [varchar](50) NULL,
[TOKEN] [nvarchar](50) NULL,
[ORD_ID] [int] NULL,
[POISEQ] [varchar](50) NULL,
[LBHNO] [varchar](50) NULL
*** original slow code ***
DECLARE @poid NVARCHAR(20)
DECLARE @token NVARCHAR(30)
DECLARE @lineid INT
DECLARE db_cursor CURSOR FOR
SELECT ord_oda_id, token
FROM xx_pm_receipts
WHERE token not in (select recptkey from xx_ordid_tracker)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @poid, @token
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @lineid = coalesce((select max(ord_id) +1
from xx_ordid_tracker t where poid = @poid),5000)
Insert into xx_ordid_tracker (poid, recptkey, ord_id)
Values (@poid, @token, @lineid)
FETCH NEXT FROM db_cursor INTO @poid, @token
*** My attempt - it doesn't take into account an existing row & increment that **
Insert into xx_ordid_tracker (poid, recptkey, ord_id)
(SELECT ord_oda_id, token, cast(ROW_NUMBER() over(PARTITION by ord_oda_id order by ord_oda_id)as integer) +4999
FROM xx_pm_receipts
WHERE token not in (select recptkey from dbo.xx_ordid_tracker))
August 20, 2012 at 8:37 am
Try the following:
SELECT ord_oda_id, token, ISNULL(ord_id, 5000) AS ord_id
FROM xx_pm_receipts
OUTER APPLY (
SELECT TOP (1) ord_id + 1 AS ord_id
FROM xx_ordid_tracker
WHERE poid = ord_oda_id
ORDER BY ord_id DESC ) AS ot
WHERE token not in (select recptkey from xx_ordid_tracker)
It's possible that you may get a more efficient query using a CTE with ROW_NUMBER(), but I don't have the time to test it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 20, 2012 at 11:10 am
are there any other ways of accomplishing this?
August 20, 2012 at 11:34 am
Dave C. (8/20/2012)
are there any other ways of accomplishing this?
Why do you ask? Is there something wrong with the proposed solution, such as your neglecting to tell us that you were running on SQL2K which doesn't have CROSS APPLY? You could always keep the existing cursor.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 20, 2012 at 11:40 am
Just curious. There's always more than one way to do things and you can learn from each approach.
August 20, 2012 at 11:49 am
You could simply expand your first attempt, assuming if only need to have the starting point fixed:
Insert into xx_ordid_tracker (poid, recptkey, ord_id)
(SELECT ord_oda_id, token, cast(ROW_NUMBER() over(PARTITION by ord_oda_id order by ord_oda_id)as integer) +isnull(ordmax.lastOrd,4999 )
FROM xx_pm_receipts
left outer join ( select poid, max(ord_id) lastOrd from xx_ordid_tracker group by poid) OrdMax
on xx_pm_receipts.ord_oda_id=ordmax.poid
WHERE token not in (select recptkey from dbo.xx_ordid_tracker))
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 20, 2012 at 12:05 pm
This could work, but I don't have sample data to test with. I'm not sure about performance but it's 2000 friendly 😛
INSERT INTO XX_ordid_tracker (
poid,
recptkey,
ord_id)
SELECT r.ord_oda_id,
r.token,
ISNULL( MAX( t.ord_id) + 1, 5000)
FROM XX_pm_receipts r
LEFT
JOIN XX_ordid_tracker t ON r.ord_oda_id = t.poid
WHERE r.token NOT IN( SELECT x.recptkey FROM XX_ordid_tracker x)
GROUP BY r.ord_oda_id, r.token
EDIT: Matt posted something similar to mine, just better 😉 I might have made a bad assumption.
August 20, 2012 at 12:15 pm
I'm on SQLServer 2008, sorry I forgot to mention that.
I appreciate ALL the replies. I've learned something from each.
Your solutions provided what I needed to complete my task and bring a six plus hour job down to 2-3 seconds.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply