Ensure batch inserts are done in a transaction on SQLite #1177
Comments
sgrif
added a commit
that referenced
this issue
Sep 21, 2017
While it's safe for us to do the inserts one query at a time, due to the lack of round-trip time on SQLite, there's still a significant performance difference if done outside of a transaction. If we're not in a transaction, SQLite will perform table locking, and file IO for each query individually. This also means that batch insert on SQLite will have the same semantics on failure as the other backends. While I previously thought that we did not want a savepoint here if we were already in a transaction, it's actually important that we create one in order to match the semantics of other backends on failure. Fixes #1177.
sgrif
added a commit
that referenced
this issue
Sep 21, 2017
While it's safe for us to do the inserts one query at a time, due to the lack of round-trip time on SQLite, there's still a significant performance difference if done outside of a transaction. If we're not in a transaction, SQLite will perform table locking, and file IO for each query individually. This also means that batch insert on SQLite will have the same semantics on failure as the other backends. While I previously thought that we did not want a savepoint here if we were already in a transaction, it's actually important that we create one in order to match the semantics of other backends on failure. Fixes #1177.
sgrif
added a commit
that referenced
this issue
Sep 21, 2017
While it's safe for us to do the inserts one query at a time, due to the lack of round-trip time on SQLite, there's still a significant performance difference if done outside of a transaction. If we're not in a transaction, SQLite will perform table locking, and file IO for each query individually. This also means that batch insert on SQLite will have the same semantics on failure as the other backends. While I previously thought that we did not want a savepoint here if we were already in a transaction, it's actually important that we create one in order to match the semantics of other backends on failure. Fixes #1177.
sgrif
added a commit
that referenced
this issue
Sep 21, 2017
While it's safe for us to do the inserts one query at a time, due to the lack of round-trip time on SQLite, there's still a significant performance difference if done outside of a transaction. If we're not in a transaction, SQLite will perform table locking, and file IO for each query individually. This also means that batch insert on SQLite will have the same semantics on failure as the other backends. While I previously thought that we did not want a savepoint here if we were already in a transaction, it's actually important that we create one in order to match the semantics of other backends on failure. Fixes #1177.
sgrif
added a commit
that referenced
this issue
Sep 21, 2017
While it's safe for us to do the inserts one query at a time, due to the lack of round-trip time on SQLite, there's still a significant performance difference if done outside of a transaction. If we're not in a transaction, SQLite will perform table locking, and file IO for each query individually. This also means that batch insert on SQLite will have the same semantics on failure as the other backends. While I previously thought that we did not want a savepoint here if we were already in a transaction, it's actually important that we create one in order to match the semantics of other backends on failure. Fixes #1177.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
There's a significant performance difference between doing a batch insert in a transaction vs out of a transaction on SQLite, since if we're not in a transaction it'll write to disk between every query. The implementation will need to be a little bit more complex than just
conn.transaction(|| what we have now), since savepoints have a non-zero cost so we should only start a transaction if we aren't already inside of one.The text was updated successfully, but these errors were encountered: