Re: [sqlite] SQLite v/s SQLite3 Performance Assay

[Nitin Kashyap]

Version 3 has a different default safety-level (default FULL) to
version 3 (default NORMAL). So if you didn't explicitly set the
safety-level during the tests, then version 3 was syncing the
disk more often than version 2. I think this might be why version 3
appears slower in Test Case I (Inserts).

The results of cases II to IV seem odd. Can you post the test
code to the list?

Dan.

Hi Dan,
  You seem to be right on the Sync end; But it does not explains why
read is taking more time.  Below are the snippet u requested.

Select Query:
#define SQLQuery4 "select tbl01.code, * from tbl01, tbl02, tbl03, tbl04 "
\
                "where tbl01.code=tbl02.code01 "
\
                "and tbl01.code=tbl03.code01 "
\
                "and tbl01.code=tbl04.code01 "
\
                "order by tbl04.orderField "

#define SQLQuery2 "select tbl03.code, * from tbl03, tbl04 "
\
                "where tbl03.code = tbl04.code03 "
\
                "order by tbl04.orderField "

SQLite2 Snippet:
{
   end getTickCount();

   /******************** Start Select: 2 table *********************/
   printf("Perfroming Simple Select of 2 Table...");
   fflush(stdout);
   beg = end;
   i=0;
   snprintf(sqlQry, 1024, "%s",SQLQuery2);
   if( sqlite_compile(pSource, sqlQry, NULL, &pVm, &errMsg) == SQLITE_OK )
   {
       while( sqlite_step( pVm
                          ,&numColumn
                          ,(const char ***) &ppRowValues
                          ,(const char ***) &ppColNames) == SQLITE_ROW )
       {
            i++;
       }
       sqlite_finalize(pVm, &errMsg);
   }
   else
   {
       printf("err in sql: <line:%d> : %s\n",__LINE__, sqlQry);
       if(errMsg != NULL)
           printf("errMsg: %s\n", errMsg);
       goto cleanUp;
   }
   end = getTickCount();
   printf("Done\n");
   printf("Time To Select 2 Table with entries returned(%d),(%d): %ld\n",
i, numColumn, (end-beg));
}

SQLite3 Snippet:
{
   end = getTickCount();
   /******************** Start Selecting: 2 table *********************/
   printf("Perfroming Simple Select of 2 Table");
   beg = end;
   i=0;
   snprintf(sqlQry, 1024, "%s",SQLQuery1);
   if( sqlite3_prepare(pSource, sqlQry, -1, &pSqlStmt, &pzTail) ==
SQLITE_OK )
   {
       while( sqlite3_step(pSqlStmt) == SQLITE_ROW )
       {
           i++;
       }
       sqlite3_finalize(pSqlStmt);
   }
   else
   {
       printf("err in sql: <line:%d> : %s\n",__LINE__, sqlQry);
       if(errMsg != NULL)
           printf("errMsg: %s\n", errMsg);
       goto cleanUp;
   }
   end = getTickCount();
   printf("Done\n");
   printf("Time To Select 2 Table with entries returned(%d): %ld\n", i,
(end-beg));
}

Thanks & Regards
Nitin K