MaxBatchSize for Insert?

MaxBatchSize for Insert?

Postby Zero-G. » Fri 19 Jun 2015 13:17

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
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Re: MaxBatchSize for Insert?

Postby MariiaI » Mon 22 Jun 2015 12:28

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/?BatchUpdates.html
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)