Hello everybody,
I'm currently experimenting with SQLite (3.7.8) and am a complete newbie in the
field of SQL databases.
Running performance comparisons between a C++ std::map< int, std::string > and
an in-memory SQLite database, I found SQLite to be astonishingly slow when
selecting single rows by their ROWID. More than that, the performance
degradation when increasing the total number of rows was far from being O(log
n) - a jump from 1000 rows to 10000 rows resulted in a factor of 100 in
execution time.
I strongly believe that my setup is somehow wrong, I just can't figure out how.
Below is my test, I left out checking the return values to make it more
readable:
static const int TOTAL_ROW_COUNT = 10000;
sqlite3* db = 0;
sqlite3_open(":memory:", &db);
static const uint BUFSIZE = 2048;
char buf[BUFSIZE];
sqlite3_exec(db, "CREATE TABLE mytable (MyKey INTEGER PRIMARY KEY)", 0, 0, 0);
for (int key=0; key<TOTAL_ROW_COUNT; key++)
{
sprintf(buf, "INSERT INTO mytable (MyKey) VALUES (%d)", key);
sqlite3_exec(db, buf, 0, 0, 0);
}
sqlite3_stmt* stmt = 0;
sqlite3_prepare_v2(db, "SELECT * FROM mytable WHERE MyKey IS ?", -1, &stmt, 0);
for (int key=0; key<TOTAL_ROW_COUNT; key++)
{
sqlite3_bind_int(stmt, 1, key);
/* The call to sqlite3_step() takes > 1ms on a 2.4GHz Core2Duo and a table
with 10000 rows */
sqlite3_step(stmt);
sqlite3_reset(stmt);
}
Enclosing the last loop in "BEGIN TRANSACTION" and "COMMIT TRANSACTION"
statements did not help, neither did fiddling with PRAGMAs (journal_mode = OFF,
synchronous = OFF, temp_store = MEMORY).
I ran this test under Windows XP/VS2008 (Release build) and OS X Lion/XCode
(Debug build, I believe.. I'm new to this IDE), the results were nearly
identical (slight differences because of the hardware).
I'm completely puzzled by this. It is actually faster to dump the whole table
into a std::map and then access it one-by-one:
- execute "SELECT * FROM mytable"
- sqlite3_step() through the whole result set
- for each row: std::map.insert(sqlite3_column_int(stmt, 0), "sometext")
(pseudo-code)
- for (int i=0; i<TOTAL_ROW_COUNT; i++)
- std::map.find(i) (pseudo-code)
This takes less than a second (under 100ms if I remember correctly) with
TOTAL_ROW_COUNT = 10000. Selecting each row by its ID takes around 11s.
The emails I found in the archive regarding bad performance gave me no
indication that my numbers are to be expected.
To quote the subject: What am I doing wrong? Am I not seeing the forest for the
trees?
Thanks in advance,
Tobias
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
