Cursor and SetBased Thearoy

Question

Post reply

Cursor and SetBased Thearoy

karthik M

One Orange Chip

Points: 29139
More actions
September 3, 2007 at 10:27 am

#178258

Hi,

I want to remove cursor and need to apply set based thearoy logic.
How to apply Set Based thearoy logic ? It would be highly appreciable if anybody give me a real time example and sqlcode.

Regards
Karthik

karthik

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

You need to provide some example of what you are trying to do. That's too open ended a question.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

See what I posted here... http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=395869

Then, as Steve suggested, post the code you want to convert (provided it's NOT hundreds of lines long)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 3

My Code is,

----------------------------------------------------------------------

select name into #t1

from sysobjects

where type ='U'

and name not in ('ProductCommitmentWorkflow',

'Tranche',

'CompanyTrans',

'ProductFeatureSelection',

'ProductFeature',

'ProductSubType',

'ProductType'

)

create unique index id1 on #t1(name)

declare p1 cursor

for select name from #t1

for read only

open p1

declare @name varchar(40)

fetch p1 into @name

while @@fetch_status = 0

begin

declare @sql varchar(255)

select @sql = 'Truncate table '+ @name

exec (@sql)

fetch p1 into @name

end

close p1

deallocate p1

Regards

Karthik

karthik

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

Karthik,

Now THAT's a horse of a different color! The example you posted is for "Flow Control" and there's not much you can do in the form "Set Based" processing here.

Still, you need to know that a Cursor has a lot more overhead than a temp table and a While Loop. We can avoid the Cursor by adding a column to the temp table and changing the way you retrieve each row for the flow control that controls the Truncates... thusly...

--===== Create the local variables

DECLARE @Counter INT --Just a loop counter

DECLARE @MyCount INT --Remembers the value of @@ROWCOUNT

DECLARE @SQL VARCHAR(8000) --Contains the dynamic SQL to truncate the tables

--===== Create a numbered list of tables to truncate

SELECT IDENTITY(INT,1,1) AS TableNum, --Creates a number column to support RBAR access

NAME

INTO #TruncateTableList

FROM dbo.SysObjects

WHERE NAME NOT IN ('ProductCommitmentWorkflow',

'Tranche',

'CompanyTrans',

'ProductFeatureSelection',

'ProductFeature',

'ProductSubType',

'ProductType'

)

AND OBJECTPROPERTY(ID,'IsTable') = 1 --Is a table

AND OBJECTPROPERTY(ID,'IsMSShipped') = 0 --Protects dtProperties and other system tables

--that have a TYPE of "U".

--===== Capture how many rows we put into the temp table

SET @MyCount = @@ROWCOUNT

--===== Add a primary key just because every table deserves one

-- and it will also create an index on the TableNum column

-- which will come in handy later.

ALTER TABLE #TruncateTableList

ADD PRIMARY KEY CLUSTERED (TableNum)

--===== Do the flow control to truncate the captured table names

SET @Counter = 1

WHILE @Counter <= @MyCount

BEGIN

SELECT @SQL = 'Truncate table ' + Name

FROM #TruncateTableList

WHERE TableNum = @Counter

EXEC (@SQL)

SET @Counter = @Counter + 1 --Ya just gotta remember to do this

END

Now, in this case, there's not much of a speed advantage between using the cursor and the way I did it. Either way is fine except that the cursor does use some extra resources. BUT, there are a couple of things I added to your code...

I changed the name of your temp table to make the code a bit more self documenting. #t1 could mean anything. #TruncateTableList is self documenting... you KNOW what it contains just by reading the name.
I changed the criteria for which tables to find. Most folks don't know it, but there is a "System" table that is labelled as a "User" table called dtProperties. The method I used (using OBJECTPROPERTY) will prevent that eventuallity. You could also have gotten the table names from the INFORMATION_SCHEMA.Tables view, but even that incorrectly lists the dtProperties tables as a "Base Table".

Just as another performance tip, you could also write the WHILE Loop like this (notice where the counter increment code is)...

--===== Do the flow control to truncate the captured table names

SET @Counter = 1

WHILE @Counter <= @MyCount

BEGIN

SELECT @SQL = 'TRUNCATE TABLE ' + Name,

@Counter = @Counter + 1 --Ya just gotta remember to do this

FROM #TruncateTableList

WHERE TableNum = @Counter

EXEC (@SQL)

END

p.s. Don't let anyone talk you into using the undocumented "MSForEachTable" 'cause, guess what?... it contains a Cursor!

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

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Hi Jeff,

Thanks !Really i got some idea to use while loop instead of Cursor.

I have two questions.

1) What is the difference between Cursor and While Loop ? as you mentioned 'cursor does use some extra resources',can you expalin what kind of extra resources will be used by cursor ?

