Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: executing stored proc across network

    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?

    Here is the code which I am using:


    bool SOMEFUNCTION( ..... )
    _CommandPtr objCmd = NULL;
    objCmd.CreateInstance(__uuidof(Command));
    bool retval = true;

    try
    {
    objCmd->ActiveConnection = connection;
    objCmd->CommandText = "sp_as_played_list";
    objCmd->CommandType = adCmdStoredProc;
    recordset->CursorLocation = adUseClient;

    // WORKS VERY SLOWLY - get to have Static cursor
    recordset->Open( (IDispatch *)objCmd, vtMissing, adOpenStatic, adLockReadOnly, adCmdStoredProc );

    // Or - WORKS VERY QUICKLY - adOpenForwardOnly default cursor
    recordset = objCmd->Execute( NULL, NULL, adCmdStoredProc);

    if(recordset->State == adStateClosed)
    {
    retval = false;
    }


    thanks

    aidan

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •