System.Data.SQLite command executions slower if a transaction is not explicitly passed
I am coping a large amount of data row by row from one table to other. If I initialize a transaction with
BeginTransaction and then create a command and go into the loop, and do transaction.Commit at the end, it happens in seconds. But if I skip those steps and just create command and execute them in loop, it takes hours. What really happens if transaction is not created? Does each command execution use a separate transaction and do a commit each time, slowing the execution?
I couldn't find any documentation or explanation for this.5 Replies
My immediate gut instict is to say this has to do with table/row locking, but that is just a guess.
Is there any official doc page for
System.Data.SQLite? I find it strange that such a widely used library doesn't have proper docThis behaviour has nothing to do with the access library, and everything to do with SQLite internals
so go check the sqlite documentation
As per SQLite doc, if no transaction is specified, an automatic is created which commits at the end of the command
https://www.sqlite.org/lang_transaction.html
but not sure the reason behind such performance diff
well you said there are a lot rows, and its row by row
so that means its creating and committing N transactions instead of 1
Might not be the full explanation for hours -> seconds, but its part of it