Nasty cursors - 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

Transact-SQL (2000)

Nasty cursors

Author

Topic

reenz
Starting Member

29 Posts

Posted - 2006-04-05 : 01:49:16

Cursors! Hate them! Its taking a damn long time to run this stored procedure on a table with 2million records. can anyone help me think of a better way? there's no unique id and im trying to assign an id to it by comparing it with another table . If there is a match, depending on their gen_id, store part of their data in different tables. Or anyway to split them into smaller batches for process, commit them then move on...

CREATE PROCEDURE [dbo].[sp_convertCheckpoint] AS

SET NOCOUNT ON

DECLARE @gen_key int, @gen_awb numeric, @gen_id smallint, @gen_date datetime,
@gen_time varchar(20), @gen_isogmt smallint, @gen_stn char(3), @gen_sub char(3),
@gen_cnum char(4), @gen_rte char(6), @gen_cycl char(2), @gen_ckpt varchar(100),
@gen_from char(10), @gen_svsn smallint, @gen_cvsn smallint, @gen_dtmadd int,
@gen_dtmfl int, @sscode varchar(3), @sd_id int, @del_datetime datetime, @count int

DECLARE read_cursor CURSOR FOR
SELECT gen_key, gen_awb, gen_id, gen_date, gen_time, gen_isogmt, gen_stn, gen_sub,
gen_cnum, gen_rte, gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd,
gen_dtmfl
FROM dbo.checkpoint_hold

OPEN read_cursor

FETCH NEXT FROM read_cursor
INTO @gen_key, @gen_awb, @gen_id, @gen_date, @gen_time, @gen_isogmt, @gen_stn,
@gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,@gen_from, @gen_svsn,
@gen_cvsn, @gen_dtmadd, @gen_dtmfl

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sd_id = sd_id
FROM [ship_detail_hold]
WHERE [sd_awb] = @gen_awb
IF (@sd_id IS NULL)
BEGIN
INSERT INTO [dbo].[checkpoint_unknown] (gen_key, gen_awb, gen_id, gen_date,
gen_time, gen_isogmt, gen_stn, gen_sub, gen_cnum, gen_rte,
gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd,
gen_dtmfl)

VALUES(@gen_key, @gen_awb, @gen_id, @gen_date, @gen_time, @gen_isogmt,
@gen_stn, @gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,
@gen_from, @gen_svsn, @gen_cvsn, @gen_dtmadd, @gen_dtmfl)

IF(@@ERROR <>0 )
RAISERROR ('INSERT INTO CHECKPOINT_UNKNOWN FAILED FOR RECORD GEN_KEY: %d WITH ERROR NO %d.', 16, 1, @gen_key, @@ERROR)

END
ELSE

BEGIN

IF (@gen_id='40') OR (@gen_id ='20') OR (@gen_id = '42')
BEGIN
SELECT @sscode = SUBSTRING(@gen_ckpt,7,2)
END

ELSE
BEGIN
SELECT @sscode = status_code
FROM [checkpoint_code]
WHERE [id] = @gen_id
END

IF (@gen_id = '42' AND @sscode='DF')
BEGIN
exec sp_Txt_Split @gen_ckpt,@gen_awb,@sd_id
END

IF (@gen_time = '01/01/1200')
BEGIN
SELECT @del_datetime = NULL
END
ELSE
BEGIN
SELECT @del_datetime =(@gen_date + ' ' + convert(datetime,@gen_time,108))
END
IF (@gen_id ='18')
BEGIN
exec sp_checkpointDelivered @del_datetime, @gen_awb, @sd_id, @gen_ckpt
END

IF (@gen_id='40')
BEGIN
exec sp_checkpointDelivered @del_datetime, @gen_awb, @sd_id, NULL
END

INSERT INTO [dbo].[checkpoints] (gen_key, gen_awb, gen_id, gen_date,
gen_time, gen_datetime, gen_isogmt, gen_stn, gen_sub, gen_cnum, gen_rte,
gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd,
gen_dtmfl, sd_id, scode)
VALUES(@gen_key, @gen_awb, @gen_id,
@gen_date, @gen_time,@del_datetime, @gen_isogmt,
@gen_stn, @gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,
@gen_from, @gen_svsn, @gen_cvsn, @gen_dtmadd, @gen_dtmfl, @sd_id,@sscode)

IF(@@ERROR <>0 )
RAISERROR ('INSERT INTO CHECKPOINTS FAILED FOR RECORD SD_ID: %d WITH ERROR NO %d.', 16, 1, @sd_id, @@ERROR)

END
FETCH NEXT FROM read_cursor
INTO @gen_key, @gen_awb, @gen_id, @gen_date, @gen_time, @gen_isogmt,
@gen_stn, @gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,
@gen_from, @gen_svsn, @gen_cvsn, @gen_dtmadd, @gen_dtmfl
SELECT @COUNT = @@ROWCOUNT

END
CLOSE read_cursor
DEALLOCATE read_cursor
GO

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-05 : 05:43:50

Break it down into smaller components.
As a start your 1st insert should/could look like....
After that you are looking at incorporating CASE statements into some of the other SELECT/INSERT statements.

INSERT INTO [dbo].[checkpoint_unknown] (gen_key, gen_awb, gen_id, gen_date, gen_time, gen_isogmt, gen_stn, gen_sub, gen_cnum, gen_rte, gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd, gen_dtmfl)
SELECT gen_key, gen_awb, gen_id, gen_date, gen_time, gen_isogmt, gen_stn, gen_sub,
gen_cnum, gen_rte, gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd,
gen_dtmfl
FROM dbo.checkpoint_hold
LEFT JOIN [ship_detail_hold] on [sd_awb] = gen_awb
WHERE sd_id IS NULL

Posting some sample input data, and matching expected results, as well as the DDL for the tables involved in the process will make it easier for you to get answers from us.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources