SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor optimization


Cursor optimization

Author
Message
Marius Els
Marius Els
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 Visits: 180
Schweet..

Just make sure your QotD isn't confusing, misleading, can be misinterpreted or anything, cause i've seen where the guys were taken to the cleaners because the submitted question was either confusing, misleading, or misinterpreted from whomever's point of view, and yes, the question was submitted with good intentions and then badly received....

we all should strive to submit at least 1 QotD, one can only benefit from it

all the best Hugo
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11206 Visits: 12005
davidthegray (7/8/2008)
Does anybody have a ready made query to find out the list of all the stored procedures in a db having the word 'CURSOR' inside their SQL body text? Smile


Hi David,

Emamet already gave a query that works for SQL Server 2005 (and probably 2008 as well).

For SQL Server 2000, you can use the query posted by Marius, though there are some caveats with it that I'll point out in a seperate reply.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11206 Visits: 12005
Marius Els (7/8/2008)
i use this to find text in any db object:


Hi Marius,

Mostly good, solid code, but there are a few caveats:

Select distinct sysobjects.id, name, type
from syscomments, sysobjects
where syscomments.id = sysobjects.id
and ( text like '%' + @criteria + '%' OR text like @criteria + '%' )
order by name


Using syscomments and sysobjects is fine for SQL Server 2000, but they are deprecated since SQL Server 2005 introduced the new system views.
One (minor) problem with this code is that syscomments stores a stored procedure text in chunks of 8000 characters. If the word you are searching for is just on the border of two chunks, it'll be cut in halve and this code won't find it. The chances of that are, of course, prettty slim Smile
Finally, you can simplify the fourth line in the query to "and text like '%' + @criteria + '%'", snice the second part (searching for @criteria + '@') will never match rows not matched by the first part.

--check for dependencies.
exec sp_depends @criteria


This is fine when you are looking for table, view, function, or stored procedure names (though sp_depends to be less than accurate under some circumstances), but not when you are looking for procedures that use specific keywords (such as CURSOR).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11206 Visits: 12005
Mark Horninger (7/8/2008)
Will probably end up rewriting it completely, need more speed...


Mark,

If possible, try eliminating the cursor completely rather than rewriting it using other options.... Smile


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Craig@Work
Craig@Work
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 326
Smile Was a bit surprised at FAST_FORWARD being wrong ... seemed such an 'obvious' answer that there was bound to be some additional complexity somewhere.

Also always with performance, it depends on context :/ .

Appreciate the blog entry supplied though. More knowledge always good.
Marius Els
Marius Els
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 Visits: 180
Hugo

Thanks for the feedback on my query

I have to admit, i haven't worked with SQL 2005 yet, so the fact that the mentioned tables in SQL2005 is being depreciated, was unknown to me w00t, noted though...

I'll update the like clause as suggested.

I also noted the use of sp_depends.
John Laskey
John Laskey
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 238
I got this wrong as well... I selected FAST-FORWARD... It you use STATIC this can cause temp tables to be created in tempdb, which can lock out other process and cause other issues. FAST-FORWARD will at least use some internal performance optimizations. In order to assure maximum performance I would think STATIC should be avoided.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41231 Visits: 32666
I got this wrong as well. I've seen both STATIC & FAST_FORWARD work better in different circumstances. "It Depends" should have been the right answer on this one.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
davidthegray
davidthegray
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 130
Thanks for the answers to my question. Looks like in my db I have 23, among sp's and triggers, using cursors, all written several years ago, which makes about 1/10 of the total. In the recent years I mostly use dynamic SQL strings sent to the db by the client application (mostly ASP.NET), which is also deprecated, but I find that managing the catalog of objects in order was becoming unconfortable as the number of sp's was increasing. Otherwhise today I'd probably have 500 stored procedures in my db.

How I'd like if MS would put a tree structure in the db object names, allowing us to create folders to keep tables, views and stored procedures reasonably ordered instead of having to scroll between hundreds of names when searching for something...

Am I the only one longing for this feature? Sorry for going a bit off-topic...
Eric Mamet
Eric  Mamet
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1627 Visits: 893
With 2005, you can use "Schemas" to achieve a little bit of that.

To a large extent, working with flat files for each object also helps a lot.
You could put these scripts in folders and use any text based tool to search, which is a lot easier (no offence...) than TSql.

I have done that for years... (please, don't ask) and was extremely pleased to see that Microsoft finaly decided to do that sort of thing too: Visual Studio Team Edition for Database Developer... which is unfortunately far too expensive Crying
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search