SYSK 4: Why are the read only-cursors (i.e. "firehose cursors") faster than server side cursors?

Because with “firehose cursors”, when the query is executed, the results are immediately (i.e. without a FETCH command) sent to pre-reserved network buffers, read by the client (i.e. . Network write operations will succeed and free up used buffers as long as the client driver is reading from the network. If client is not consuming results, at some point network write operations will block, network buffers will fill up in the server, and execution must be suspended, holding on to state and execution thread until client driver catches up reading.   In essence, the results are “pushed out” as they become available. Cursors on the other hand, require a roundtrip to the server for each fetch operation.

This applies not only for results of the SELECT statement, but also for any errors, output of PRINT command, result row count (if NOCOUNT is off), etc.


Comments (1)

  1. Sergey says:

    Source URL is invalid, probably expired.