replacing long running cursor

Question

replacing long running cursor

Sanjay-940444

SSC-Addicted

Points: 422
More actions
August 17, 2010 at 3:05 am

#95002

I have one table tbldsrtemplate
it has 3 columns namely Id,OrderNo,PhoneID
Id is an identity column
One orderno can have 1 or more rows in the table
Phoneid in the table is updated as per below code:
---------------------------------------------------------------
---------------------------------------------------------------
DECLARE Cursor_ID cursor
FOR
SELECT DISTINCT OrderNo
FROM tblDSRTemplate NOLOCK
OPEN Cursor_ID
FETCH NEXT FROM Cursor_ID INTO @OrderNo
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE Cursor_Test cursor
FOR
SELECT ID
FROM tblDSRTemplate NOLOCK WHERE OrderNo = @OrderNo
SET @i=0
OPEN Cursor_Test
FETCH NEXT FROM Cursor_Test INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i=@i+1
--SET @PhoneID = @OrderNo + convert(char(4), @i)
UPDATE tblDSRTemplate SET PhoneID = @i where ID = @ID
FETCH NEXT FROM Cursor_Test INTO @ID
END
CLOSE Cursor_Test
DEALLOCATE Cursor_Test
FETCH NEXT FROM Cursor_ID INTO @OrderNo
END
CLOSE Cursor_ID
DEALLOCATE Cursor_ID
----------------------------------------------------------------------
----------------------------------------------------------------------
Can anybody tell me how can i do same without using a cursor or while loop.
Its for sql server 2000
Thanks in advance.
Sanjay

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would use the "quirky update" method, assuming you're using SS2K and not SS2K5 or higher (in this case ROW_NUMBER() would be my preferred method).

The quirky update method is described in one of Jeff's great articles[/url].

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

What is the maximum number of rows for any orderno?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sanjay-940444 SSC-Addicted Points: 422 More actions · Answer 3

It cud be any no between 1 and 20 and in some rare cases more than 20.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Oh, I see it coming...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Jeff Moden (8/17/2010)
Oh, I see it coming...

Heh Jeff got that porkchop launcher loaded up for the first triangular join post? 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Chris Morris-439714 (8/17/2010)
Jeff Moden (8/17/2010)
Oh, I see it coming...
Heh Jeff got that porkchop launcher loaded up for the first triangular join post? 😀

Z'actly 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 7

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.-- first, load the data into a test table.

So, since you didn't provide any of this, I'm making a few guesses on the data types in the table. And since there isn't any sample data, it hasn't been tested. If it happens to not work, then I'll only look at it again once you follow the directions in the above paragraph.

-- it needs to have a clustered index on the orderno column

CREATE TABLE #test (ID int, OrderNo varchar(20), PhoneID varchar(20), CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (OrderNo, ID));

INSERT INTO #test

SELECT Id,OrderNo,PhoneID

FROM tblDSRTemplate;

declare @id int,

@OrderNo varchar(20);

-- This form of the UPDATE statement has some rules for proper usage.

-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

UPDATE t1

SET @id = CASE WHEN OrderNo = @OrderNo THEN @id + 1 ELSE 1 END,

@OrderNo = OrderNo, -- use first column in clustered index as an anchor column.

PhoneID = @id

FROM #test t1 WITH (TABLOCKX)

OPTION (MAXDOP 1); -- << prevent parallelism!

-- now, go back and update the source table.

UPDATE t1

SET PhoneID = t2.PhoneID

FROM tblDSRTemplate t1

JOIN #test t2

ON t1.Id = t2.ID;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sanjay-940444 SSC-Addicted Points: 422 More actions · Answer 8

-- it needs to have a clustered index on the orderno column
CREATE TABLE #test (ID int, OrderNo varchar(20), PhoneID varchar(20), CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (OrderNo, ID));
INSERT INTO #test
SELECT Id,OrderNo,PhoneID
FROM tblDSRTemplate;
declare @id int,
@OrderNo varchar(20);
-- This form of the UPDATE statement has some rules for proper usage.
-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
-- for a complete discussion of how this works, and all of the rules for utilizing it.
UPDATE t1
SET @id = CASE WHEN OrderNo = @OrderNo THEN @id + 1 ELSE 1 END,
@OrderNo = OrderNo, -- use first column in clustered index as an anchor column.
PhoneID = @id
FROM #test t1 WITH (TABLOCKX)
OPTION (MAXDOP 1); -- << prevent parallelism!
-- now, go back and update the source table.
UPDATE t1
SET PhoneID = t2.PhoneID
FROM tblDSRTemplate t1
JOIN #test t2
ON t1.Id = t2.ID;

