May 22, 2003 at 2:18 pm
I have written this code, and it works, but it slow, and locking up a table so everything go's slow..
Here is the code
/****** Object: Stored Procedure dbo.SP_UpdateWorkPlan Script Date: 2002-09-18 18:11:24 ******/
ALTER PROCEDURE [SP_UpdateWorkPlan] AS
-- dit is de actuele job running op SQL
Declare @Item varchar(20),
@Lijn varchar (2),
@Land varchar (4),
@Bestemming varchar(4),
@AantalPlan decimal(6,0),
@AantalWerkelijk decimal(6,0),
@SeqNr decimal(18,0)
/*First we select the actual date, and get the actual week, period and year back*/
Declare PLAN_Cursor CURSOR for
SELECT ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN,SEQNR
from INFO_ARTIKEL_PLAN
WHERE
(YEAR = (select mfyear from INFO_NEW_MFPERIODE where MFDag = convert(varchar(10),getdate(),120))
and period = (select mfperiod from INFO_NEW_MFPERIODE where MFDag = convert(varchar(10),getdate(),120))
and week = (select mfweeknr from INFO_NEW_MFPERIODE where MFDag = convert(varchar(10),getdate(),120)) )
order by seqnr
OPEN PLAN_Cursor
Fetch NEXT FROM PLAN_Cursor
INTO @Item, @Lijn, @Land,@Bestemming, @AantalPlan, @SeqNr
WHILE @@FETCH_STATUS = 0
BEGIN
/*We check to see if record already excists,
if NO we insert the record
if YES we update the excisting record*/
IF EXISTS (SELECT ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN,SEQNR from INFO_WORK_PLAN WHERE SEQNR = @SeqNr)
UPDATE INFO_WORK_PLAN
SET AANTALPLAN = @AantalPlan WHERE SEQNR = @SeqNr
ELSE
INSERT INTO INFO_WORK_PLAN (ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN, AANTALWERKELIJK,SEQNR)
VALUES ( @Item, @Lijn, @Land,@Bestemming, @AantalPlan,0, @SeqNr)
Fetch NEXT FROM PLAN_Cursor
INTO @Item, @Lijn, @Land,@Bestemming, @AantalPlan, @SeqNr
END
CLOSE PLAN_Cursor
DEALLOCATE PLAN_Cursor
/*Then we go 7 days back and get plan for the previous week, period, year */
Declare PLAN_Cursor CURSOR for
SELECT ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN,SEQNR
from INFO_ARTIKEL_PLAN
WHERE
(YEAR = (select mfyear from INFO_NEW_MFPERIODE where MFDag = convert(varchar(10),getdate()-7,120))
and period = (select mfperiod from INFO_NEW_MFPERIODE where MFDag = convert(varchar(10),getdate()-7,120))
and week = (select mfweeknr from INFO_NEW_MFPERIODE where MFDag = convert(varchar(10),getdate()-7,120)) )
order by seqnr
OPEN PLAN_Cursor
Fetch NEXT FROM PLAN_Cursor
INTO @Item, @Lijn, @Land,@Bestemming, @AantalPlan, @SeqNr
WHILE @@FETCH_STATUS = 0
BEGIN
/*If any of these records excist then we delete then, since out of date*/
IF EXISTS (SELECT ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN,SEQNR from INFO_WORK_PLAN WHERE SEQNR = @SeqNr)
DELETE FROM INFO_WORK_PLAN WHERE SEQNR = @SeqNr
Fetch NEXT FROM PLAN_Cursor
INTO @Item, @Lijn, @Land,@Bestemming, @AantalPlan, @SeqNr
END
CLOSE PLAN_Cursor
DEALLOCATE PLAN_Cursor
/*Next we delete all records which no longer exists in the info_artikel_plan*/
DELETE FROM info_work_plan WHERE NOT EXISTS (SELECT * FROM info_artikel_plan WHERE info_artikel_plan.seqnr = Info_work_plan.seqnr )
May 22, 2003 at 2:40 pm
Wow. Where do we start.
1) Don't use cursors. This procedure can be rewritten using standard SET-based SQL commands. First, do the update on current date records where they match on the correct criteria (sequence number). Then do the insert for the records which do not match. Then do an update for the 7 days back batch. Then lastly insert the remaining records. I will try an example at the end of this post (although it's tough since it's not really in english so I might guess at a few things)
2) Don't use SP_ as the name of the procedure. Not a huge deal, but you should avoid this naming convention.
3) Get rid of initial SELECT without SUB-SELECTS in WHERE clause...you can put these into temporary variables
OK, here goes:
CREATE PROCEDURE UpdateWorkPlan AS
DECLARE
@Item VARCHAR(20)
, @Lijn VARCHAR (2)
, @Land VARCHAR (4)
, @Bestemming VARCHAR(4)
, @AantalPlan DECIMAL(6,0)
, @AantalWerkelijk DECIMAL(6,0)
, @SeqNr DECIMAL(18,0)
, @TempYear VARCHAR(4)-- Might be wrong datatype...
, @TempPeriod VARCHAR(10)-- Might be wrong datatype...
, @TempWeek VARCHAR(10)-- Might be wrong datatype...
/* Get temp variables from today's date */
SELECT
@TempYear = mfyear
, @TempPeriod = mfperiod
, @TempWeek = mfweek
FROMINFO_NEW_MFPERIODE
WHEREMFDag = CONVERT(VARCHAR(10),GETDATE(),120))
/* Update today's date matches and other criteria */
UPDATE WP
SET WP.AANTALPLAN = INFO_ARTIKEL_PLAN.AANTALPLAN
FROM INFO_WORK_PLAN WP
INNER JOIN INFO_ARTIKEL_PLAN
ON WP.SEQNR = INFO_ARTIKEL_PLAN.SEQNR
WHERE
INFO_ARTIKEL_PLAN.[YEAR] = @TempYear
AND INFO_ARTIKEL_PLAN.[period] = @TempPeriod
AND INFO_ARTIKEL_PLAN.[week] = @TempWeek
/* Next do inserts for today's date batch */
INSERT INTO INFO_WORK_PLAN
(ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN, AANTALWERKELIJK,SEQNR)
SELECT
INFO_ARTIKEL_PLAN.ITEM
, INFO_ARTIKEL_PLAN.LIJN
, INFO_ARTIKEL_PLAN.LAND
, INFO_ARTIKEL_PLAN.BESTEMMING
, INFO_ARTIKEL_PLAN.AANTALPLAN
, INFO_ARTIKEL_PLAN.AANTALWERKELIJK
, INFO_ARTIKEL_PLAN.SEQNR
FROM INFO_WORK_PLAN WP
RIGHT JOIN INFO_ARTIKEL_PLAN
ON WP.SEQNR = INFO_ARTIKEL_PLAN.SEQNR
WHERE
INFO_ARTIKEL_PLAN.[YEAR] = @TempYear
AND INFO_ARTIKEL_PLAN.[period] = @TempPeriod
AND INFO_ARTIKEL_PLAN.[week] = @TempWeek
ANDWP.SEQNR IS NULL
/* reset temp variables from 7 days ago */
SELECT
@TempYear = mfyear
, @TempPeriod = mfperiod
, @TempWeek = mfweek
FROMINFO_NEW_MFPERIODE
WHEREMFDag = CONVERT(VARCHAR(10),GETDATE() - 7,120))
/* Update today's date matches and other criteria */
UPDATE WP
SET WP.AANTALPLAN = INFO_ARTIKEL_PLAN.AANTALPLAN
FROM INFO_WORK_PLAN WP
INNER JOIN INFO_ARTIKEL_PLAN
ON WP.SEQNR = INFO_ARTIKEL_PLAN.SEQNR
WHERE
INFO_ARTIKEL_PLAN.[YEAR] = @TempYear
AND INFO_ARTIKEL_PLAN.[period] = @TempPeriod
AND INFO_ARTIKEL_PLAN.[week] = @TempWeek
/* Next do inserts for today's date batch */
INSERT INTO INFO_WORK_PLAN
(ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN, AANTALWERKELIJK,SEQNR)
SELECT
INFO_ARTIKEL_PLAN.ITEM
, INFO_ARTIKEL_PLAN.LIJN
, INFO_ARTIKEL_PLAN.LAND
, INFO_ARTIKEL_PLAN.BESTEMMING
, INFO_ARTIKEL_PLAN.AANTALPLAN
, INFO_ARTIKEL_PLAN.AANTALWERKELIJK
, INFO_ARTIKEL_PLAN.SEQNR
FROM INFO_WORK_PLAN WP
RIGHT JOIN INFO_ARTIKEL_PLAN
ON WP.SEQNR = INFO_ARTIKEL_PLAN.SEQNR
WHERE
INFO_ARTIKEL_PLAN.[YEAR] = @TempYear
AND INFO_ARTIKEL_PLAN.[period] = @TempPeriod
AND INFO_ARTIKEL_PLAN.[week] = @TempWeek
ANDWP.SEQNR IS NULL
/*Next we delete all records which no longer exists in the info_artikel_plan*/
DELETE FROM info_work_plan WHERE NOT EXISTS (SELECT * FROM info_artikel_plan WHERE info_artikel_plan.seqnr = Info_work_plan.seqnr )
Oh, and I would highly recommend putting a transaction around the whole thing... 🙂
Hope this helps,
Jay
Edited by - jpipes on 05/22/2003 2:42:08 PM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply