Multiple ADOQuery on same connection, performances go down because of serverside cursors...

Hi everyone,

I am coding under Delphi a software which will be using a SQL Server 2000. I am using ADO components and have the latest MDAC: 2.8 SP1.

The software is using a lot of TADOQuery objects which are connected to the same TADOConnection.
The TADOQuery having for options :
- cursorlocation : useclient
- cursortype : static
- locktype : optimistic

On the first use of a TADOQuery in order to execute any type of request, the processing is fast.
If I am using this same object for others requests, everything is fine and as fast as the first request.
But as soon as I am using another TADOQuery, performances are going down.

In order to give some numbers, some requests can take as much as 17 seconds to be executed. The same request would take at most 100 milliseconds if it were executed with the first TADOQuery.

It is not a problem concerning indexes; if I try to use the request analyzer, all my requests are processed quickly.

I used the SQL Profiler to see the details and saw some interesting things :
the first uses of TADOQuery are of type SQL:BatchCompleted
but switching to another TADOquery make it use another type : RPC:Completed.
Contents of this RPC can be a declaration or an execution of cursor.

More precisely, if I am using only one ADOQuery, I see one line in the Profiler for every block of code opening/reading contents/closing.
But using multiples ADOQuery, I see one declaration of cursor for the opening and then every reading produces one cursorfetch.
So, this declaration of cursor may take a long of time and every fetch too and if there is many lines to send to the client, the network is used for each line to send.

Better, using an TADOQuery to make a request and then simply initializing another one by changing the SQL property and then executing a second time the first TADOQuery makes this last execution used as a cursor serverside.
The problem should then on the side of the client but I can not figure where.

I hope having been as clear as needed !
My wishes is to not have recode the whole database side of our software because it is huge and badly designed so it will be hard to test everything fine.
So before recoding I wish to continue the analysis.

So does anyone knows why is happening this serverside thing ?
And is there a simple way to make it not happen ?

Thanks to everyone for any future help !

Is this issue still alive? I have several years experience with Delphi and only a little with sql server, but I can never get ADO to perform quickly especially since our database holds pictures. That's why we opted to connect to sql server through the bordland db engine, since queries are always quick this way. Is this an option for you?