0

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 ...
alancc
  • 487
  • 2
  • 24
  • 68
  • 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 Sep 27 '18 at 09:37
  • @Andrew I would suggest using one transaction instead of two, one for each. – Ilyes Sep 27 '18 at 09:41

1 Answers1

1

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

Guy Louzon
  • 1,175
  • 9
  • 19
  • Thank you. But will that makes a very long SQL statement? Does SQLite has limit on the length of the SQL statement? Also with "INSERT INTO table (col1, col2, col3,...) VALUES (v1, v2, v3,...);", I can use sqlite3_prepare_v2 to prepare a SQL statement and the bind the argument to the statement thereafter. But with INSERT INTO table (col1, col2, col3,...) VALUES {(v1, v2, v3,...), }X 1000; is seems that the advantage of prepared statement is not exist any more. – alancc Oct 30 '18 at 09:52
  • 1st of all, the string limit is quite long, and even if 1000 is too long, 500 can be ok. 2nd of all, you have asked about performance... inserting more rows at a time improves performance. You're code comfortability is something else... – Guy Louzon Oct 30 '18 at 09:58
  • the sqlite query maximum size https://stackoverflow.com/questions/24510707/is-there-any-limit-on-sqlite-query-size – Guy Louzon Oct 30 '18 at 10:29