SQLite Forum

Feature request: add an optional transaction size in mode insert
Login

Feature request: add an optional transaction size in mode insert

(1.2) By Ferran Jorba (fjorba) on 2020-05-13 16:14:27 edited from 1.1 [link]

First of all, thank you very much for SQLite, it is wonderful!

An operation that I do often is batch import records into SQLite, sometimes exported from other SQLite db. For that, I use this idiom:

$ sqlite3 -cmd '.mode insert mytable' old.db 'select * from mytable;' | sqlite3 new.db

If mytable is very large, this operation may take a long time, and I have to do more cumbersome operation to provide transactions.  I think it would be very useful to add an optional numeric parameter that, if provided, would create transactions, for example:

$ sqlite3 -cmd '.mode insert mytable 10000' old.db 'select * from mytable;' | sqlite3 new.db

Would create a transaction every 10000 records, and the operation would be much faster with this minimal addition.

Thanks again,

Ferran Jorba

Edit: add the forgotten select statement in the examples

(2) By Larry Brasfield (LarryBrasfield) on 2020-05-13 14:46:03 in reply to 1.0 [link]

What you request can be readily achieved with ordinary scripting methods.

For example, this Perl script:

```
# /usr/bin/perl

my $nClump = shift;

if (!defined($nClump)) { $nClump = 100; }

print "BEGIN TRANSACTION;\n";

my $nLines = 0;

while ($_ = <>) {
  print $_;
  if (++$nLines % $nClump == 0) {
    print "COMMIT TRANSACTION;\n";
    print "BEGIN TRANSACTION;\n";
  }
}

print "COMMIT TRANSACTION;\n";
```

in a file marked executable, "transact_clump", can be used thusly:

```
echo .quit | sqlite3 -batch -cmd ".mode insert People" -cmd "select * from People;" furd.sdb | transact_clump 5
```

to break a long sequence of inserts into clumps of inserts within transactions.

(3) By Ferran Jorba (fjorba) on 2020-05-13 15:11:23 in reply to 2

Of course it can be done; that's what I have been doing myself all those years (mostly in shell and awk).

What I'm asking is that it would be very useful as a built in, and I'm suggesting a syntax.

(4) By Simon Slavin (slavin) on 2020-05-13 20:52:13 in reply to 1.2 [link]

<code>INSERT INTO table2 SELECT * FROM table1 WHERE TRUE</code>

Does all the inserting in one big transaction.

<https://sqlite.org/lang_insert.html>

(5) By Ferran Jorba (fjorba) on 2020-05-14 04:37:11 in reply to 4 [link]

Thanks for this hint, that I didn't know.  However, my use case is more about transferring data between databases.

Anyway, I may want it in smaller chunks. If I have a table with millions of entries, I may prefer to do break it.

My suggestion is a very simple addition that facilitates this use case always, without having to use external scripting tools, that not everybody is able to use or not all environments have.  I can, but I'm thinking from usability point of view at large.

(6) By Stephan Beal (stephan) on 2020-05-14 05:24:00 in reply to 5 [link]

> ... without having to use external scripting tools, that not everybody is able to use or not all environments have.  I can, but I'm thinking from usability point of view at large.

Seeing as you're the only person who's suggested it so far, the larger audience for it might only be you ;).

(7.1) By Ferran Jorba (fjorba) on 2020-05-14 07:53:55 edited from 7.0 in reply to 6 [link]

A good idea can come at unsuspected times and places ;-)

If it is good, it may have users that may have not found a way to do it before.  Questions about performance when inserting large number of records in SQLite databases pop up here and in other forums, and creating transactions is the generalized answer.  So, I believe that facilitating the creation of transactions can help real users.

(8) By anonymous on 2020-05-14 06:48:29 in reply to 6 [link]

Well, add one at least... I've done this myself in surrounding code.  I just didn't expect SQLite to cover the overhead for me so didn't ask the question.

- Donald Shepherd

(9) By Larry Brasfield (LarryBrasfield) on 2020-05-14 12:59:31 in reply to 7.1 [link]

There are plenty of useful tools that use SQLite as a DB engine and are feature-rich for helping with the sort of task that motivates your suggestion. However, the SQLite shell itself adheres closer to what is known as "[The Unix Philosophy](https://en.wikipedia.org/wiki/Unix_philosophy)." As a rule [a], it does what can only be done there and leaves composition into more complex tasks to users. As the linked article says, "The Unix philosophy favors composability as opposed to monolithic design."

[a. There are slight departures from this rule, but they involve boundary cases and testing of SQLite itself. ]

Shell scripting (or Python/Perl/TCL/Whatever) scripting with the sqlite3 shell (or library) used as the database access component is very quick and easy. When I find myself typing too much at the shell, my reaction is to capture that logic in some kind of script, usually one (or more) having ongoing utility but sometimes just a quick-and-dirty one-off, in the temp directory.

Given the dedicated focus of the small SQLite development team, I would be surprised to see their shell become bloated with a ever-growing set of enhancements that can be readily achieved via composition, by others.

In regard to "facilitating the creation of transactions", the shell does that very well already. What you seek is a way of specifying when and how that is to be done, with minimal typing on your part, at the cost of making the shell harder to understand as a tool.