We have an application that attempts to save a collection of objects to a SQL 2005 database using a 'save' stored procedure via the SQLCommand class. We have a performance issue in that some calls of the stored procedure are slow (15-20ms) whereas other calls execute very quickly (<1ms). This is a problem as we will typically save a collection of 100,000 objects.
Using SQL Server Profiler, I can see that the actual stored procedure runs very quickly every time (always <1ms), however on odd occasions, the difference between Debug.Print points 2 and 3 can be 15-20ms. Does anyone have any pointers as to why this may happen, are we doing something wrong?
We would prefer to avoid a method of bulk saving the objects (by serializing to XML or similar).
Public Function UpdateDataBase()
Using cn As New SqlConnection("Data Source=localhost;Initial Catalog=TestDBEmpty;User ID=******;Password=******;Network Library=dbmssocn")
For Each item As OBJ_Bookings In Me
'Debug.Print("Start - " & Now.ToString & " - " & Now.Millisecond) 'Debug.Print Point 1
'Debug.Print("End - " & Now.ToString & " - " & Now.Millisecond) 'Debug.Print Point 4
Catch ex As Exception
Public Sub SaveToDatabase(ByRef ItemToUpdate as OBJ_Bookings, ByRef cn As SqlConnection)
Using cmd As SqlCommand = cn.CreateCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "isCRUD_BookingsSave"
Debug.Print("Procedure Start - " & Now.ToString & " - " & Now.Millisecond) 'Debug.Print Point 2
Debug.Print("Procedure End - " & Now.ToString & " - " & Now.Millisecond) 'Debug.Print Point 3