MTS/database performance issue

Our calls to a component are taking a long time and sometimes timing out when the calls are done inside of MTS.

The same methods execute immediately when run outside of MTS.

I have debugged it and found where the big performance hit is.

cmd.Execute is the first one.  It takes about 20 seconds.

And the "End Function" line on the method also takes about 20 seconds.

I believe (though maybe incorrectly) that it looks like MTS is having a problem beginning and committing the transactions, due to the locations of the performance hits.

The stored proc just deletes things from about seven tables and returns.

I can begin a transaction and commit it in in ISQL and I can begin a distributed transaction and commit it in ISQL with no problem.

We are using MTS with a VB6 component which is set to require transactions.

Any ideas?
KLRDERAsked:
Who is Participating?
 
NetminderCommented:
Per Admin request, points refunded and question closed.

Netminder
CS Moderator
0
 
rpaiCommented:
Did you check if your SQL statement takes 20sec to execute even outside MTS?
0
 
KLRDERAuthor Commented:
Outside of MTS, they execute in less than half a second.

If I set transaction mode to "Uses Transactions", sometimes this helps when the cmd.Execute is called.  However, it still continues to hang on the "End Function" line -- I assume this is the object context Deactivate time.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
rpaiCommented:
What is the Transaction Timeout (time) set to in MTS? If your object takes longer than what is set, then it quite possible that your application hangs. Try increasing the Transaction Timeout in MTS if that helps.
0
 
rpaiCommented:
Also, the object is deactivated only when you release the reference to the object by setting it to Nothing and not at the 'End Function' line.
0
 
KLRDERAuthor Commented:
transaction time out is 20 seconds.

The problem is not a timeout.  The problem is that it's taking so long to do a transaction which should take less than half a second to accomplish.
0
 
KLRDERAuthor Commented:
transaction time out is 20 seconds.

The problem is not a timeout.  The problem is that it's taking so long to do a transaction which should take less than half a second to accomplish.
0
 
KLRDERAuthor Commented:
Also, the object is deactivated only when you release the reference to the object by setting it to Nothing and not at the 'End Function' line.

Are you sure?  I thought that deactivation (as opposed to terminate) occurred when the method finished.  I've put messageboxes once to confirm that.
0
 
rpaiCommented:
An MTS object is deactivated when any of the following occurs:
- The object requests deactivation
- A transaction is committed or aborted.
- The last client releases the object. (which is the 'End Function' line)

Also, take a look at this article. This might explain the reason you see 'UsesTransaction' work fine.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224320
0
 
KLRDERAuthor Commented:
Also, the object is deactivated only when you release the reference to the object by setting it to Nothing and not at the 'End Function' line.

Are you sure?  I thought that deactivation (as opposed to terminate) occurred when the method finished.  I've put messageboxes once to confirm that.
0
 
KLRDERAuthor Commented:
Also, the object is deactivated only when you release the reference to the object by setting it to Nothing and not at the 'End Function' line.

Are you sure?  I thought that deactivation (as opposed to terminate) occurred when the method finished.  I've put messageboxes once to confirm that.
0
 
rpaiCommented:
Please do not use the REFRESH button on your browser. Use the Reload Question instead.
0
 
KLRDERAuthor Commented:
Also, the object is deactivated only when you release the reference to the object by setting it to Nothing and not at the 'End Function' line.

Are you sure?  I thought that deactivation (as opposed to terminate) occurred when the method finished.  I've put messageboxes once to confirm that.
0
 
rpaiCommented:
Did you try running the object in MTS with 'Does Not Support Transaction' set to check if it is the Transaction that is an issue?
0
 
KLRDERAuthor Commented:
Changing it to "NotAnMTSObject" makes it behave as it should.
0
 
rpaiCommented:
Please post your code here.
0
 
KLRDERAuthor Commented:
Function RunSQL(ByVal strSQL As String, ConnectionString As String, _
                            ParamArray params() As Variant) As Variant
    On Error GoTo errorHandler
   
    ' Set up Command and Connection objects
    Dim cmd As ADODB.Command
    Set cmd = CtxCreateObject("ADODB.Command")
       
    'Run the procedure
    cmd.ActiveConnection = GetConnectString(ConnectionString)
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdStoredProc
    collectParams cmd, params(0)
   
    Set RunSQL = cmd.Execute(RunSQL)
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing

    Exit Function
   
errorHandler:
    RaiseError g_modName, "RunSQL(" & strSQL & ", ...)"
End Function

Sub RaiseError(module As String, functionName As String)
    '' Save the error information before calling CtxSetAbort in case it has side effects
    Dim lErr As Long
    Dim sErr As String
    Dim sSource As String
   
    lErr = VBA.Err.Number
    sErr = VBA.Err.Description
    sSource = VBA.Err.Source
   
    On Error Resume Next
    CtxSetAbort
    On Error GoTo 0
   
    Err.Source = sSource
    Err.Raise lErr, SetErrSource(module, functionName), sErr
End Sub

