Share via

adUseClient vs adUseServer

  • Article

Question

Wednesday, April 15, 2015 4:15 PM

There's a system with multiple frontend PCs and a SQL Server backend.

I have 2 choices to configure the frontend's recordsets I create: to set its cursor location at client side (adUseClient) or in server side (adUseServer). I already know some of the pros/cons of each case, but I have a specific question regarding what is sent/processed by the client/server.

Suppose I have a query that joins 3 tables.

1 - "If CursorLocation = adUseServer, the query is processed at server, then only the data set is sent to the client"

2 - "If CursorLocation = adUseClient, the whole 3 tables are sent to the client, the client process the query and then build the data set."

3 - "Closing a RecordSet in adUseClient is much faster than in adUseServer".

If 1 and 2 are correct, the best way to work with large recordsets is to use adUseClient, but use a Stored Procedure to build the data sets in the server. Right?

If 2 is correct, using adUseClient uses SQL Server as file server like Access?

If 3 is correct, I'd like to know why.

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

All replies (4)

Wednesday, April 15, 2015 5:30 PM

2 is incorrect. Query execution is always 100% at the server.

A server-side cursor may create a temp table to store the results, and in any case needs to get cleaned up on the server when you close the recordset. 

BTW is there a good reason you're using such an ancient API to access SQL Server, instead of .NET's SqlClient or ODBC?

David

David http://blogs.msdn.com/b/dbrowne/

Wednesday, April 15, 2015 6:30 PM

Thank you.

"2 is incorrect. Query execution is always 100% at the server."
So, this means that the amout of data transferred from server to client in both cases are aproximately the same?

"BTW is there a good reason you're using such an ancient API to access SQL Server, instead of .NET's SqlClient or ODBC?"
Yes. I use Excel VBA.

My client's provider is SQLNCLI11. Would it be better using a ODBC;Driver={SQL Server Native Client 11.0} instead?

There is something I don't understand. In a VPN enviroment, why using adUseClient is much slower to get a recordset than adUseServer? Sample code:

    oConnection.ConnectionString = sConnectionString
    oConnection.Open
    csQuery = "Report.BigQuery"
    
    'adUseClient
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    nTimer = Timer
    rst.Open csQuery, oConnection, adOpenForwardOnly
    Debug.Print "Time adUseClient: " & Format(Timer - nTimer, "0.0") & " seconds."
    rst.Close
    Set rst = Nothing
    
'
    
    'adUseServer
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    nTimer = Timer
    rst.Open csQuery, oConnection, adOpenForwardOnly
    Debug.Print "Time adUseServer: " & Format(Timer - nTimer, "0.0") & " seconds."
    rst.Close
    Set rst = Nothing

I got ~28 seconds with adUseClient and only ~7 seconds with adUseServer (medium).

When testing locally, both queries are instantaneous.

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Wednesday, April 15, 2015 7:32 PM | 1 vote

For a valid test you need to set both CursorType and CursorLocation, and actually read all the rows.

Read Using Cursors with ADO

I think your client cursor is static, so it's fetching all the rows.  And your server cursor is not fetching all the rows on open, and may not even be finished executing the query.

David

David http://blogs.msdn.com/b/dbrowne/

Wednesday, April 15, 2015 7:48 PM

Thank you, that'll give me a while to read the entire ADO content from the website.

Just a final question: "Query execution is always 100% at the server"

Do you know if there is any MS page stating that? I need a link for a discussion.

Felipe Costa Gualberto - http://www.ambienteoffice.com.br