Thanks Wayne for such a wonderful solution.

I have been working on sql server for three years but first time I saw variables can be used in this way too.

Clustered index cant be created on this table due to other resons but we can assume that id and orderno will be in increasing orders as insert-select statement on this table will be ordered on orderno.This table is truncated before this insert everytime.

I had thought of like this:

select min(id) as id,orderno

into #temp

from tbldsrtemplate

group by orderno

update tbldsrtemplate

set phoneid=id-t.id+1

from tbldsrtemplate td inner join #temp t

on td.orderno=t.orderno

I am yet to test which way will take the less time.

Any comments on above code and which way will take the less time?

There are millions of rows in this table so there cud be significant difference in query time depending on the codes.

Thanks

Sanjay

WayneS SSC Guru Points: 95460 More actions · Answer 9

Yes, I assumed that a table with a million records would already have a clustered index. That's why we pull the data into a #temp table, and throw the index on it there.

I don't think your solution will work correctly:

update tbldsrtemplate

set phoneid=id-t.id+1

from tbldsrtemplate td inner join #temp t

on td.orderno=t.orderno

If there a 5 records for an order number, with the min(id) being one, and with existing gaps, you won't get sequential numbers:

if object_id('tempdb..#test') IS NOT NULL DROP TABLE #test

create table #test (ID int, OrderNo varchar(5), phoneid int);

insert into #test (ID, OrderNo)

select 1, 'test' UNION ALL

select 3, 'test' UNION ALL

select 5, 'test' UNION ALL

select 7, 'test' UNION ALL

select 9, 'test'

select * from #test

update t

set PhoneID = t.ID - t2.ID+1

from #test t

JOIN (select ID = min(id), OrderNo from #test group by OrderNo) t2

ON t.OrderNo = t2.OrderNo

select * from #test

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sanjay-940444 SSC-Addicted Points: 422 More actions · Answer 10

I have one question here

Does SQL Server guarantee that update will be done strictly in top down manner?

I didnt find it writtem anywhere in BOL.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Sanjay-940444 (10/20/2010)
I have one question here
Does SQL Server guarantee that update will be done strictly in top down manner?
I didnt find it writtem anywhere in BOL.

The update is performed strictly in the order of the clustered index, unless your table is partitioned.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

Sanjay-940444 (10/20/2010)
I have one question here
Does SQL Server guarantee that update will be done strictly in top down manner?
I didnt find it writtem anywhere in BOL.

Actually, like anything else, it depends... not in every circumstance. That's why you need to write in "the check". The clustered index order is how most updates that follow the quirky update rules work especially if the clustered index is the only "unique" index. The check itself actually helps in forcing the correct order and, make no bones about it, the order of the check MUST be the same order as the clustered index.

I've been using the method for many years and never had a problem. The thing is, I use it carefully and usually only on tables that my code has 100% control of... namely, Temp Tables. If I need to write the result to a permanent table, I usually do it in a Temp Table first. The recently implemented "inline check" method ensures that if anything goes wrong, an error will be raised.

If that makes you or the folks you work for nervous, get better at writing a cursor or write a CLR to do the work. As a side bar, a very well written cursor may be a fair bit slower than the Quirky Update... but it's not THAT slow. It's not the 2-7 seconds of the Quirky Update but Hugo got the cursor method down to something like 2 minutes on a million rows. That's not bad considering that SQL Server was never designed for such a thing.

Thanks to Paul White and Tom Thompson for the enhanced inline check method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 13

Ah... my apologies. I didn't look to see that this is for an SQL Server 2000 instance.

Unless WayneS or someone else beats me to it (which I don't mind at all), I'll show you a way to do the "inline check" for SL Server 2000. Basically, you have to start out with a Temp Table that has an IDENTITY column to replace the ROW_NUMBER safety check...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sanjay-940444 SSC-Addicted Points: 422 More actions · Answer 14

Chris, is there any any Microsoft official document which states this?

Jeff, is there a link for what u just mentioned?

Thanks both