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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users