For those who say cursors are evil
-
OK, people keep telling me cursors are evil and there is always a way to do something without using them. I keep running into situations where I can't think of any other way to do something about it. Here's one that I had nothing to do with: http://www.sqlservercentral.com/scripts/contributions/1557.asp
So, show me how to do what this does without using cursors. (Using SQL Server 2000 technology. With the new datatypes coming out in 2005, I could figure out how to do this without cursors.)
Don't tell me it can be done, do it. There's an extremely simple example of when I can't figure out how to do something without cursors. Just re-write his code without cursors.
-
Oh, yea, don't use the underlying table dbo.sysproperties, that's an extremely simple way to build your dictionary without cursors that I've used a long time ago. Use the function that obsfucates the process like the author of the above code did.
-
This sort of what you're looking for?
Create table #tblDataDictionary ( table_id [sql_variant] , table_name [sql_variant] , column_order [sql_variant] , column_name [sql_variant] , column_description [sql_variant] )
INSERT INTO #tblDataDictionary SELECT o.[id] as 'table_id' , o.[name] as 'table_name' , c.colorder as 'column_order' , c.[name] as 'column_name' , e.value as 'column_description' FROM sysobjects o INNER join syscolumns c ON o.id = c.id LEFT join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', null, N'column', null) e on c.name = e.objname WHERE o.type = 'U' AND o.status > 1 ORDER BY c.colorder
SELECT * FROM #tblDataDictionary
DROP TABLE #tblDataDictionary
--------------------
Colt 45 - the original point and click interface
-
Try again
select * from ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', null, N'column', null) e
produces null results, not surprisingly, so does:
SELECT o.[id] as 'table_id' , o.[name] as 'table_name' , c.colorder as 'column_order' , c.[name] as 'column_name' , e.value as 'column_description'
FROM sysobjects o INNER join syscolumns c ON o.id = c.id LEFT join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', null, N'column', null) e on c.name = e.objname
WHERE o.type = 'U' AND o.status > 1 AND e.value is not null
ORDER BY c.colorder
This in a DB where
select * from ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'CopyTable', N'column', null) e
does produce results.
Even if it did work, multiple tables use the same variable name. At many locations with very different meanings. If you had 10 variable names that matched and a definition for each, your join would produce 100 results. (If it worked.)
Like I said, creating the dictionary using dbo.sysproperties without cursors is simple. I want you to do this with the function the author used and no cursors.
Create table #tblDataDictionary
(table_id int,
table_name sysname,
column_order tinyint,
column_name nvarchar(256),
column_description [sql_variant])
--Add table and column definitions
insert into #tblDataDictionary
SELECT o.id, o.name, c.colid, c.name, p.value
FROM dbo.sysobjects o
left join dbo.sysproperties p on o.id=p.id and p.name='MS_Description'
full outer join dbo.syscolumns c on o.id=c.id and p.smallid=c.colid
where o.type = 'U' and o.status > 1
--Add columns that aren't defined in the dictionary
insert into #tblDataDictionary
SELECT o.id, o.name, c.colid, c.name, null
FROM dbo.sysobjects o
join dbo.syscolumns c on o.id=c.id
left join #tblDataDictionary p on o.id=p.table_id and p.column_name=c.name
where o.type = 'U' and o.status > 1 and p.column_name is null
-- Lists table definitions that have been defined, something not in the author's example
select * from #tblDataDictionary
where column_description is not null
and column_name is null
order by table_name
-- Lists column definitions that have been defined
select * from #tblDataDictionary
where column_description is not null
and column_name is not null
order by table_name, column_name
-- Lists table definitions that have not been defined
select * from #tblDataDictionary
where column_description is null
and column_name is null
order by table_name
-- Lists column definitions that have not been defined
select * from #tblDataDictionary
where column_description is null
and column_name is not null
order by table_name, column_name
-
Sorry, I can't reproduce your results, I don't have any databases where the MS_Description property is used.
Honestly, I can't recall using it in any of the databases I've worked on over the years. Generally the data dictionary was seperate from the database in tools like ERWin and ER/Studio.
Are you trying to say that because you can't find a non-cursor solution to this problem then cursors are not evil
--------------------
Colt 45 - the original point and click interface -
The properties support is built into the Enterprise Manager toolset. The definition library travels with the database. It doesn't depend on 3rd party software your customer may not have to see the definitions. SQL Server provides built-in procs that allow you to expand the properties in other directions than using name='MS_Description'. All good reasons to use their services.
The interfaces to access the definition library suck. If they didn't, 3rd party software wouldn't have a chance in H of succeeding. The article written about it wouldn't have been written. The definition library is not independent of the DB. (See, the same thing going for it, goes against it as well.)
I'm kind of curious, do you know if any of your 3rd party software is using dbo.sysproperties to store it's information?
I don't really care if you like or dislike the toolset, what I'm asking about is how to get results where I don't see any other way than using a cursor.
Show me how to run "DBCC INPUTBUFFER (spid)" for every spid that's currently active without using a cursor.
-
I have seen misuse of cursors, I know they are a performance killer and they are the lazy way for a beginner to find a solution. In that respect, they are a little bit evil. I think that if they are being used in a heavily used production code, that code should be seriously reviewed to see if there isn't a set solution to do the same thing. I keep running into situations where a cursor seems like the best choice, and yes, I believe it isn't evil. It's no more evil than a gun is. It all depends on who is handling it.
In this particular example, there is a solution that does not need cursors in the SQL Server environment, in fact I provided a solution. Since I don't plan on leaving the SQL Server environment, it is a very satisfying solution. If I was trying to build a general purpose solution to this particular problem, this isn't a satisfying solution. (First, I'd have to know if extended properties are an ANSI standard and if the functions/views I was using come from ANSI. I'd have to care enough about it, to find out. I certainly wouldn't hardcode my solution to depend on the key 'MS_Description'.)
What is interesting about this function is that it provides a result set that is dependent on the environment. You can in-line call a function that produces a scalar result in a select statement but you can't do that with a function that produces a result set. I don't see how you could do it without cursors.
Another place where I end up using cursors, I know is because of my lack of knowledge. This is when I am building scripts where the final result will be longer than 8K bytes. I've read about ntext editing and I don't get it. Could someone show an example of creating an ntext field in a temp table, getting a pointer to a row on that table and adding 'exec ::FN_LISTEXTENDEDPROPERTY(N''MS_Description'',N''user'',N''dbo'',N''table'',N''' + name + ''', N''column'', null)' + char(13) + char(10) from dbo.sysobjects? (Yes, I know this can be put on individual rows in a result set, but how would code then execute it without using a cursor? What about situations where one command would exceed 8K bytes?)
The third place where I find cursors are useful is in scripts where I am not concerned in performance and using them breaks up the work into slightly more readable chunks of code. This is one argument I know I'll never win with cursor-hater purists. Let's just agree to disagree.
Oh, I figured out why my first join didn't include non-defined columns. This single insert would work instead of the two I gave in my non-cursor example:
--Add table and column definitions
insert into #tblDataDictionary
SELECT o.id, o.name, c.colid, c.name, p.value
FROM dbo.sysobjects o
left join dbo.sysproperties p on o.id=p.id and p.name='MS_Description'
join dbo.syscolumns c on o.id=c.id or (p.id=c.id and p.smallid=c.colid)
where o.type = 'U' and o.status > 1
-
If it's just looping over all the tables, you can do that with a while loop (but since it's still completely procedural, I classify this as cheating :-):
1. Create a table variable with an identity column and a column for table names
2. Stuff it full of the list of tables
3. Declare i = 1
4. While i <= max of table list
begin
do things for the ith table
i = 1 + 1
end
Does that accomplish the task?
-
Using an even more inefficient method than cursors, sure it does!
I'm looking more for a "set" answer from the "set purists" who don't like any cursors because there are more efficient ways of doing things using sets. That cursors are used by people who don't understand how to operate in sets. I certainly agree with that point, your solution is no better in that regard.
Show me how to run "DBCC INPUTBUFFER (spid)" for every spid that's currently active with a set answer, not using cursors or individual looping logic. Since this command is going to give 1 result row, show me how to get a scaler answer from just one of the fields of this result set in a select statement that returns every spid.
-
I am probably considered on of those "set purists". My answer to your challenges is simple; do no use SQL Server as a business logic server, especially not for procedural code. I usually say that "a problem that can not be handled using set based T-SQL is probably not a problem for the DBMS to solve". If I needed to run DBCC INPUTBUFFER for each spid I could easily code that functionality in a C# application.
It is not hard to come up with situations that can not be handled without cursors and/or dynamic sql in SQL Server. Here is one: "For every table in every database, check every column and see if it is a varchar column. If it is, take every second character of that column and insert into a new table which should be named after the table and column where you are currently searching." But does that mean we should implement this in SQL Server?
I am not blind and would for instance never argue that cursors should be removed from SQL Server. However, for handling problems normally handled by RDBMSs I avoid them because I think there is a set based solution for every possibility that will always outperform the cursor.
-
Chris, that's one of the best answers I've seen regarding this quesion. Using a string to store generated script output certainly solves my ntext question. Now all I have to do is figure out how to organize all these little customized C# programs that I'll have to write that don't belong in the business model and can be easily done using SQL text files instead and I won't have to write cursor logic again.
You are absolutely right, for production work, highly re-used business logic, if SQL can't do it without using cursors, it probably doesn't belong in SQL. I'm not convinced for low use/low performance impact code that using cursors is wrong.
-
Agreed!
But, though my While/Looping answer was tongue-in-cheek, here's a more serious question: what about locking? Does a Declare Cursor on (stuff) lock more (stuff) than would a While loop that processes items one at a time? Or is the difference negligible?
-
Hmmm, hadn't thought of that angle. I don't know enough about locking to give a definitive answer. What isolation level are you processing at in your script? What isolation level are interacting scripts going to run at? Are all of your script commands inside of a transaction? What type of cursor? If you want to argue that scrollable updateable cursors are evil, I don't want to fight that battle because I don't feel like being General Custer. I was thinking about a fast_forward read committed cursor outside of transaction level processing. I believe that produces a snapshot result set at the open command that is read locked on every record for the duration of the open command. If it's inside of a transaction, I'd guess it's read locked for the duration of the transaction.
Your loop processing would read lock the same information for the duration of creating the temporary table. The act of writing the result set into a table would cause a little more overhead, so I'd guess you might lock the information a split second longer outside of a transaction. A transaction would lock your resources longer because your loop should take longer.
Then the impact of what we are doing inside of the loop comes into play, outside of a transaction I'd say a push, inside I'd say a cursor should have less impact because the loop should take less time and the impacts would come in the same order with either method.
I'd guess hands down a loop would beat a scrollable cursor because the whole result set is read locked from open to close of the cursor.
-
You also don't have the overhead of maintaining the cursor data in memory.
--------------------
Colt 45 - the original point and click interface -
You also don't have the overhead of maintaining the cursor data in memory.
Well, if you have a temp table that starts with @, you still have the memory overhead. If you have a temp table that starts with # or ##, you are increasing contention for tempdb resources as you make your individual inquiries.
Oh, wait, I'm building the temp table as I go along, so I'm building either more memory storage or more tempdb resource contention problems too.
I don't know enough about the mechanics to argue this either way.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply