VB6 Recordset "Open" taking more time to show result as compared to backend

Question

I am using a query to find data. Same query taking less time i.e 2 sec to execute from backend. But in code same query taking more time i.e 30 sec in recordset.open. Database : Sybase

Thanks

Code Sample :

    Dim rsRoute As New ADODB.Recordset 
    ---------------------------------------------

    If rsRoute.State = 1 Then rsRoute.Close
    Set rsRoute = New ADODB.Recordset
    Set rsRoute.ActiveConnection = con
    rsRoute.CursorLocation = adUseClient
    rsRoute.CursorType = adOpenKeyset
    rsRoute.LockType = adLockBatchOptimistic
    strCmd = " select * from Table where CoumnVal =1 "
    con.Errors.Clear
    On Error Resume Next
    rsRoute.Open strCmd

Answer 1

There are several types of CursorsType and two different CursorLocation varieties. On the Sybase database (ASE back in the day) the performance differs wildly depending on what you choose. Try both client-side and server-side cursors and see what happens.

If you just need to loop through the result once, select the adOpenForwardOnly cursor type. It usually results in the best performance.

EDIT: Based on the code you posted, try a) not locking anything (e.g LockType), b) using a adOpenForwardOnly cursor, a) the keep the cursor on the server (adUseServer)