Sqlite Insert Taking Long Time

SQLite Insert very slow?

Wrap BEGIN \ END statements around your bulk inserts. Sqlite is optimized for transactions.

dbcon = new SQLiteConnection(connectionString);
dbcon.Open();

SQLiteCommand sqlComm;
sqlComm = new SQLiteCommand("begin", dbcon);
sqlComm.ExecuteNonQuery(); 
//---INSIDE LOOP

 sqlComm = new SQLiteCommand(sqlQuery, dbcon);

 nRowUpdatedCount = sqlComm.ExecuteNonQuery(); 

//---END LOOP
sqlComm = new SQLiteCommand("end", dbcon);
sqlComm.ExecuteNonQuery(); 
dbcon.close();

sqlite insert taking long time

Insert speed in sqlite mainly depends on:

• amount of inserts per transaction (insert without transaction is an atomic insert, each in it's own transaction, which means it's very slow)
• database mode - WAL or normal journal
• number of indexes on the inserted fields
• disk speed

If speed is a problem, then you should consult google for each of those factors I wrote and act appropriately.

SQLite insert speed slows as number of records increases due to an index

If your requirement is to find a particular z_id and the x_ids and y_ids linked to it (as distinct from quickly selecting a range of z_ids) you could look into a non-indexed hash-table nested-relational db that would allow you to instantly find your way to a particular z_id in order to get its y_ids and x_ids -- without the indexing overhead and the concomitant degraded performance during inserts as the index grows. In order to avoid clumping (aka bucket collisions), choose a key hashing algorithm that puts greatest weight on the digits of z_id with greatest variation (right-weighted).

P.S. A database that uses a b-tree may at first appear faster than a db that uses linear hashing, say, but the insert performance will remain level with the linear hash as performance on the b-tree begins to degrade.

P.P.S. To answer @kawing-chiu's question: the core feature relevant here is that such a database relies on so-called "sparse" tables in which the physical location of a record is determined by a hashing algorithm which takes the record key as input. This approach permits a seek directly to the record's location in the table without the intermediary of an index. As there is no need to traverse indexes or to re-balance indexes, insert-times remain constant as the table becomes more densely populated. With a b-tree, by contrast, insert times degrade as the index tree grows. OLTP applications with large numbers of concurrent inserts can benefit from such a sparse-table approach. The records are scattered throughout the table. The downside of records being scattered across the "tundra" of the sparse table is that gathering large sets of records which have a value in common, such as a postal code, can be slower. The hashed sparse-table approach is optimized to insert and retrieve individual records, and to retrieve networks of related records, not large sets of records that have some field value in common.

A nested relational database is one that permits tuples within a column of a row.

Improve INSERT-per-second performance of SQLite

Several tips:

1. Put inserts/updates in a transaction.
2. For older versions of SQLite - Consider a less paranoid journal mode (pragma journal_mode). There is NORMAL, and then there is OFF, which can significantly increase insert speed if you're not too worried about the database possibly getting corrupted if the OS crashes. If your application crashes the data should be fine. Note that in newer versions, the OFF/MEMORY settings are not safe for application level crashes.
3. Playing with page sizes makes a difference as well (PRAGMA page_size). Having larger page sizes can make reads and writes go a bit faster as larger pages are held in memory. Note that more memory will be used for your database.
4. If you have indices, consider calling CREATE INDEX after doing all your inserts. This is significantly faster than creating the index and then doing your inserts.
5. You have to be quite careful if you have concurrent access to SQLite, as the whole database is locked when writes are done, and although multiple readers are possible, writes will be locked out. This has been improved somewhat with the addition of a WAL in newer SQLite versions.
6. Take advantage of saving space...smaller databases go faster. For instance, if you have key value pairs, try making the key an INTEGER PRIMARY KEY if possible, which will replace the implied unique row number column in the table.
7. If you are using multiple threads, you can try using the shared page cache, which will allow loaded pages to be shared between threads, which can avoid expensive I/O calls.
8. Don't use !feof(file)!