Sub collectParams(ByRef cmd As ADODB.Command, ParamArray argparams() As Variant)
    On Error GoTo ErrHandler

    Dim i As Integer, vValue As Variant
    Dim oParam As ADODB.Parameter
    Dim sName As String
   
    For i = LBound(argparams) To UBound(argparams(0))
        If TypeName(argparams(0)(i + 3)) = "String" Then
            vValue = IIf(argparams(0)(i + 3) = "", Null, argparams(0)(i + 3))
        ElseIf IsNumeric(argparams(0)(3)) Then
            vValue = IIf(argparams(0)(i + 3) < 0, Null, argparams(0)(i + 3))
        Else
            vValue = argparams(0)(i + 3)
        End If
       
        sName = argparams(0)(i)
        Set oParam = cmd.Parameters(sName)
   
        'Replace the information for the parameter.
        oParam.Type = argparams(0)(i + 1)
        oParam.Size = argparams(0)(i + 2)
        oParam.Value = vValue

        i = i + 3
    Next i
ExitProc:
    Exit Sub

ErrHandler:
    're-raise error with more specific information
    Err.Raise vbObjectError + Err.Number, Err.Source, _
        Err.Description & vbNewLine _
        & "Error occurred while assigning a value to Parameter name '" & sName & "'."

End Sub
0
 
KLRDERAuthor Commented:
When I implement the ObjectContext interface in the class, I find that it is deactivating at the point it exits the method call.
0
 
EDDYKTCommented:
I usually create an object inside the ObjectControl_Activate

i.e.

Option Explicit

Implements ObjectControl
private cmd

Private Sub ObjectControl_Activate()
    Dim CTX As ObjectContext
   
    On Error Resume Next
    Set CTX = GetObjectContext
    If CTX Is Nothing Then Exit Sub
    Set cmd = CTX.CreateInstance("ADODB.Command")
    Set CTX = Nothing
End Sub

Private Function ObjectControl_CanBePooled() As Boolean

    On Error Resume Next
    ObjectControl_CanBePooled = True
End Function

Private Sub ObjectControl_Deactivate()

    On Error Resume Next
    If (Not cmd Is Nothing) Then Set cmd = Nothing
End Sub
0
 
mdouganCommented:
I don't know if this will apply to MTS alone, but under COM+ this is how we created our ADO objects:

    ' setup connection
Set m_Con = GetObjectContext.CreateInstance("ADODB.Connection")

Set m_Cmd = GetObjectContext.CreateInstance("ADODB.Command")

Also, in every method call (we designed each method to be stateless) our component would vote on completing or aborting the transaction:

GetObjectContext.SetComplete
or
GetObjectContext.SetAbort

One other thing I'm noticing is that you are passing your array of parameters ByReference which is the VB default.  This means that when the method hits the End Function it must marshall back the parm array to the calling client.  If your parm array is big, this will be killing your performance.  There is no reason to pass back the parm array to the client unless you have some output parameters, but I didn't see anything in the code to suggest that you are setting these output parameters prior to returning to the client.  If you wanted to do this, you should redim the parm array inside of your method leaving only the output params so that the stuff you marshall back to the client is only the relevent stuff.

Also, if the result of your cmd.Execute statement is a Recordset, then sending a recordset back to the client is pretty inefficient too.  What we did was retrieve the recordset into a recordset object, and then save the recordset into a DOM Document using the  persist XML option, then, sent the DOMs XML back to the client as the return value of the method.  This was orders of magnitude faster than passing the recordset back.  Once on the client, you can load a local recordset from the XML and it's still faster than passing the recordset back!
0
 
mdouganCommented:
Sorry, also make sure that your Stored Procedures are not issuing any Transactional Statements.  Otherwise, the transaction manager is going to be in conflict with SQL Server's (or sybase) transactions
0
 
KLRDERAuthor Commented:
The param array is not large.

Additionally, we do not handle any transactions inside the procs.

0
 
KLRDERAuthor Commented:
Actually, we don't return the recordset back to the client in this instance.

The RunSQL is one level lower on the call stack than this method which is the one that is being called externally:

Public Function Execute(ByVal SQLString As Variant, _
                        ByVal ConnectionName As Variant, _
                        ParamArray params() As Variant)
On Error GoTo errorHandler
   
    mvRowsAffected = RunSQL(SQLString, GetConnectString(ConvertToString(ConnectionName)), params(0))

Exit Function
errorHandler:
RaiseError g_module, "Execute"
End Function
0
 
mdouganCommented:
Since as far as I can tell, you are not updating the Parms, I'd still recommend that you pass them by value.  Any excess marshalling is going to hurt, and it is not helping you in any way.

I'm also not too sure about the CreateObject versus the CreateInstance.  Again, my experience is with COM+, but I thought that CreateObject invoked COM and not DCOM/COM+
0
 
KLRDERAuthor Commented:
Acutally, inside of our CtxCreateObject method, we use CreateInstance to create the new object.
0
 
KLRDERAuthor Commented:
When I set the transaction isolation level to read uncommitted (boo hiss), the cmd.Execute actually executes immediately.  However, deactivation of the object still takes 20 seconds.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.