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:
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.