Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sqlite3: Disabling primary key index while inserting?

I have an Sqlite3 database with a table and a primary key consisting of two integers, and I'm trying to insert lots of data into it (ie. around 1GB or so)

The issue I'm having is that creating primary key also implicitly creates an index, which in my case bogs down inserts to a crawl after a few commits (and that would be because the database file is on NFS.. sigh).

So, I'd like to somehow temporary disable that index. My best plan so far involved dropping the primary key's automatic index, however it seems that SQLite doesn't like it and throws an error if I attempt to do it.

My second best plan would involve the application making transparent copies of the database on the network drive, making modifications and then merging it back. Note that as opposed to most SQlite/NFS questions, I don't need access concurrency.

What would be a correct way to do something like that?

UPDATE:

I forgot to specify the flags I'm already using:

PRAGMA synchronous = OFF
PRAGMA journal_mode = OFF
PRAGMA locking_mode = EXCLUSIVE
PRAGMA temp_store = MEMORY

UPDATE 2: I'm in fact inserting items in batches, however every next batch is slower to commit than previous one (I'm assuming this has to do with the size of index). I tried doing batches of between 10k and 50k tuples, each one being two integers and a float.

like image 433
Dimitri Tcaciuc Avatar asked Apr 25 '09 08:04

Dimitri Tcaciuc


People also ask

Does SQLite automatically create an index for primary key columns?

When an integer column is marked as a primary key in an SQLite table, should an index be explicitly created for it as well? SQLite does not appear to automatically create an index for a primary key column, but perhaps it indexes it anyway, given its purpose? (I will be searching on that column all the time).

What is PRIMARY KEY constraint in SQLite?

Summary: in this tutorial, you will learn how to use SQLite PRIMARY KEY constraint to define a primary key for a table. A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key.

Does SQLite allow null values in primary key?

However, to make the current version of SQLite compatible with the earlier version, SQLite allows the primary key column to contain NULL values. When you create a table without specifying the WITHOUT ROWID option, SQLite adds an implicit column called rowid that stores 64-bit signed integer.

What is a primary key in SQL?

A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key. First, if the primary key has only one column, you use the PRIMARY KEY column constraint to define the primary key as follows: CREATE TABLE table_name ( column_1 INTEGER NOT NULL PRIMARY KEY, ... );


2 Answers

  1. You can't remove embedded index since it's the only address of row.
  2. Merge your 2 integer keys in single long key = (key1<<32) + key2; and make this as a INTEGER PRIMARY KEY in youd schema (in that case you will have only 1 index)
  3. Set page size for new DB at least 4096
  4. Remove ANY additional index except primary
  5. Fill in data in the SORTED order so that primary key is growing.
  6. Reuse commands, don't create each time them from string
  7. Set page cache size to as much memory as you have left (remember that cache size is in number of pages, but not number of bytes)
  8. Commit every 50000 items.
  9. If you have additional indexes - create them only AFTER ALL data is in table

If you'll be able to merge key (I think you're using 32bit, while sqlite using 64bit, so it's possible) and fill data in sorted order I bet you will fill in your first Gb with the same performance as second and both will be fast enough.

like image 154
Mash Avatar answered Oct 11 '22 10:10

Mash


Are you doing the INSERT of each new as an individual Transaction?

If you use BEGIN TRANSACTION and INSERT rows in batches then I think the index will only get rebuilt at the end of each Transaction.

like image 21
Dave Webb Avatar answered Oct 11 '22 10:10

Dave Webb