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

[Doug Nebeker]

Be aware that the Windows GetTickCount call has a resolution of 10 to 15
ms on most machines, so that could throw throw your timings off if
you're timing each individual test case as it appears below.  To get
better timer resolution, use QueryPerformanceCounter.

Now, would that make SQLite3 looks slower than v2?  I wouldn't think
so--you'd think it would skew the results equally for both tests.  

Doug

-----Original Message-----
From: Nitin Kashyap [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 13, 2007 2:51 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite v/s SQLite3 Performance Assay

> 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

This email was sent to you by Reuters, the global news and information company. 
To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender, 
except where the sender specifically states them to be the views of Reuters 
Limited.

Reuters Limited is part of the Reuters Group of companies, of which Reuters 
Group PLC is the ultimate parent company.
Reuters Group PLC - Registered office address: The Reuters Building, South 
Colonnade, Canary Wharf, London E14 5EP, United Kingdom
Registered No: 3296375
Registered in England and Wales



-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------