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! 😀