September 3, 2007 at 10:27 am
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
September 3, 2007 at 12:27 pm
You need to provide some example of what you are trying to do. That's too open ended a question.
September 3, 2007 at 5:00 pm
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
Change is inevitable... Change for the better is not.
September 4, 2007 at 1:31 am
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
September 4, 2007 at 7:10 am
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...
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
Change is inevitable... Change for the better is not.
September 5, 2007 at 10:24 am
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
September 5, 2007 at 5:26 pm
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...
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.
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
Change is inevitable... Change for the better is not.
September 7, 2007 at 1:42 am
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
September 7, 2007 at 6:11 am
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
Change is inevitable... Change for the better is not.
September 10, 2007 at 6:11 am
Thanks a lot David and Jeff !
Really i gathered some knowledge about Cursor and 'SetBased' Thearoy from your posts.
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