March 31, 2009 at 10:23 pm
Hi,
I want know,when exactly to use cursor,if possible with an example.
Generally,SQL Server cursors can result in some performance degradation in comparison with select statements.
So we should not use cursor at all or it can be use in certain situation.
What can be the circumtances under which cursor is use ?
[font="Verdana"]Regards
Kumar Harsh[/font]
March 31, 2009 at 10:59 pm
April 1, 2009 at 12:01 am
As of SQL Server 2005 and later, the only time that you should use Cursors is when you actually want a procedure to run slower.
[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]
April 1, 2009 at 12:10 am
pandeharsh (3/31/2009)
Hi,I want know,when exactly to use cursor,if possible with an example.
Generally,SQL Server cursors can result in some performance degradation in comparison with select statements.
So we should not use cursor at all or it can be use in certain situation.
What can be the circumtances under which cursor is use ?
The best way to use C*** (the evil) is don't use them ;-). The only situation to use cursors (dang, I said...) are some administrative scripts. Usually there should be no other place to use it.
Search for articles concerning cursors and RBAR on this page.
Greets
Flo
April 1, 2009 at 12:13 am
RBarryYoung (4/1/2009)
As of SQL Server 2005 and later, the only time that you should use Cursors is when you actually want a procedure to run slower.
Hi Barry!
"Run slower" sounds so bad... let's say "if you have a plenty of time" 😛
Greets
Flo
April 1, 2009 at 12:15 am
Florian Reischl (4/1/2009)
RBarryYoung (4/1/2009)
As of SQL Server 2005 and later, the only time that you should use Cursors is when you actually want a procedure to run slower."Run slower" sounds so bad... let's say "if you have a plenty of time" 😛
It's meant to. 😀
[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]
April 1, 2009 at 8:20 am
Hi
In simple words you can use the cursors when you have plenty of time 🙂 i.e. Even if the system suffers from perfromance nobody is going to complaint. 🙂
Thanks -- Vijaya Kadiyala
April 2, 2009 at 8:48 am
I may be sticking my neck out a little far here... but I do use cursors a bit. I only use them for specific tasks, particularly when I need to run something on multiple databases (no 2008 in house yet) or when I need to create and execute dynamic SQL (usually on remote servers). Yes, I know... dynamic sql. Shame on me. But it does accomplish what I need to do.
Let me provide specific examples to my defense. (and please feel free to dispute any... I encourage "discourse" aka disagreement.)
1) In a DBA database (administrative tables and sp's, only accessible by the DBAs) I have a stored procedure "pSearchSchemaForString". I have a cursor over a dynamic query of all database objects. This could probably be done more efficiently using CLR, but it does what I need and the impact is negligible.
2) I have replication enabled, using a third party tool, taking data between DB2 to SQL Server. We have had problems with this replication. I created an SSRS report that returns on demand a scorecard with red, yellow, or green indicators about errors in the application or excessive row count differences. In order to get the row count differences without having a stored procedure that has to be edited every time I add a new table to replication, I have the portion of the query counting rows on the DB2 server in a cursor based on the tables in the replicated database. Could I do this without dynamic SQL or without a cursor? Probably, but the only way I can think is to either create something on the DB2 (which I cannot do) or... I am not sure, maybe dynamically modifying a stored procedure?
3) Again, in my DBA database, I have a list of aliases for specific objects (to simplify development time between multiple databases). Any time this list is changed I update all applicable databases with the new alias. I could do this with a custom .Net application, but T-SQL works fine, when I use cursors.
I am not advising the use of cursors where other, more efficient solutions (CTE, Tally Table, etc) would work, but I do believe they have their place (at least in 2005).
April 2, 2009 at 9:28 am
Hi Timothy
I may be sticking my neck out a little far here... but I do use cursors a bit.
Shame on you! ... not really... there are cases where it makes sense...
1) In a DBA database (administrative tables and sp's, only accessible by the DBAs) I have a stored procedure "pSearchSchemaForString". I have a cursor over a dynamic query of all database objects.
And this are exactly the reasons for cursors - administrative jobs. For your specific case you maybe should have a look to "sp_MSforeachdb". It's also a cursor inside but it's more easy to use and may reduce your scripts to their real work. I just had the same discussion within another thread:
http://www.sqlservercentral.com/Forums/Topic683335-338-1.aspx.
BTW: If you need to loop tables for some reason you should also have a look to "sp_MSforeachtable".
This could probably be done more efficiently using CLR, but it does what I need and the impact is negligible.
Not at all... CLR is a new possibility in SSE2k5 but it should be used careful. It makes sense for huge string operations or things like that. But what you can do with TSQL should be done with - just my opinion.
2) I have replication enabled, using a third party tool, taking data between DB2 to SQL Server. We have had problems with this replication. I created an SSRS report that returns on demand a scorecard with red, yellow, or green indicators about errors in the application or excessive row count differences. In order to get the row count differences without having a stored procedure that has to be edited every time I add a new table to replication, I have the portion of the query counting rows on the DB2 server in a cursor based on the tables in the replicated database. Could I do this without dynamic SQL or without a cursor? Probably, but the only way I can think is to either create something on the DB2 (which I cannot do) or... I am not sure, maybe dynamically modifying a stored procedure?
I have no idea about DB2 but if you need the count of different tables for analyzes you should have a look to this:
SELECT OBJECT_NAME(object_id) table_name,
row_count
FROM sys.dm_db_partition_stats
WHERE index_id < 2
AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
The row_count might differ a little bit, but you should try. The less I/O overhead is dramatic!
3) Again, in my DBA database, I have a list of aliases for specific objects (to simplify development time between multiple databases). Any time this list is changed I update all applicable databases with the new alias. I could do this with a custom .Net application, but T-SQL works fine, when I use cursors.
I have no idea about your "aliases" but as already written for administrative work it makes sometimes sense.
I am not advising the use of cursors where other, more efficient solutions (CTE, Tally Table, etc) would work, but I do believe they have their place (at least in 2005).
I would say cursors should not be used within operational processes. Cursors become a problem if the front-end applications or any data processing jobs work with. But if you have an admin script which takes some seconds and it is only for test/development or administration needed and no other processes may be blocked from it is okay to use a cursor 😉 .
Greets
Flo
April 2, 2009 at 9:39 am
Hi
You can check out the below link
http://www.sqlservercentral.com/articles/Advanced+Querying/2785/
April 2, 2009 at 9:51 am
I'll go with they have their place in admin scripts and a few things like that. Timothy's stuff sounds about right.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:59 am
Flo,
Thanks for the feedback!
I am glad to hear that I am not "way off base" in my thinking... didn't think I was, but that's what this site is good for... pointing out what you don't know you don't know!
I have been meaning to try the "sp_MSforeachdb" and "sp_MSforeachtable" stored procedures, but I have not created any admin stored procedures since learning about them (and as I haven't used them I often forget that they are there!)
As far as CLR I actually have found another important use for it... though maybe there is a better way. In my development environment I have created a CLR function to get the build SQL for different objects (tables, synonyms, users, etc) on different servers. Using this I can compare dev to production and automatically identify any object-related differences. I know that there are third party tools that I could use for this, but I have more fun building!
For my second example, I actually am using sys.dm_db_partition_stats for the SQL Server side tables... the cursor is only for querying the linked DB2 server.
And for the third example... my "aliases" translate to synonyms.
Again, thanks for the input!
April 2, 2009 at 10:40 am
Hi Timothy
As far as CLR I actually have found another important use for it... though maybe there is a better way. In my development environment I have created a CLR function to get the build SQL for different objects (tables, synonyms, users, etc) on different servers. Using this I can compare dev to production and automatically identify any object-related differences. I know that there are third party tools that I could use for this, but I have more fun building!
Yeah... SMO is a fine thing! But I'm currently investigating the Visual Studio Database Professional Edition and the 2008 Edition seems quiet well for Deployment. You can specify a destination database and create a deployment script which automatically creates all your missing objects, indexes, constraints, schema, procedures, ... . The SQL Editor is still... well... but this can be done by SSMS.
And for the third example... my "aliases" translate to synonyms.
I've not been sure if you mean synonyms...
Greets
Flo
April 2, 2009 at 1:29 pm
Florian Reischl (4/2/2009)
Hi TimothyAs far as CLR I actually have found another important use for it... though maybe there is a better way. In my development environment I have created a CLR function to get the build SQL for different objects (tables, synonyms, users, etc) on different servers. Using this I can compare dev to production and automatically identify any object-related differences. I know that there are third party tools that I could use for this, but I have more fun building!
Yeah... SMO is a fine thing!
That's true, except that you cannot use SMO from CLR in SQL Server 2005 (I believe that this is relaxed slightly in 2008).
[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]
April 2, 2009 at 2:10 pm
RBarryYoung (4/2/2009)
That's true, except that you cannot use SMO from CLR in SQL Server 2005 (I believe that this is relaxed slightly in 2008).
Hi Barry!
Really? I must confess that I'd never tried from a CLR procedure. I'm using it from .Net apps...
Thanks a lot for this information!
I've just tested on SQL Server 2008 and it seems that it still doesn't work...
Greets
Flo
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply