Here's the code I'm using to run the test (it includes the schema). I've been
running this code for the last few days and am a bit baffled about my recent
results, since I'm not seeing the gradual slowing anymore. This happened after
I changed to use version 3.6.15 of sqlite and even if I change back to 3.6.14
it still behaves very consistently, that is, doing the insert and delete in
constant time.
I don't think I changed anything in my original 3.6.14 install but I can't be
sure since after going into 'silly mode' I copied the 3.6.15 sqlite code over
my origianl 3.6.14 sqlite code and I now have no way of checking if I made some
changes there. At most, I may have changed some defines but if the sqlite
documentation does not suggest such a thing I doubt that I have done so on my
own accord. I have always compiled the code defining SQLITE_THREADSAFE=1.
The only thing I can think of that's different from what I did when I saw the
gradual slowing was that I am now always creating a new database when running
the test (I delete the file) and used 3.6.15 for a while. I had been hammering
away on the same database using the test code below for a while before I
noticed the increase in execution time. Could that be a factor? Could it also
be that 3.6.15 did some cleanup somewhere which could have been confusing my
previous 3.6.14, since now when I use 3.6.14 it seems quite stable?
Anyway, I will continue to try and reproduce the slowing (by not creating a new
database and re-use the table) but I'm getting a bit pessimistic on being able
to do so, which is a good thing if this was a fluke, but also a bad thing since
it leaves behind a nagging feeling that something might still be wrong. Maybe
it should be a best practice to vacuum the database regularly to keep it in
shape?
// Code begins
#include <fstream>
#include <iostream>
#include <windows.h>
#include <sqlite3.h>
int
callback(void* arg, int argc, char** argv, char** column_names)
{
/*
for (int i = 0; i < argc; i++)
{
std::cout << argv[i] << " ";
}
std::cout << std::endl;
*/
return 0;
}
int
main()
{
sqlite3* db;
int res;
char* error_msg = 0;
/*
std::cout << "Opening time file...";
std::fstream file("time.txt", std::ios::app);
if (!file.is_open())
{
std::cerr << "failed\n";
exit(1);
}
std::cout << "done\n";
*/
std::cout << "Opening database...";
res = sqlite3_open("my1.db", &db);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << sqlite3_errmsg(db) << std::endl;
sqlite3_close(db);
exit(1);
}
std::cout << "done\n";
std::string sql;
/**/
std::cout << "Dropping table t1...";
sql = "drop table t1";
res = sqlite3_exec(db, sql.c_str(), callback, 0, &error_msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
//exit(1);
}
else
{
std::cout << "done" << std::endl;
}
std::cout << "Creating table t1...";
sql = "create table t1(a integer, b integer, c varchar(100))";
res = sqlite3_exec(db, sql.c_str(), callback, 0, &error_msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
std::cout << "done" << std::endl;
std::cout << "Creating index i1 on t1...";
sql = "create index i1 on t1(a)";
res = sqlite3_exec(db, sql.c_str(), callback, 0, &error_msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
std::cout << "done" << std::endl;
/**/
while (true)
{
//std::cout << "Deleting from table t1...";
sql = "delete from t1";
res = sqlite3_exec(db, sql.c_str(), callback, 0, &error_msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
//std::cout << "done" << std::endl;
SYSTEMTIME start;
SYSTEMTIME end;
double start_seconds;
double end_seconds;
int record_count = 1000;
int batches = 100;
//std::cout << "Inserting and selecting " << record_count * batches <<
" records in batches of " << record_count << std::endl;
GetSystemTime(&start);
char sql_c[1024];
for (int b = 0; b < batches; b++)
{
int row;
//std::cout << "Beginning transaction...";
sql = "begin transaction";
res = sqlite3_exec(db, sql.c_str(), callback, 0, &error_msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
//std::cout << "done" << std::endl;
for (int i = 0; i < record_count; i++)
{
row = b * record_count + i;
sprintf(sql_c, "insert into t1
values(%i,%i,'a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.a.')",
row, i);
//std::cout << sql_c << std::endl;
res = sqlite3_exec(db, sql_c, callback, 0, &error_msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
}
//std::cout << "Ending transaction...";
sql = "commit";
res = sqlite3_exec(db, sql.c_str(), callback, 0, &error_msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
//std::cout << "done" << std::endl;
/*
sprintf(sql_c, "select * from t1"); // where a >= %i and a <= %i",
row - record_count + 1, row);
//std::cout << sql_c << std::endl;
res = sqlite3_exec(db, sql_c, callback, 0, &error_msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
std::cout << "done" << std::endl;
*/
}
GetSystemTime(&end);
start_seconds = start.wHour * 60*60 + start.wMinute * 60 +
start.wSecond + (double)start.wMilliseconds / 1000.0;
end_seconds = end.wHour * 60*60 + end.wMinute * 60 + end.wSecond +
(double)end.wMilliseconds / 1000.0;
double total_time = end_seconds - start_seconds;
//std::cout << "Insert took " << total_time << " seconds" << std::endl;
std::cout << total_time << std::endl;
//file << total_time << std::endl;
}
return 0;
}
// Code ends
JP
-----Original Message-----
Jens,
Can you post the schema for your table and the index (i.e. the actual
schema you are using for the test), and also the code that shows how you
are assigning values to the columns in your table when you do the inserts?
I suspect that you may be using a autoincrement id field and then
running into the extra work (both CPU load and increased disk space)
needed to handle the variable sized integer storage method used by
SQLite. This would lead to the type of logarithmic growth you are
seeing. The first few iterations used short single byte integer values,
the next bunch use 2 byte integer values, etc. The autoincrement field
would cause SQLite to continue at the same speed after restarting the
application as you have described, since the next field values used
would continue from where it left off at the end of the previous run.
I would have expected the time to stabilize on 3 byte values fairly
qucikly, and then only change again when switching to values that
required 4 bytes.
This may be a part of the answer even if it is not the complete answer.
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
