Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Cursor optimization Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2008 5:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:06 AM
Points: 217, Visits: 172
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
Post #529924
Posted Tuesday, July 8, 2008 5:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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? :)


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
Post #529926
Posted Tuesday, July 8, 2008 5:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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 :)
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
Post #529931
Posted Tuesday, July 8, 2008 5:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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.... :)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #529934
Posted Tuesday, July 8, 2008 6:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 5, 2014 6:18 AM
Points: 1,140, Visits: 326
:) 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.

Post #529943
Posted Tuesday, July 8, 2008 6:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:06 AM
Points: 217, Visits: 172
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 , noted though...

I'll update the like clause as suggested.

I also noted the use of sp_depends.
Post #529954
Posted Tuesday, July 8, 2008 6:32 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, June 7, 2013 6:57 AM
Points: 642, Visits: 200
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.
Post #529965
Posted Tuesday, July 8, 2008 6:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 14,002, Visits: 28,377
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #529971
Posted Tuesday, July 8, 2008 6:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 19, 2010 6:47 AM
Points: 100, 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...
Post #529975
Posted Tuesday, July 8, 2008 6:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:31 AM
Points: 1,191, Visits: 793
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
Post #529977
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse