Performance in DB Insert/Delete Operations

Thread is closed for posting
7 posts, 0 answers
  1. Joseph S.
    Joseph S. avatar
    11 posts
    Member since:
    Dec 2008

    Posted 14 Jun 2011 Link to this post

    Hello,

    we are using OpenAccess ORM 2010.2.714.1

    In our solution we have a Web Application and a Class Library (DAL) that we use to read the data stored in a SQL Server 2005 DB installed in another server. The OpenAccess features are enabled only for the DAL Class Library.

    The objects treated in the application are very simple: they contain few basic fields as string, integer  and date (for a total of 10), and three fields related to other objects (that contain two field each) already stored into their own table.

    We seem  to have problems regarding inserting and deleting about 15000 objects in a sql server table: the application takes more than 300 seconds to commit the insert operation (almost the same for deleting).

    The VB code to perform the insert operation is like that:

    Protected Sub AddNewEntityObjectList(Of T)(ByRef plstEntityObjects As IList(Of T))
            Dim lblnStatus As Boolean = True
            Try
                ClearLogStatus()
                mobjObjectScope.Transaction.Begin()
                ObjectScope.Add(plstEntityObjects)
     
            Catch ex As Exception
                lblnStatus = False
                  
            Finally
                If (lblnStatus) Then
                    mobjObjectScope.Transaction.Commit()
                Else
                    mobjObjectScope.Transaction.Rollback()
                End If
            End Try
        End Sub


    The VB code to perform the delete operation is like that:

    Public Sub DeleteBySessionId(Of T)(ByRef plstEntityObjects As IList(Of T))
            Dim lblnStatus As Boolean = True
            Try
                ClearLogStatus()
                mobjObjectScope.Transaction.Begin()
                ObjectScope.Remove(plstEntityObjects)
     
            Catch ex As Exception
                lblnStatus = False
                
            Finally
                If (lblnStatus) Then
                    mobjObjectScope.Transaction.Commit()
                Else
                    mobjObjectScope.Transaction.Rollback()
                End If
            End Try
    End Sub


    The commit instruction takes more time for both operations.

    Perhaps, is there any mistakes  that caused this slowdown in the set of the instruction used to perform the insert and delete operations?

    Do you have any suggestions to improve the performance?


    Thanks in advance for your reply.

           Joseph

     

     


  2. Joseph S.
    Joseph S. avatar
    11 posts
    Member since:
    Dec 2008

    Posted 15 Jun 2011 Link to this post


    We have activated the trace and we have verified that for the INSERT operations are executed 2 SQL INSERT for every Object (because we have a master/detail SQL table definition):

     
    20110615 19:42:10 PM driver.pool.alloc          1 active=1/10 idle=2/10 con=62476613
    20110615 19:42:10 PM driver.stat.exec           62476613 INSERT INTO [PRC_ENTITY_OBJECT] ([ObjectId], [voa_class], [copyId], [Action], [SessionId], [TTGTimeStamp], [Version]) VALUES (?, ?, ?, ?, ?, ?, ?)
    ..............
    ..............
    20110615 19:42:19 PM driver.stat.exec           62476613 INSERT INTO [PRC_PRICE] ([ObjectId], [Active], [Selected], [DepBeginDate], [DepEndDate], [OfficeCode], [PriceSystemCode], [COD_SHIP], [Code], [Description], [InvoiceCode], [SupCategoryCode]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

    So when we insert 1000 rows, 2000 statements are executed.
    Every "driver.stat.exec" row in the log is a single call to the SQL DB?
    In this case is possible to optimize the access to the SQL DB and to execute all the 2000 insert in only one DB call?
    Could be great if all these insert could be executed with a single call to a stored procedure: does is it possible?

    Also for the DELETE operation we have verified that the DELETE statements are executed using the SQL "IN" operator:

    20110615 19:45:21 PM driver.pool.alloc          1 active=1/10 idle=2/10 con=8067444
    20110615 19:45:21 PM driver.stat.execUpdate     8067444 delete from [PRC_PRICE_CATEGORY] where [PriceId] IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    .......
    20110615 19:45:21 PM driver.stat.execUpdate     8067444 delete from [PRC_PRICE_CRUISE] where [PriceId] IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    .......
    20110615 19:45:22 PM driver.stat.execUpdate     8067444 DELETE FROM [PRC_ENTITY_OBJECT] WHERE [ObjectId] IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    .......
     
    Is possibile to change the "IN" operator and to use instead the equal ("=") operator, maybe it could be perform better.
    Is possibile to execute all the delete statements in only one DB call?
     
    Thanks again.
         Joseph
  3. Ady
    Admin
    Ady avatar
    589 posts

    Posted 20 Jun 2011 Link to this post

    Hi Joseph S.,

     Currently it is not possible to specify a single stored procedure that inserts all the rows. Bulk inserts are currently not supported.
    Regarding the 'DELETE..IN' statement - using 'IN' lets us delete multiple rows in a single statement. All rows of a certain type are deleted in a single statement.

    Do get  back in case you need further clarification.

    Greetings,
    Ady
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  4. Joseph S.
    Joseph S. avatar
    11 posts
    Member since:
    Dec 2008

    Posted 21 Jun 2011 Link to this post

    Hi Ady,

    thank you for your answer.

    If the Bulk inserts are currently not supported, what's the best solution to insert in the DB a large number of records in a fast way using Open Access?

    Thanks.
         Joseph
  5. Ady
    Admin
    Ady avatar
    589 posts

    Posted 27 Jun 2011 Link to this post

    Hello Joseph S.,

     To insert large number of records in a very fast way you would need to use the ADO.NET api directly. One possible faster approach would be to use the 'SqlBulkCopy' class in the System.Data assembly to efficiently insert large amounts of data to a table. Is the large amount of data generated by your application or does it come from an external source (example: from a file)? The 'WriteToServer' method accepts an IDataReader. You could implement the IDataReader in your application that would feed the 'WriteToServer' method.  In order for OpenAccess to know the changes you would need to query for these instances via the context.

    Do get back in case you need further assistance.

    All the best,
    Ady
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  6. Joseph S.
    Joseph S. avatar
    11 posts
    Member since:
    Dec 2008

    Posted 27 Jun 2011 Link to this post


    Hi Ady,

    thanks, but the problem is that every object in our Object Model managed by Open Access is defined with an unique key "ObjectId". This is our base class:

    <Telerik.OpenAccess.Persistent(IdentityField:="objectId", VersionField:="objectVersion")> <Serializable()> Public MustInherit Class clsDmlEntityObject
     
    #Region "Variable Declarations"
        Private objectId As Guid
        Private objectVersion As Integer
        Private mstrSessionId As String
        Private mstrTTGTimeStamp As String
        Private mstrAction As String
     
        <Telerik.OpenAccess.Depend()> Private mobjCopy As clsDmlEntityObject
    #End Region
     
    #Region "Property Definitions"
     
        Public Property Version() As Integer
            Get
                Return objectVersion
            End Get
            Set(ByVal value As Integer)
                objectVersion = value
            End Set
        End Property
        Public Property Id() As Guid
            Get
                Return objectId
            End Get
            Set(ByVal value As Guid)
                objectId = value
            End Set
        End Property
     
        <Telerik.OpenAccess.FieldAlias("mstrSessionId")> _
        Public Property SessionId() As String
            Get
                Return mstrSessionId
            End Get
            Set(ByVal Value As String)
                mstrSessionId = Value
            End Set
        End Property
     
        Public Property TTGTimeStamp() As String
            Get
                Return mstrTTGTimeStamp
            End Get
            Set(ByVal value As String)
                mstrTTGTimeStamp = value
            End Set
        End Property
     
        <Telerik.OpenAccess.FieldAlias("mstrAction")> _
        Public Property Action() As String
            Get
                Return mstrAction
            End Get
            Set(ByVal Value As String)
                mstrAction = Value
            End Set
        End Property
     
        Public Property Copy() As clsDmlEntityObject
            Get
                Return mobjCopy
            End Get
            Set(ByVal value As clsDmlEntityObject)
                mobjCopy = value
            End Set
        End Property
     
    #End Region
     
    End Class

    When we insert new records in the DB using Open Access the key ObjectId is automatically valued by Open Access.
    But if we insert the new record using ADO.NET or a Stored Procedure, how we can set the new ObjectId values?

    Thanks Again.
        Joseph



  7. Ady
    Admin
    Ady avatar
    589 posts

    Posted 30 Jun 2011 Link to this post

    Hi Joseph S.,

     The ObjectId is a Guid field. OpenAccess uses the 'Guid.NewGuid' to generate a new value. You can generate a unique value using this method.

    Best wishes,
    Ady
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
Back to Top