Performance and memory implications depending Drivers support for multiple data readers

Unofficial Content
This documentation is valid for:

ADO.NET drivers may support or not a feature called 'multiple server cursors per connection'. Having or not this feature has consequences in performance and memory consumption in applications.
This article describes how to mitigate the absense of the feature.

Let's state the case of DriverA that has the feature, and DriverB that doesn't have it.

When the driver does not support multiple server cursors reading data at the same time (DriverB), when you nest reads, all data of the outer cursor has to be read first and loaded to memory, and then the nested cursor can start executing.

For example, using DriverB, the following code will load all the data of Table1 into memory and then execute some action (New, For Each, etc.) 100 times.
When using DriverA, it will only load into memory approximately 100 records from the DB (depending on the size of the blocks it loads from the database); after loading the first ones, it already starts executing some action.

for each Table1
    new ... endnew | call to a proc | update | for each endfor | delete | servernow | any other BD access
    if &i = 100

To mitigate this, use appropriate conditions or paging methods.

for each Table1 SKIP 0 COUNT 100
  new ... endnew | call to a proc | update | for each endfor | delete | servernow | any other BD access
  //if &i = 100
  //   return

Specifically, some drivers for PostgreSQL and MySQL do not support this feature: 



In the case of SQLServer, it is supported from SQLServer 2005 (9.x) although it is disabled by default. It can be turned on with the following string in the Additional connection string attributes property MultipleActiveResultSets=True

When MultipleActiveResultSets is disabled, specific stored procedures of SQLServer are used to define and execute server cursors, such as sp_cursorprepexec, sp_cursorfetch, sp_cursorexecute, sp_cursorclose, sp_cursorunprepare. Those stored procedures are supported by a wide range of SQLServer versions, otherwise, this error will appear:

Stored procedure sp_cursorprepexec does not exist or is not supported. 

In that case, turn on MultipleActiveResultSets.

See Also