July 7, 2008 at 10:02 pm
Comments posted to this topic are about the item Cursor optimization
July 8, 2008 at 1:34 am
Hmm... I got this wrong, based on the BOL it's supposed to be FAST_FORWARD that is optimized for performance, but apparently, based on your paper, it's not. But I'm happy to lose the point as I learn something new again. I take my hat off to you for doing the testing.
July 8, 2008 at 1:41 am
I lost the point as well, fortunately for the better of learning something new.
This was very interesting to me, as all the years of just trusted BOL in being correct, without doing my own investigation into performance testing.
Going forward I will definitely remember this one, but then again I hope I don't have to make use of cursors going forward, thanks for the tip Hugo
July 8, 2008 at 2:50 am
A very worth while read, but I'm not sure if I agree with the conclusion that STATIC is always faster than FAST_FORWARD. The table fitting in cache and point 5 of the conclusion, "It Depends", being my two main gripes. That said, whenever I do use CURSORS (???) I'll make sure I test them with STATIC.
Cheers!
July 8, 2008 at 3:50 am
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? 🙂
July 8, 2008 at 3:56 am
I think this should do
SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules WHERE definition LIKE '%CURSOR%'
🙂
July 8, 2008 at 4:04 am
i use this to find text in any db object:
-----------------------------------------------------
declare @criteria as varchar(32)
set @criteria = 'tadMSVRAreaDetail'
--:Utility proc for searching for dependents.":--
Select 'Checking references for (' + @criteria + ')'
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
--check for dependencies.
exec sp_depends @criteria
-----------------------------------------------------
I found it somewhere on the net, so i 'm not taking credit for this code, but I can tell you it's very handy
July 8, 2008 at 4:38 am
Learned something new today... STATIC is faster, tested in a sproc I was trying to optimize.
Will probably end up rewriting it completely, need more speed...
July 8, 2008 at 5:38 am
Iggy, Marius, Mark: Thanks for the kind words! 🙂
This is the first time I submitted a QotD, but with reactions like this, I'm sure I'll try to submit some more!
July 8, 2008 at 5:47 am
Dave F (7/8/2008)
A very worth while read, but I'm not sure if I agree with the conclusion that STATIC is always faster than FAST_FORWARD. The table fitting in cache and point 5 of the conclusion, "It Depends", being my two main gripes. That said, whenever I do use CURSORS (???) I'll make sure I test them with STATIC.Cheers!
Dave,
You actually made a very good point about the influence of the cache. I must admit that I completely overlooked that point when doing my tests. :blush:
I have just done some tests with a huge table (100,000 rows with 8,016 bytes each - two integer columns and a char(8000)) on a memory-constrained system (simulated by setting the max server memory option to 64 MB and then restarting the service). It shows that you are completely right. If I use a cursor to fetch only the two integer columns, FAST_FORWARD completes in ~ 18 seconds whereas STATIC takes ~21.5 seconds - and if I process the char(8000) column as well, FAST_FORWARD still takes only ~ 18.5 seconds, and STATIC goes up to almost 80 (!) seconds.
So it seems that for tables larger than the available cache, FAST_FORWARD does indeed deliver as advertised in Books Online. I'll shoot off a mail to Steve, asking him to adjust the question and award back points where appropriate.
Thanks for putting me straight! 😀
July 8, 2008 at 5:49 am
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
July 8, 2008 at 5:49 am
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.
July 8, 2008 at 5:55 am
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).
July 8, 2008 at 5:56 am
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.... 🙂
July 8, 2008 at 6:06 am
🙂 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.
Viewing 15 posts - 1 through 15 (of 65 total)
You must be logged in to reply to this topic. Login to reply