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 Monday, July 07, 2008 10:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 5,793, Visits: 8,003
Comments posted to this topic are about the item Cursor optimization


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #529742
Posted Tuesday, July 08, 2008 1:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 5:31 PM
Points: 2,953, Visits: 435
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
Post #529802
Posted Tuesday, July 08, 2008 1:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 2:20 AM
Points: 216, Visits: 169
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
Post #529806
Posted Tuesday, July 08, 2008 2:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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!
Post #529826
Posted Tuesday, July 08, 2008 3:49 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
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? :)
Post #529844
Posted Tuesday, July 08, 2008 3:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 1:45 AM
Points: 1,165, Visits: 742
I think this should do

SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules WHERE definition LIKE '%CURSOR%'

:)
Post #529850
Posted Tuesday, July 08, 2008 4:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 2:20 AM
Points: 216, Visits: 169
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
Post #529853
Posted Tuesday, July 08, 2008 4:38 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711
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...
Post #529876
Posted Tuesday, July 08, 2008 5:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 5,793, Visits: 8,003
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
Post #529917
Posted Tuesday, July 08, 2008 5:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 5,793, Visits: 8,003
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
Post #529923
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse