[sqlite] Performance problem

Sun, 02 Nov 2003 02:43:12 -0800

Hi,
I've written the following program to test the sqlite performance for a specific scenario I have.
I've used most tricks I've found but still when the database gets big the performance gets unacceptable.
The performance problem happens of course only if the indexes are defined.
Since I need the indexes, is there any other option that can make it faster ?
When the database gets big the insert rate gets to 50 rows per second.
Thanks in advance.

* It seems that the larger the DB gets, the journal copies more and more pages out of the database into the file, which kills performance.

=================================================================================
// sqlite_test.cpp : Defines the entry point for the console application.
//
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <windows.h>

#include "sqlite.h"

#define CHECK_SQL(stat) \
   if ((l_nErr=stat) != 0) \
   { \
       printf("Error in sql %d, %s\n\n\n\n\n\n", l_nErr, l_pszError);\
       exit(-1); \
   }

#define REPORT_SQL(stat) \
   if ((l_nErr=stat) != 0) \
   { \
       printf("Error in sql %d, %s\n", l_nErr, l_pszError);\
   }


int main(int argc, char* argv[])
{
   char * l_pszError;
   int l_nErr;
   if (unlink("test.db")!=0)
   {
       printf("Failed to delete old database\n");
   }
   if (unlink("test.db-journal")!=0)
   {
       printf("Failed to delete old database journal\n");
   }

sqlite *l_hDB= sqlite_open("test.db", 0, &l_pszError);
CHECK_SQL(
sqlite_exec(
l_hDB,
"PRAGMA default_cache_size = 100000;",
NULL,
NULL,
&l_pszError));

   CHECK_SQL(
   sqlite_exec(
                   l_hDB,
                   "PRAGMA default_synchronous = OFF;",
                   NULL,
                   NULL,
                   &l_pszError));

   REPORT_SQL(
   sqlite_exec(
                   l_hDB,
                   "create table test ( parent_id INTEGER, age INTEGER)",
                   NULL,
                   NULL,
                   &l_pszError));

   REPORT_SQL(
   sqlite_exec(
                   l_hDB,
                   "create index  test_parent_id on test(parent_id)",
                   NULL,
                   NULL,
                   &l_pszError));

   REPORT_SQL(
   sqlite_exec(
                   l_hDB,
                   "create index  test_age on test(age)",
                   NULL,
                   NULL,
                   &l_pszError));

   REPORT_SQL(
   sqlite_exec(
                   l_hDB,
                   "begin transaction",
                   NULL,
                   NULL,
                   &l_pszError));

   for (int i=0; i<10000; ++i)
   {
       static int l_nTrans=0;
       DWORD l_nCurrTime=GetTickCount();

       for (int j=0; j<300; ++j)
       {
           REPORT_SQL(
           sqlite_exec_printf(
                           l_hDB,
                           "insert into test values(%d, %d)",
                           NULL,
                           NULL,
                           &l_pszError,
                           i, rand()));

}
if (!((++l_nTrans) % 100))
{
REPORT_SQL(
sqlite_exec(
l_hDB,
"commit",
NULL,
NULL,
&l_pszError));
REPORT_SQL(
sqlite_exec(
l_hDB,
"begin transaction",
NULL,
NULL,
&l_pszError));
printf("Entered %d events\n", i);
}
DWORD l_nEndTime=GetTickCount();
static int l_nPr=0;
if (!((++l_nPr) % 100))
printf("%d\n", l_nEndTime-l_nCurrTime);
}

   REPORT_SQL(
   sqlite_exec(
                   l_hDB,
                   "commit",
                   NULL,
                   NULL,
                   &l_pszError));

   return 0;
}

=================================================================================


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to