The environment is:
Linux at91sam9260nand 2.6.25.10
SQLite 3.6.5
liubin liu wrote:
>
> 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-tp26909939p26910009.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
