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

Reply via email to