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
    &i+=1
    if &i = 100
       return
    endif
endfor

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
  &i+=1
  //if &i = 100
  //   return
  //endif 
endfor

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

See Also