Re: Replace Cursor Procedure with Update Query?
From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 02/19/04
- Next message: Jacco Schalkwijk: "Re: question for memory leak?"
- Previous message: Dejan Sarka: "Re: SQL Server Client"
- In reply to: Peter Bellis: "Replace Cursor Procedure with Update Query?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Feb 2004 14:21:32 +0530
1.) Can I replace the below cursor based procedure with an update
> query? How?
Yes. See below script.
DECLARE @tmpResults TABLE(Case_Id VARCHAR(15), Create_Date Int, Seq int)
INSERT INTO @tmpResults
SELECT Case_Id, Create_Date,
(SELECT Count(*) FROM #tmpSAS_Assignment_History T2
WHERE T1.Case_Id = T2.Case_Id AND T1.Create_Date >= T2.Create_Date)
FROM #tmpSAS_Assignment_History T1
WHERE T1.Sequence Is Null
ORDER BY Case_Id, Create_Date
--SELECT * FROM @tmpResults
UPDATE SAH
SET SAH.[Sequence] = tmp.[Seq]
FROM #tmpSAS_Assignment_History SAH
INNER JOIN @tmpResults tmp
ON SAH.Case_Id = TMP.Case_Id AND SAH.Create_Date = TMP.Create_Date
--
Roji. P. Thomas
SQL Server Programmer
"Peter Bellis" <anonymous@discussions.microsoft.com> wrote in message
news:069AD981-3CA5-49F2-8F82-70496C67B991@microsoft.com...
> Hi all,
>
> I have a few questions for the gurus.
>
> Short version:
> 1.) Can I replace the below cursor based procedure with an update
> query? How?
> If not ...
> 2.) I am Declaring / Deallocating one cursor inside a loop. Would it
> be better to move the Allocation/Deallocation outside of the
> loop? What about the variable in the Declaration?
> 3.) Any obvious bloopers/improvements?
>
> Long version:
> I have a table which is updated once a day from a live database at
> midnight. This table contains a History of how problem tickets have
> been assigned. In order for a 3rd Party app (Webfocus) to report off
> it, I have been asked to create a field "Sequence" to show in which
> order the assignments took place. Every morning, after the update,
> I run the procedure below to update the Sequence field.
>
> When I first added the Sequence field, there were 1.6 million records
> in the table, and the procedure took 55 minutes to update the lot.
> Each night, on average, 30 000 records get added, and the procedure
> takes about 1-3 minutes to update them. So everything works. My
> reason for asking if there is alternative is because I am aware that
> cursors are not the best practice ... I just can't see how to get
> round using them in this instance. So I'm on a quest for
> understanding/efficiency.
>
> The data/structures are as follows:
> 1.) Table SAS_Assignment_History (irrelevant fields excluded)
>
> CREATE TABLE [dbo].[tmpSAS_Assignment_History] (
> [Case_Id] [varchar] (15) NULL ,
> [Create_Date] [int] NOT NULL ,
> [Sequence] [int] NULL )
>
> 2.) View viAssHist
>
> CREATE VIEW dbo.tmpviAssHist
> AS
> SELECT Case_Id, Create_Date, Sequence
> FROM dbo.tmpSAS_Assignment_History
>
> 3.) View viAssHistID
>
> CREATE VIEW dbo.tmpviAssHistID
> AS
> SELECT Case_Id
> FROM dbo.tmpSAS_Assignment_History
> WHERE (Sequence IS NULL)
> GROUP BY Case_Id
>
> 4.) Sample data for SAS_Assignment (before running the UPDATE)
>
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233333',
> '1066555555', 1)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233333',
> '1066655555', 2)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233333',
> '1066755555', NULL)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233334',
> '1066555555', NULL)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233335',
> '1067555555', NULL)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233335',
> '1067655555', NULL)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233336',
> '1066555555', 1)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233336',
> '1066655555', 2)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233337',
> '1066555555', 1)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233337',
> '1066655555', 2)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233337',
> '1066755555', NULL)
> INSERT INTO tmpSAS_Assignment_History VALUES ('HD0000000233337',
> '1066855555', NULL)
>
> 5.) Output (before running the Update)
> Select * from tmpSAS_Assignment_History Order by Case_Id, Create_Date
>
> HD0000000233333 1066555555 1
> HD0000000233333 1066655555 2
> HD0000000233333 1066755555 NULL
> HD0000000233334 1066555555 NULL
> HD0000000233335 1067555555 NULL
> HD0000000233335 1067655555 NULL
> HD0000000233336 1066555555 1
> HD0000000233336 1066655555 2
> HD0000000233337 1066555555 1
> HD0000000233337 1066655555 2
> HD0000000233337 1066755555 NULL
> HD0000000233337 1066855555 NULL
>
> 6.) Output (after running the Update)
> Select * from tmpSAS_Assignment_History Order by Case_Id, Create_Date
>
> HD0000000233333 1066555555 1
> HD0000000233333 1066655555 2
> HD0000000233333 1066755555 3
> HD0000000233334 1066555555 1
> HD0000000233335 1067555555 1
> HD0000000233335 1067655555 2
> HD0000000233336 1066555555 1
> HD0000000233336 1066655555 2
> HD0000000233337 1066555555 1
> HD0000000233337 1066655555 2
> HD0000000233337 1066755555 3
> HD0000000233337 1066855555 4
>
> Comments:
> 1.) Yes, I know I should have a primary key ... I'm not in control of
> that.
> 2.) Yes, I know the Sequence field is actually redundant, but the
> Webfocus query requires it, I've been told (for a max(Sequence)
> ... ).
>
> Procedure follows:
>
> ********************************************************
>
> DECLARE @hd_str varchar(15), @ts_date bigint, @sq_int int, @ts_int
> int, @ts_str varchar(15), @hd_str1 varchar(15)
>
> DECLARE ahistID_cursor CURSOR FOR
> SELECT * FROM tmpviAssHistID Order by Case_ID
> Open ahistID_cursor
> FETCH NEXT FROM ahistID_cursor
> INTO @hd_str
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
> DECLARE ahist_cursor CURSOR FOR
> SELECT * FROM tmpviAssHist Where Case_ID = @hd_str
> ORDER by Create_Date
> OPEN ahist_cursor
>
> FETCH NEXT FROM ahist_cursor
> INTO @hd_str1, @ts_date, @sq_int
> SELECT @ts_int = 1
> SELECT @ts_str = @hd_str
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
> IF @sq_int is Null
> BEGIN
> UPDATE tmpSAS_Assignment_History
> SET Sequence = @ts_int
> Where Case_Id = @hd_str AND Create_Date = @ts_Date
> END
> Select @ts_int = @ts_int + 1
> FETCH NEXT FROM ahist_cursor
> INTO @hd_str1, @ts_date, @sq_int
> END
>
> CLOSE ahist_cursor
> DEALLOCATE ahist_cursor
>
> FETCH NEXT FROM ahistID_cursor
> INTO @hd_str
> END
> CLOSE ahistID_cursor
> DEALLOCATE ahistID_cursor
>
> ************************************************************
> Procedure Ends.
>
> Thanks a mil,
> Peter Bellis
> Sunny South Africa
- Next message: Jacco Schalkwijk: "Re: question for memory leak?"
- Previous message: Dejan Sarka: "Re: SQL Server Client"
- In reply to: Peter Bellis: "Replace Cursor Procedure with Update Query?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
