I need to add over 150.000 records in a table.
This is insanely slow and takes upto an hour to process.
After a lot of debugging (and "JustDecompiling" ;) we found the reason for this: The table has a primary key, that is defined as "IsIdentity(KeyGenerator.AutoInc)" in the Metadata Source.
This results in an extra query being fired when a new row is added to the database.
This query reads the newly created identity.
We managed to find the code that generates this extra SQL "code": GetInsertSQLForServerGeneratedIdentity() in the MsSqlDriver class.
We understand that this is a thing that we really want to do in a normal scenario.
In our scenario however, we want to add all data and collect newly added data at a later time.
While searching for a solution for this problem we found the DbContext.ContextOptions.RefreshObjectsAfterSaveChanges property.
We set this property to false and kept our fingers crossed if this was a solution for our performance problem.
Unfortunately it was not. The extra SQL SELECT still gets fired/added...
From our point of view it would be a logical step to remove the extra SQL when RefreshObjectsAfterSaveChanges is false. Only a plain insert has to be done and nothing more. We allready tried this by removing the AutoInc settings and then the process (insert 150.000 rows) takes about 25 seconds to complete.
Could you please fix this, or show me another way to insert the new records in a fast way (without using SQL bulk insert)?
Kind Regards,
Alexander