Using "BEGIN TRANSACTION" and "END TRANSACTION" to improve the performance

Question

I am reading the post Improve INSERT-per-second performance of SQLite? to improve the performance of my SQLite.

One question is: If I need to perform the following queries:

INSERT INTO
INSERT INTO
...
INSERT INTO(more than 10000 times)

SELECT ...
SELECT 

UPDATE ...

If I want to improve the performance, should I insert "BEGIN TRANSATION" and "END TRANSATION" at the very beginning and ending of all codes, like this:

BEGIN TRANSACTION

INSERT INTO
INSERT INTO
...
INSERT INTO(more than 10000 times)

SELECT ...
SELECT 

UPDATE ...
UPDATE ...

END TRANSACTION

Or should I insert BEGIN/END TRANSACTION just for the insert operation?

BEGIN TRANSACTION

INSERT INTO
INSERT INTO
...
INSERT INTO(more than 10000 times)

END TRANSACTION

SELECT ...
SELECT 

UPDATE ...
UPDATE ...

You can of course run a test similar to that post - you also could wrap the updates in it's own begin / end, as well as the inserts — Andrew
@Andrew I would suggest using one transaction instead of two, one for each. — Ilyes

Guy Louzon Guy Louzon · Accepted Answer · 2018-09-27T09:46:00

IF the INSERTs are for the same table, with the same columns inserted, using one insert will improve performance significantly, that's because each seperate insert command includes going back and forth from the DB, much more time than the actual query time.

Based on the limits of the server (other processes logged in etc) , I would set a limit to the number of inserted rows, for example a 1000 rows at a time.

INSERT INTO table (col1, col2, col3,...) VALUES
{(v1, v2, v3,...), }X 1000;

Is much faster than

{
INSERT INTO table (col1, col2, col3,...) VALUES
(v1, v2, v3,...);
}
X 1000

hope that helps

Using "BEGIN TRANSACTION" and "END TRANSACTION" to improve the performance

1 Answers