Use sqlite3_prepare/bind/step instead of sqlite3_exec and group your
activity into BEGIN ... COMMIT transactions.
Breeze Meadow wrote:
> Hi,
>
> I found sqlite is kind of slow on windows (2003 server and XP professional).
> Here is what i did:
> 1) downloaded and installed sqlite3.dll (version 3.6.5).
> 2) created a MSVC 6.0 C++ projects with the following code, which basically
> runs a list of SQL statments on a opened database:
>
> #include <stdio.h>
> #include "pctimer.h"
> #include "sqlite3.h"
> int main(int argc, char** argv)
> {
> int ret;
> double start, duration;
> char* error = NULL;
> char* database_name = "test.dat";
> sqlite3* database = NULL;
> ret = sqlite3_open(database_name, &database);
> char* sql_list[] = {
> "create table if not exists t1(f1)",
> "insert into t1 values('a')",
> "select * from t1",
> "drop table if exists t1"
> };
> for (int i = 0; i < 4; i++)
> {
> TIME(ret = sqlite3_exec(database, sql_list[i], NULL, NULL, &error), 1000);
> printf("sql_list[%d] = %d, %f msec\n", i, ret, duration);
> }
> sqlite3_close(database);
> return 0;
> }
>
> in "pctimer.h"
> #define TIME(Statement, Unit) start = pctimer(), Statement, duration =
> (pctimer() - start) * Unit
> #include <windows.h>
>
> __inline pctimer_t pctimer()
> {
> static LARGE_INTEGER pcount, pcfreq;
> static int initflag;
> if (!initflag)
> {
> QueryPerformanceFrequency(&pcfreq);
> initflag++;
> }
> QueryPerformanceCounter(&pcount);
> return (double)pcount.QuadPart / (double)pcfreq.QuadPart;
> }
>
> Here is a typical outputs from the program running in debug mode:
>
> sql_list[0] = 0, 115.522582 msec
> sql_list[1] = 0, 146.979062 msec
> sql_list[2] = 0, 0.152468 msec
> sql_list[3] = 0, 127.423370 msec
>
> The "create table", "insert into" and "drop table" are slower than I expected
> whereas the "select" is ok (compared to published sqlite performance). I
> tried turning off antivirus but got the same results. Notice my constraint is
> these SQL statements must run one by one, not within a transaction.
>
> My questions are:
> 1) are these expected performance on windows?
> 2) if not, what can be done to improve it?
>
> Many thanks in advance.
>
> FLX
>
>
>
>
> _______________________________________________
> 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
