This is because the way the decision is made to use a dynamic-like or a static-like plan is fundamentally different (see below). However, performance testing should be done before a final decision is made for a particular application. On balance, fast_forward is a little better. Fast_forward cursors take a more balanced approach, choosing a static plan if it looks better. The reason this is a problem is that sometimes, the best dynamic plan is much, much worse than a static one. Read_only forward_only cursors are dynamic cursors, and dynamic cursors use a dynamic plan if one is available. Read_only forward_only cursors are sufficient for many apps. Why do we need them?įast_forward is redundant. It does not downgrade to other cursor models, like dynamic and keyset do.Īren't fast_forward cursors redundant? We already have read_only forward_only cursors. It's a cursor model equivalent to read_only, forward_only that compiles to a static-like or dynamic-like cursor plan. What's a fast_forward cursor in a nutshell? Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. But my concern here is to demystify fast_forward cursors, about which Books Online say very little, perhaps worse than nothing: SQL Server 2008 Books Online's section on cursors There is lots of further background on the models and their differences in SQL Server engine has four server cursor models: static, keyset, dynamic, and fast_forward. There is simply no better way to tell SQL Server to "give me the next row(s)." Mapping very well to screen-at-a-time or window-at-a-time viewing and scrolling through large data sets. Because in many cases (though not all), server cursor processing is incremental and on-demand, server load and data transmission can be reduced.ģ. Reducing unnecessary processing of unused data when a client application stops reading data early. If the data that is to be retrieved will be consumed all at once, and there is no need for positioned updates or scrolling, default result sets are recommended.Ģ. This advantage is more perceived than actual, and does more harm than good. Matching very well the iterative model of programming that non-database programmers are most comfortable with. Server cursors' popularity is derived from three advantages:ġ. Many applications and client libraries have iteration models, but server cursors are the only way for SQL Server SQL clients can use a server cursor to fetch results of a query one-at-a-time, or in batches of N-at-a-time, rather than by the usual all-at-a-time, firehose, default query result set. It consists of a query execution and some runtime state, including a current position. So I thought I'd give it a go.Ī server cursor is a cursor managed by SQL Engine. Fast_forward cursors are very popular as an alternative to read_only forward_only cursors, but their inner workings are not well-publicized. SQL Server's server cursor model is a critical tool to many application writers.
0 kommentar(er)
