Solved

MTS/database performance issue

Posted on 2002-06-25
26
323 Views
Last Modified: 2012-05-04
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?
0
Comment
Question by:KLRDER
  • 14
  • 7
  • 3
  • +2
26 Comments
 
LVL 5

Expert Comment

by:rpai
Comment Utility
Did you check if your SQL statement takes 20sec to execute even outside MTS?
0
 

Author Comment

by:KLRDER
Comment Utility
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
 
LVL 5

Expert Comment

by:rpai
Comment Utility
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
 
LVL 5

Expert Comment

by:rpai
Comment Utility
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
 

Author Comment

by:KLRDER
Comment Utility
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
 

Author Comment

by:KLRDER
Comment Utility
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
 

Author Comment

by:KLRDER
Comment Utility
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
 
LVL 5

Expert Comment

by:rpai
Comment Utility
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
 

Author Comment

by:KLRDER
Comment Utility
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
 

Author Comment

by:KLRDER
Comment Utility
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
 
LVL 5

Expert Comment

by:rpai
Comment Utility
Please do not use the REFRESH button on your browser. Use the Reload Question instead.
0
 

Author Comment

by:KLRDER
Comment Utility
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
 
LVL 5

Expert Comment

by:rpai
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:KLRDER
Comment Utility
Changing it to "NotAnMTSObject" makes it behave as it should.
0
 
LVL 5

Expert Comment

by:rpai
Comment Utility
Please post your code here.
0
 

Author Comment

by:KLRDER
Comment Utility
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
 

Author Comment

by:KLRDER
Comment Utility
When I implement the ObjectContext interface in the class, I find that it is deactivating at the point it exits the method call.
0
 
LVL 26

Expert Comment

by:EDDYKT
Comment Utility
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
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
 

Author Comment

by:KLRDER
Comment Utility
The param array is not large.

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

0
 

Author Comment

by:KLRDER
Comment Utility
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
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
 

Author Comment

by:KLRDER
Comment Utility
Acutally, inside of our CtxCreateObject method, we use CreateInstance to create the new object.
0
 

Author Comment

by:KLRDER
Comment Utility
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
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
Comment Utility
Per Admin request, points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now