I've also asked similar questions here and here.

SQLite DB Insert Very Slow

This is what I would address, in order. It may or may not fix the problem, but it won't hurt to see (and it might just do some magic):

1. Ensure the Database is not being contended with updates (from another thread, process, or even timer!). Writers will acquire locks and unclosed/over-long-running transactions can interact in bad ways. (For updates that take "30 seconds to 2 minutes" I would imagine there is an issue obtaining locks. Also ensure the media the DB is on is sufficient, e.g. local drive.)

2. The transaction is not being used (??). Move the transaction inside the timer callback, attach it to the appropriate SQLCommands, and dispose it before the callback ends. (Use using).

3. Not all SQLCommand's are being disposed correctly. Dispose each and every one. (The use of using simplifies this. Do not let it bleed past the callback.)

4. Placeholders are not being used. Not only is this simpler and easier to use, but it is also ever so slightly more friendly to SQLite and the adapter.

(Example only; there may be errors in the following code.)

// It's okay to keep long-running SQLite connections.
// In my applications I have a single application-wide connection.
// The more important thing is watching thread-access and transactions.
// In any case, we can keep this here.
SQLiteConnection sqlconnection = new SQLiteConnection(con);
sqlconnection.Open();

// In timer event - remember this is on the /UI/ thread.
// DO NOT ALLOW CROSS-THREAD ACCESS TO THE SAME SQLite CONNECTION.
// (You have been warned.)
URL u = firefox.URLs[count2];
string newtitle = u.title;
form.label1.Text = count2 + "/" + pBar.Maximum;

try {
   // This transaction is ONLY kept about for this timer callback.
   // Great care must be taken with long-running transactions in SQLite.
   // SQLite does not have good support for (long running) concurrent-writers
   // because it must obtain exclusive file locks.
   // There is no Table/Row locks!
   sqlconnection.BeginTransaction();
   // using ensures cmd will be Disposed as appropriate.
   using (var cmd = sqlconnection.CreateCommand()) {
     // Using placeholders is cleaner. It shouldn't be an issue to
     // re-create the SQLCommand because it can be cached in the adapter/driver
     // (although I could be wrong on this, anyway, it's not "this issue" here).
     cmd.CommandText = "insert or ignore into " + table
       + " (id, url, title, visit_count, typed_count, last_visit_time, hidden)"
       + " values (@dbID, @url, 'etc, add other parameters')";
     // Add each parameter; easy-peasy
     cmd.Parameters.Add("@dbID", dbID);
     cmd.Parameter.Add("@url", u.url);
     // .. add other parameters
     cmd.ExecuteNonQuery();
   }
   // Do same for other command (runs in the same TX)
   // Then commit TX
   sqlconnection.Commit();
} catch (Exception ex) {
   // Or fail TX and propagate exception ..
   sqlconnection.Rollback();
   throw;
}

if (pBar.Maximum == count2)
{
    pBar.Value = 0;
    timer.Stop();
    // All the other SQLite resources are already
    // cleaned up!
    sqlconnection.Dispose();
    sqlconnection.Close();
}

sqlite3 multiple inserts really slow

Your answer may lie here:

https://www.sqlite.org/threadsafe.html

Because it says there that:

The default mode is serialized.

which might explain your observations.

According to that document, you can either configure this at compile time (which I would most definitely not myself do) or via:

sqlite3_config (SQLITE_CONFIG_MULTITHREAD);

Just how stratospherically it then performs I wouldn't know.

Android SQLite database: slow insertion

You should do batch inserts.

Pseudocode:

db.beginTransaction();
for (entry : listOfEntries) {
    db.insert(entry);
}
db.setTransactionSuccessful();
db.endTransaction();

That increased the speed of inserts in my apps extremely.

Update:

@Yuku provided a very interesting blog post: Android using inserthelper for faster insertions into sqlite database

---

Related Topics