Re: Performance Problem with Stored Procedure calls to Sql Server

From: Doug Stoltz (NoSpam_at_MyEmail.com)
Date: 08/04/04

  • Next message: Erland Sommarskog: "Re: System.Data.OleDb.OleDbException on INSERT command" 
    Date: Wed, 4 Aug 2004 16:42:06 -0400

    Look at this in SQL profiler. You can specify to filter on disk reads/writes
    this may provide more info.

    "Jack Broughton" <JackBroughton@discussions.microsoft.com> wrote in message
    news:CA2A6B6D-B36E-4D5A-95BE-995569953350@microsoft.com...
    > I am struggling with bizarre performance problem trying to retrieve TEXT
    data types from SQL Server 2000. The accessing program is VB6 running on a
    W2K Server using ADO 2.7 SP1 Refresh. All server Service Packs are up to
    date.
    >
    > The table is simple enough. it has 4 columns: Key, LastAccessDateTime,
    Name, Value and is used in a session manager.
    >
    > The Value column is a TEXT data type and holds large strings, typically
    between 10K to 150K. The strings contain XML.
    >
    > I am finding that different lengths of data in the value column take very
    different amounts of time to retrieve. It is not necessarily the larger
    strings that take more time.
    >
    > E.g. I can retrieve a 50K string in 50ms, while a 13K string is taking
    600ms. The times are consistent for any given length, but a difference of a
    few bytes seems to vary the time considerably.
    >
    > I have tried calling the stored proc from a connection object and from a
    command object and the results are the same.
    >
    > Any explanation as to why this should happen or a resolution would be
    greatly appreciated.
    >
    > I have analyzed the query in Query analyzer and the duration and cpu don't
    appear to reflect the extra time. The problem appears to be with the
    interaction between ADO and SQL server.
    >
    > Here is my VB code:
    >
    > Private Function GetSelectedRecord(ByVal sSessionKey As String, _
    > ByVal sVariableName As String, ByVal
    sDSNSource As String, _
    > vntError As Variant) As Variant
    >
    > Const sPROC_NAME As String = msOBJ_NAME & ".GetSelectedRecord"
    >
    > On Error GoTo Err
    >
    > Dim oRs As ADODB.Recordset
    > Dim oCmd As ADODB.Command
    > Dim oCn As ADODB.Connection
    > Dim sProcName As String
    > Dim vData As Variant
    >
    '---------------------------------------------------------------------------
    -------------------
    > '--- Set the parameters and get a recordset
    >
    '---------------------------------------------------------------------------
    -------------------
    > sProcName = "prAPP_Get_SessionValue"
    >
    >
    > Set oCn = CreateObject("ADODB.Connection")
    > With oCn
    > .CursorLocation = adUseClient
    > .ConnectionTimeout = 15
    > .IsolationLevel = adXactReadUncommitted
    > .ConnectionString = sDSNSource
    > .Open
    > End With
    >
    > Set oCmd = CreateObject("ADODB.Command")
    > With oCmd
    > Set .ActiveConnection = oCn
    > .CommandTimeout = 30
    > .CommandType = adCmdStoredProc
    > .CommandText = sProcName
    > .Parameters.Append .CreateParameter("sSessionKey", adVarChar,
    adParamInput, LenB(sSessionKey & "") + 1, sSessionKey)
    > .Parameters.Append .CreateParameter("sVariableName", adVarChar,
    adParamInput, LenB(sVariableName & "") + 1, sVariableName)
    > End With
    >
    > Set oRs = CreateObject("ADODB.Recordset")
    > With oRs
    > Set .Source = oCmd
    > .CursorLocation = adUseClient
    > .CursorType = adOpenStatic
    > .LockType = adLockReadOnly
    > .Open
    > .ActiveConnection = Nothing
    > CloseObject oCn
    > End With
    >
    > If Not oRs.EOF Then
    >
    '---------------------------------------------------------------------------
    -------------------
    > '--- Get the data from the recordset
    >
    '---------------------------------------------------------------------------
    -------------------
    > vData = oRs.GetRows
    > End If
    >
    >
    '---------------------------------------------------------------------------
    -------------------
    > '--- Return the array
    >
    '---------------------------------------------------------------------------
    -------------------
    > GetSelectedRecord = vData
    >
    > CloseObject oRs
    > Set oRs = Nothing
    > Set oCmd = Nothing
    > Set oCn = Nothing
    >
    > --
    > CSC Systems Architect

  • Next message: Erland Sommarskog: "Re: System.Data.OleDb.OleDbException on INSERT command"

    Relevant Pages