I am trying to execute stored procedures accross a network, which return recordsets. The problem I'm having is as follows:
I need to return the recordset with cursor type adOpenStatic and not adOpenForwardOnly, which is the default when using the execute command. So I decided to use the open cammand instead which seemed to work.
the probelm is that it takes a really long time to process the open command, but the execute is very fast. Does anyone know why this might be?
When you use adOpenForwardOnly the cursor is asynchronous, so it doesn't need to retrieve any rows at all before returning control to the user code. When you use adOpenStatic, it forces the entire result set to be returned before control returns to the user code.
The execute takes the same amount of time for both calls (after you factor out any parsing or cache loading). It is just that control returns after the first TDS ack (essentially once the sproc has started to run) for adOpenForwardOnly, and control returns only after the last packet of data is received for adOpenStatic. This makes what appears to be a huge difference to the client PC in performance, when there is really little or no difference on the server.