2)As you mentioned 'dtproperties' will come under 'dbo'. Can you explain me the purpose of 'dtproperties' ? will it be created automatically during database creation ?

Please clarify my doubts.

Regards

Karthik

karthik

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

Sure, Karthik... let's answer question #2 first...

'dtProperties' is a special table used by things like the "Visual Database Design Tools". It even has to be modified if you want to be able to use earlier versions of the Tools. Basically, it's a system table that can be altered by the user which is why it's actually considered to be a user table. And, I don't believe it can be deleted without causing drastic problems in the database. Every database has one of these tables.

Back to question #1...

A While Loop, although a bit slow (sometimes, quite a bit) when compared to Set Based code, doesn't really use much in the form of resources. A Cursor, on the other hand, must store the "result set" from the cursor declaration somewhere... and guess where that place is??? Sure, TempDB where all temporary objects are created... here's from Books Online about cursors...

Remarks

If the cursor is declared with the INSENSITIVE or STATIC option, OPEN creates a temporary table to hold the result set. OPEN fails if the size of any row in the result set exceeds the maximum row size for Microsoft® SQL Server™ tables. If the cursor is declared with the KEYSET option, OPEN creates a temporary table to hold the keyset. The temporary tables are stored in tempdb.

Static Cursors

The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened.

So, in your original code, you created a temp table and the cursor could make another one on top of that. And, if you don't declare it correctly, it'll open yet another temp table. Those temp tables are some of the resources I'm talking about.

If the cursor is updateable, it may not open a temp table... it'll just hold locks on the source table(s) until you deallocate and close the cursor.

There have been some pretty serious wars about cursors on this forum. For the task you want to accomplish, a cursor would probably be fine although opening a cursor on a temp table can have the effect of opening two temp tables... that wastes memory because the tables in TempDB will first try to form in memory and only spill over to the disk if they get too big for memory. Memory is a precious resource that you don't want to waste.

As the note about Static Cursors says, there's really not much difference between opening a temp table and using a static cursor until you do some processing. Not much of a chance to do high speed set based processing with something inherently row based like a cursor... but you can do a lot with a temp table... if you even need one. And THAT's the key... most of the time you don't need a temp table... why have the system create one when you create a cursor?

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 7

While not exactly set based, there is no cursor, temp table, or loop required:

DECLARE @SQL varchar(8000)

SELECT @SQL = ISNULL(@SQL,'')+'TRUNCATE TABLE [' + name + ']; '

FROM sysobjects

WHERE type ='U'

AND name NOT IN ('ProductCommitmentWorkflow',

'Tranche',

'CompanyTrans',

'ProductFeatureSelection',

'ProductFeature',

'ProductSubType',

'ProductType'

)

ORDER BY name

--PRINT @SQL

IF LEN(@SQL) > 0

EXEC (@SQL)

Andy

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Very nicely done, David, and a lot closer to set based! And you even remembered the brackets for "odd" table names that I forgot Same method is used to create "CSVs" and the like.

The only reason why I didn't show such a method is because you can run out of space in the varchar if enough tables are present. If the average length of a table name is 12 characters which would make the average length of each command 31 characters (including the necessary trailing space), you'd run out of space in VARCHAR(8000) at about 258 tables. Admittedly, that's a fair number of tables...

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

karthik M One Orange Chip Points: 29139 More actions · Answer 9

Thanks a lot David and Jeff !

Really i gathered some knowledge about Cursor and 'SetBased' Thearoy from your posts.

Regards

Karthik

karthik