Can I do this another way.. - 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)

Can I do this another way..

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-10 : 06:44:08

Hello,

I have this script that is beginning to take up to much time. So I hope that this can be done faster. Maybe by using a join or update with complex select statemant??? I don't know!
All tips are welcome.....

/*** This is the procedure ***/

ALTER PROCEDURE [SP_UpdateSSCCStatus]

@LockedNr as int

AS

/*Used to update a series of SSCC when locking a whole serie*/

DECLARE @SSCC varchar(20), @LOCKED_NR int, @LOCKED_CODE tinyint

DECLARE SSCC_Cursor CURSOR for
SELECT INFO_SSCC.SSCC, INFO_LOCKED.LOCKED_NR, INFO_LOCKED.LOCKED_CODE
FROM INFO_LOCKED, INFO_SSCC
Where INFO_LOCKED.LOCKED_NR = @LockedNr and
(INFO_SSCC.SSCC_STATUS = 1 OR INFO_SSCC.SSCC_STATUS = 5 OR INFO_SSCC.SSCC_STATUS = 6)
and INFO_LOCKED.LIJN_NR = INFO_SSCC.LINE_ID
AND( (INFO_SSCC.PRODUCTION_DATE >= INFO_LOCKED.START_DATE AND INFO_SSCC.PRODUCTION_DATE <= INFO_LOCKED.END_DATE )
OR (INFO_SSCC.PROD_DATE_LAST >= INFO_LOCKED.START_DATE AND INFO_SSCC.PROD_DATE_LAST <= INFO_LOCKED.END_DATE ) )
order by INFO_SSCC.SSCC

OPEN SSCC_Cursor

FETCH NEXT from SSCC_Cursor
into @SSCC, @LOCKED_NR, @LOCKED_CODE

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE INFO_SSCC
SET
SSCC_STATUS = @LOCKED_CODE,
LOCKED_NR = @LOCKED_NR

WHERE SSCC = @SSCC

FETCH NEXT from SSCC_Cursor
into @SSCC, @LOCKED_NR, @LOCKED_CODE

END

CLOSE SSCC_Cursor
DEALLOCATE SSCC_Cursor

/*** End of Procedure ***/

There are two tables involved, Info_Locked and Info_SSCC

/*** Info tables ***/

CREATE TABLE [dbo].[INFO_SSCC] (
[SSCC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ITEM_CODE] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHIPMENTNR] [int] NULL ,
[CASECOUNT] [int] NULL ,
[TOT_WEIGHT_PALLET] [decimal](10, 4) NULL ,
[BEST_BEFORE_DATE] [datetime] NULL ,
[PRODUCTION_DATE] [datetime] NULL ,
[LINE_ID] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOG_DATE] [datetime] NULL ,
[READ_STATUS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VERIFIED] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LANDCODE] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BESTEMMINGSCODE] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSCC_STATUS] [tinyint] NULL ,
[PROD_DATE_LAST] [datetime] NULL ,
[LOCKED_NR] [int] NULL ,
[SHIPPED] [tinyint] NULL ,
[RECORD_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[INFO_LOCKED] (
[LOCKED_NR] [int] NOT NULL ,
[LIJN_NR] [tinyint] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[DESCRIPTION] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCKED_CODE] [tinyint] NULL ,
[SKEPCODE] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

/*** End info Tables ***/

MakeYourDaddyProud

184 Posts

Posted - 2003-10-10 : 07:11:44

-- Try this

declare @lockednr int

-- set your @lockednr variable

-- might need a bot of tweaking

UPDATE INFO_SSCC
SET SSCC_STATUS = A.LOCKED_CODE,
LOCKED_NR = A.LOCKED_NR
FROM INFO_SSCC I
JOIN (
SELECT INFO_SSCC.SSCC, INFO_LOCKED.LOCKED_NR, INFO_LOCKED.LOCKED_CODE
FROM INFO_LOCKED, INFO_SSCC
Where INFO_LOCKED.LOCKED_NR = @LockedNr and
(INFO_SSCC.SSCC_STATUS = 1 OR INFO_SSCC.SSCC_STATUS = 5 OR INFO_SSCC.SSCC_STATUS = 6)
and INFO_LOCKED.LIJN_NR = INFO_SSCC.LINE_ID
AND( (INFO_SSCC.PRODUCTION_DATE >= INFO_LOCKED.START_DATE AND INFO_SSCC.PRODUCTION_DATE <= INFO_LOCKED.END_DATE )
OR (INFO_SSCC.PROD_DATE_LAST >= INFO_LOCKED.START_DATE AND INFO_SSCC.PROD_DATE_LAST <= INFO_LOCKED.END_DATE ) )
) as A
ON A.SSCC = I.SSCC

Daniel Small MIAP
www.danielsmall.com IT Factoring

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-10-10 : 07:14:03

Not sure that you need a cursor to do this, you could really just use an UPDATE statement with the same WHERE clause.

Also do you think the reason it is starting to increase the time it takes due to the fact you may be updating records that have already been updated and don't need done again? i.e. where you are setting SSCC_STATUS and LOCKED_NR to the variable values, they may already have those values? To eliminate this, add another WHERE clause along the lines of 'AND SSCS_STATUS <> INFO_LOCKED.LOCKED_CODE AND LOCKED_NR <> INFO_LOCKED.LOCKED_NR'

Hope this helps

Raymond

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-10 : 07:24:26

It's early and I haven't had my coffee yet. Here's a WAG

UPDATE A

SET SSCC_STATUS = @LOCKED_CODE, LOCKED_NR = @LOCKED_NR

FROM INFO_SSCC A, INFO_LOCKED B

WHERE B.LOCKED_NR = @LockedNr

AND (A.SSCC_STATUS IN (1, 5, 6))

AND B.LIJN_NR = A.LINE_ID

AND( (A.PRODUCTION_DATE >= B.START_DATE AND A.PRODUCTION_DATE <= B.END_DATE )

OR (A.PROD_DATE_LAST >= B.START_DATE AND A.PROD_DATE_LAST <= B.END_DATE ) )

Don't know about this.

Sam

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-10-10 : 09:04:59

Thanks guys for the swift response...

One of the reasons it is getting slow, could it be the number of records in the table (416656)?

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-10 : 09:07:16

No. That's just a tiny bit of records.

I'll bet if you rewrite it as a set-based update, it'll run in a blink of an eye.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-10 : 14:41:55

How does this look?


    UPDATE s
       SET  SSCC_STATUS = l.LOCKED_CODE
	  , LOCKED_ND   = l.LOCKED_CODE 
      FROM INFO_LOCKED l
INNER JOIN INFO_SSCC s
	ON l.LIJN_NR = s.LINE_ID 
     WHERE l.LOCKED_NR = @LockedNr 
       AND s.SSCC_STATUS IN (1, 5, 6)
       AND(     (s.PRODUCTION_DATE >= l.START_DATE AND s.PRODUCTION_DATE <= l.END_DATE ) 
	     OR (s.PROD_DATE_LAST  >= l.START_DATE AND s.PROD_DATE_LAST  <= l.END_DATE ) 
	  )

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources