|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 2,937,
Visits: 1,695
|
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 2:57 PM
Points: 1,691,
Visits: 212
|
|
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.
Urbis, an urban transformation company
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 11:09 PM
Points: 192,
Visits: 151
|
|
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
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, February 09, 2009 7:44 PM
Points: 282,
Visits: 37
|
|
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!
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Friday, December 18, 2009 9:17 AM
Points: 99,
Visits: 126
|
|
| 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? :)
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: 2 days ago @ 5:38 PM
Points: 786,
Visits: 333
|
|
I think this should do
SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules WHERE definition LIKE '%CURSOR%'
:)
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 11:09 PM
Points: 192,
Visits: 151
|
|
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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Friday, March 12, 2010 5:24 AM
Points: 1,287,
Visits: 597
|
|
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...
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 2,937,
Visits: 1,695
|
|
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!
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 2,937,
Visits: 1,695
|
|
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. 
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! :D
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
|
|
|
