Page 1 of 1

MaxBatchSize for Insert?

Posted: Fri 19 Jun 2015 13:17
by Zero-G.
Hey

I have a question related to LinQ to MsSql.
I am creating about 20 000 records on the fly and use the following code to insert the data:

Code: Select all

myDataContext.Table.InsertAllOnSubmit(locData)
myDataContext.SubmitChanges()
The SubmitChanges code Needs about 3 minutes to execute.

I took a look onto the MaxBatchSize Property. - This is set to 20
I tried now:

Code: Select all

Using myDataContext As New DataContext With {.MaxBatchSize = 1000}
myDataContext.Table.InsertAllOnSubmit(locData)
myDataContext.SubmitChanges()
End Using
But without any Performance Upgrades.
Could you give me please some hints, how I can upscale the Batch Insertion!?

THX

Re: MaxBatchSize for Insert?

Posted: Mon 22 Jun 2015 12:28
by MariiaI
Most likely, it is related to the Batch Updates limitations. I.e., commands for the batch are chosen in the way that execution of one command should not affect parameters of other command. If a command does not fit this condition, it will be executed separately. A command will be included to a batch if:
  • - This command is not verified for optimistic concurrency (all updated object properties have UpdateCheck=UpdateCheck.Never parameter of the Column attribute);
    - The command does not return OUT parameters using the RETURNING clause, second query, or by LastInsertId (all updated object properties have AutoSync=AutoSync.Never parameter of the column attribute);
Please refer to: https://www.devart.com/linqconnect/docs ... dates.html