[sqlite] speed, writing spends 30ms, reading spends 2ms, still slow

[liubin liu]

the process runs on a board with Atmel AT91SAM9260 (Clocks: CPU 198 MHz,
...), Memory: 16MB, yaffs on NAND 256MB.

the time of writing one row is more than 20mS, and the time of reading one
row is more than 3mS.

Is it the ceiling of the SQLite3? If no, how to improve it? espically
improving the routine of writing.

Thanks in advance. :)

________________________________code_______________________________________

// 测试sqlite3 写入、读取、修改数据库的时间 (一个进程只包含一个打开、关闭数据库), 20091223
//
// arm-linux-gcc -Wall -g -lpthread -lsqlite3    sqlite3speed.c   -o
sqlite3speed

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>     // for system(*)
#include <sys/time.h>   // for gettimeofday(*)

void test_sqlite3_time_write (void);
void test_sqlite3_time_read (void);

sqlite3             *g_db = NULL;
struct timeval      g_tv1;
struct timeval      g_tv2;
struct timezone     g_tz;

int main (void)
{
    int ret = -1;
    int c = 0;
    char *ct = "CREATE TABLE testspeed (id INTEGER PRIMARY KEY, type
INTEGER, content TEXT)";

    ret = sqlite3_open ("testspeed.db", &g_db);
    ret = sqlite3_exec (g_db, ct, NULL, NULL, NULL);

    printf ("test sqlite3's writing、reading time\n");
    printf ("1、 writing\n");
    printf ("2、 reading\n");
    while ((c = getchar()) != 'q')
    {
        switch (c)
        {
            case '1':
                test_sqlite3_time_write ();
                break;
            case '2':
                test_sqlite3_time_read ();
                break;
            default:
                break;
        }
    }

    ret = sqlite3_close (g_db);

    return 0;
}

void test_sqlite3_time_write (void)
{
    int  ret  = -1;
    char *i_f = "INSERT OR REPLACE INTO testspeed VALUES (%d, %d, %Q)";
    char *i_s = NULL;

    /*
    gettimeofday (&g_tv1, &g_tz);
    i_s = sqlite3_mprintf (i_f, 1, 22, "reliable测试");
    ret = sqlite3_exec (g_db, i_s, NULL, NULL, NULL);
    gettimeofday (&g_tv2, &g_tz);
    ret = system ("sqlite3 testspeed.db \"SELECT * FROM testspeed\"");
    if (0 == ret)
    {
        printf ("using sqlite3_exec() writing one row spends: %d us\n\n",
                (int) ((g_tv2.tv_sec - g_tv1.tv_sec)*1000000 + g_tv2.tv_usec
- g_tv1.tv_usec));
        system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
    }
    */


    sqlite3_stmt *stmt = NULL;


    gettimeofday (&g_tv1, &g_tz);

    i_s = sqlite3_mprintf (i_f, 1, 22, "test可靠吗？");
    ret = sqlite3_prepare_v2 (g_db, i_s, -1, &stmt, NULL);
    if (SQLITE_OK == ret)
    {
        ret = sqlite3_step (stmt);
        if (SQLITE_DONE == ret)
        {
            sqlite3_finalize (stmt);

            gettimeofday (&g_tv2, &g_tz);

            ret = system ("sqlite3 testspeed.db \"SELECT * FROM
testspeed\"");
            if (0 == ret)
            {
                printf ("using sqlite3_prepare_v2() + sqlite3_step() writing
one row spends: %d uS\n\n",
                    (int) ((g_tv2.tv_sec - g_tv1.tv_sec)*1000000 +
g_tv2.tv_usec - g_tv1.tv_usec));
                system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
            }
        }
        else
                sqlite3_finalize (stmt);
    }
}

void test_sqlite3_time_read (void)
{
    int  ret  = -1;
    char *i_f = "SELECT * FROM testspeed WHERE id=%d";
    char *i_s = NULL;


    sqlite3_exec (g_db, "INSERT OR REPLACE INTO testspeed VALUES (1, 22,
\"测试, yes\")", NULL,NULL,NULL);

    /*
    char **sgt_medi = NULL;
    char **sgt_resu = NULL;
    int column = 0;
    int row = 0;

    gettimeofday (&g_tv1, &g_tz);
    i_s = sqlite3_mprintf (i_f, 1);
    ret = sqlite3_get_table (g_db, i_s, &sgt_medi, &row, &column, NULL);
    if (SQLITE_OK == ret)
    {
        sgt_resu = sgt_medi + column;
        printf ("%d, %d, %d, %s\n", row, atoi (sgt_resu[0]), atoi
(sgt_resu[1]), sgt_resu[2]);

        gettimeofday (&g_tv2, &g_tz);
        printf ("using sqlite3_get_table() reading one row spends: %d
uS\n\n",
                (int) ((g_tv2.tv_sec - g_tv1.tv_sec)*1000000 + g_tv2.tv_usec
- g_tv1.tv_usec));

        system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
    }
    */


    sqlite3_stmt *stmt = NULL;

    gettimeofday (&g_tv1, &g_tz);

    i_s = sqlite3_mprintf (i_f, 1);
    ret = sqlite3_prepare_v2 (g_db, i_s, -1, &stmt, NULL);
    if (SQLITE_OK == ret)
    {
        ret = sqlite3_step (stmt);
        if (SQLITE_ROW == ret)
        {
            printf ("%d, %d, %s\n",
            sqlite3_column_int (stmt, 0), sqlite3_column_int (stmt, 1),
sqlite3_column_text (stmt, 2));

            sqlite3_finalize (stmt);

            gettimeofday (&g_tv2, &g_tz);
            printf ("using sqlite3_prepare_v2() + sqlite3_step() reading one
row spends: %d uS\n\n",
            (int) ((g_tv2.tv_sec - g_tv1.tv_sec)*1000000 + g_tv2.tv_usec -
g_tv1.tv_usec));

            system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
        }
        else
            sqlite3_finalize (stmt);
    }
}


-- 
View this message in context: 
http://old.nabble.com/speed%2C-writing-spends-30ms%2C-reading-spends-2ms%2C-still-slow-tp26909939p26909939.html
Sent from the SQLite mailing list archive at Nabble.com.

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users