Cursor in SQl Server

Question

Cursor in SQl Server

Viewing 8 posts - 31 through 37 (of 37 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Mike Menser (3/12/2008)
Should create a staging table under the database where the original data is, or create a new database for migration, and then create the tables under that?

that's one of those "it depends". I'd probably put the staging area somewhere separate if the production DB is "busy", although I'd often do that through tempdb (assuming I tear it down when it's all done).

What you are saying is insert the data from the original tables into staging tables in order to allow me to clean it up, then insert the data directly into the new database and that is it????? I mean I have to be missing something because that sounds way too simple. Row orders have no relevance because sorting is all handled by the external application.

correct - simply taking a >50,000 line long text file of stuff to import, importing it using something like DTS bulk load, BULK INSERT, or the BCP utility, instead of a cursor or pretty much any other row by row routine, routinely cuts load time (for me) by a factor of 100 to 1 (at least). Since I want to double check the data - I don't pound it into my production stuff, thus the staging. I can then perform my validations and load into production from there.

Not that it doesn't do this on smaller files too, but some are so small that (on human standards) something cursor related might look "fast enough". On a machine level - it's still about the same as thowing a sports car into first gear (it will get you there eventually, but you're giving away a lot of performance).

This is ultimately why you see a lot of "cursors are evil" on these boards. It robs your server of performance and should only be used when there are NO other alternatives.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

If nothing else cursors are painfully slow. They hold locks for extended periods causing other issues. If a set based solution is not available, the following is a much better alternative to a cursor (and easier to use).

Now you are exchanging a cursor for a temp table, but that is rarely an issue.

IF OBJECT_ID('tempdb.dbo.#myList') IS NOT NULL

DROP TABLE #myList

-- Basically same as DECLARE/OPEN your cursor

SELECT

ROW_NUMBER() OVER(ORDER BY database_id) AS RN,

databases.*

INTO #myList

FROM sys.databases

CREATE CLUSTERED INDEX #CI_myList ON #myList(RN)

DECLARE @Cntr INT

-- FETCH FIRST

SELECT @Cntr = 1

-- @@FETCH_STATUS

WHILE EXISTS (SELECT 1 FROM #myList WHERE [#myList].RN = @Cntr)

BEGIN

-- FETCH FIRST/NEXT FROM DbList INTO @Dbname

DECLARE @Dbname sysname -- Can also declare outside of this block

SELECT

@Dbname=name

FROM #myList

WHERE RN = @Cntr

PRINT @Dbname

-- FETCH NEXT

SELECT @Cntr = @Cntr + 1

END

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Temp table and While loop is going to be almost as slow as a cursor when it comes to RBAR. The only advantage is the lack of locking and a "FireHose" cursor is just as effective even with speed and lack of locking. There is no advantage to converting cursors to Temp Tables if the cursor is (and should be) a "FireHose" cursor (FAST_FORWARD which is same as Read Only/Static).

Temp table/While Loop are both acceptable as "CONTROL OF FLOW" code... for example, doing something to a list of databases in SQL Server 2k. Neither are ever acceptable for RBAR. With VARCHAR(MAX), both methods are unacceptable in my book for 2k5.

--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)

saidwarak01 SSCommitted Points: 1577 More actions · Answer 4

Hi all,

I know cursors will degrade the performance but I dont know how to substitue them with CTE or While loop. Please help me to understand by giving examples on while loop and CTE instead of cursors.

Thanks,

sai

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

saidwarak01 (7/2/2009)
Hi all,
I know cursors will degrade the performance but I dont know how to substitue them with CTE or While loop. Please help me to understand by giving examples on while loop and CTE instead of cursors.
Thanks,
sai

It's almost a total waste of time and effor to change a cursor to a temp table and While loop. A well written forward only, read only or static cursor will do just as well. Some recursive CTE's can be even worse than a cursor.

The best thing to do is to stop thinking about what you want to do to a row and start thing about what you want to do to a column.

There're a lot of good articles on how to avoid the cursor and While loop (otherwise known as "RBAR"). Here're a couple just to get you started...

http://www.sqlservercentral.com/articles/T-SQL/66494/

http://www.sqlservercentral.com/articles/T-SQL/62867/

Also, take time to read the discussions that follow such articles. There's a huge amount of value in some of those discussions.

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Heh, thanks for the plug, Jeff. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

You bet, Barry... it's good stuff. Is there another installment coming up any time soon?

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Vacation this coming week, we'll see if I can bang out anything there.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]