Performance in DB Insert/Delete Operations

[Joseph S.]

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

 

 

[Joseph S.]

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

[Ady]

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

[Joseph S.]

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

[Ady]

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

[Joseph S.]

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

[Ady]

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