Rewriting Cursor As NOT A Cursor

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 2008 Forums

Transact-SQL (2008)

Rewriting Cursor As NOT A Cursor

Author

Topic

vbullinger
Starting Member

3 Posts

Posted - 2009-04-08 : 18:35:01

Hello, all. I wrote a cursor to do something. It seems to be working, but it's taking an awfully long time. It's still running as we speak...

Anyway, it's rifling through 89,000+ rows and I'm not sure how long this will take. I certainly don't want to turn this is as the way to go, even though it's a back-end process that will probably only be run once.

I've heard and seen proved to me that most cursors can be rewritten as normal SQL statements without cursors. I've seen a couple of examples, but nothing that's really useful. They probably started with a normal statement and forced a cursor into it, instead of starting with a cursor and trying to remove it. So, here's the code (slightly obfuscated) and thank you in advance:

DECLARE @ParentAccountID INT
DECLARE @ParentAccountUID NVARCHAR(50)
DECLARE @ChildAccountUID NVARCHAR(50)
DECLARE AccountCursor CURSOR FOR
SELECT AccountId, ParentAccountId
FROM MyDB.dbo.MyTable1

OPEN AccountCursor
FETCH NEXT FROM AccountCursor INTO @ChildAccountUID, @ParentAccountUID

WHILE @@FETCH_STATUS = 0
BEGIN
SET @ParentAccountID =
(
SELECT AccountID
FROM MyDB.dbo.MyTable2
WHERE O_LOC_FIRE_UID = @ParentAccountUID
)

UPDATE MyDB.dbo.MyTable1
SET ParentAccountID = @ParentAccountID
WHERE AccountGUID = @ChildAccountUID

FETCH NEXT FROM AccountCursor INTO @ChildAccountUID, @ParentAccountUID
END

CLOSE AccountCursor
DEALLOCATE AccountCursor

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 19:03:01

You can use a recursive CTE.

E 12°55'05.63"
N 56°04'39.26"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-08 : 22:52:13

Unless I'm missing something:

UPDATE T1 SET ParentAccountID=T2.AccountID
FROM MyDB.dbo.MyTable1 T1
INNER JOIN MyDB.dbo.MyTable2 T2 ON T1.ParentAccountID=T2.O_LOC_FIRE_UID AND T1.AccountGUID=T1.AccountID

You may get a type conversion error, if the column types match their respective parameter types in the original. Even if this doesn't work, my advice would be to make the parameters match the types of the columns, you don't need nvarchar to store integer data, and you save a lot of implicit conversions.

vbullinger
Starting Member

3 Posts

Posted - 2009-04-09 : 09:50:25

quote:
Originally posted by robvolk

Unless I'm missing something:
UPDATE T1 SET ParentAccountID=T2.AccountID
FROM MyDB.dbo.MyTable1 T1
INNER JOIN MyDB.dbo.MyTable2 T2 ON T1.ParentAccountID=T2.O_LOC_FIRE_UID AND T1.AccountGUID=T1.AccountID
You may get a type conversion error, if the column types match their respective parameter types in the original. Even if this doesn't work, my advice would be to make the parameters match the types of the columns, you don't need nvarchar to store integer data, and you save a lot of implicit conversions.

If I'm using nvarchar, it's because it's really a GUID. Should've said "uniqueidentifier." Sorry about that. If I'm using an int, it's a real primary key.

That second clause - AND T1.AccountGUID=T1.AccountID - doesn't quite look right... :)

Also, you're really tripped up over the tables. That query doesn't work at all.

I'll try you guys' suggestions and see if I can figure it out. Thanks for the tips, guys.

Oh, and I let the query run overnight. It took a little over three hours :D

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-09 : 12:03:12

Obviously, Rob is missing something. Try following the first link in my signature, and supplying Rob with the info he needs to solve the problem?

NO query or operation should take 3 hours. That is ridiculous.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-09 : 16:01:02

Yeah, sorry, I don't speak cursor.

vbullinger
Starting Member

3 Posts

Posted - 2009-04-09 : 16:51:12

I just had to wrap my brain around it a little longer and came up with this:

UPDATE MyDB.dbo.MyTable1
SET ParentAccountID =
(
SELECT ParentAccount.AccountID
FROM MyDB.dbo.MyTable1 ChildAccount
INNER JOIN MyDB.dbo.MyTable2 CRMAccount
ON CRMAccount.AccountId = ChildAccount.AccountGUID
INNER JOIN MyDB.dbo.MyTable1 ParentAccount
ON ParentAccount.AccountGUID = CRMAccount.ParentAccountId
WHERE FireSafeAccount.AccountID = ChildAccount.AccountID
)
FROM MyDB.dbo.MyTable1 FireSafeAccount

Still took 26 minutes, though... Then again, we're talking about 89,000 records, plus it's an insert from one table in one database into another table in another database, plus the update above, then selecting the data to display it and then truncating the database (because it's for testing and I want to delete the data). No wait, it's a delete, not a truncate, so that takes even longer. Still, if you can figure out a faster way, please let me know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources