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

[Dennis Cote]

Nitin Kashyap wrote:
 
I have tried to verify for any test artifact as suggested by you; but 
still

the result were same.
Requesting for further guidance as to what can cause the slow 
responnce from

SQLite3 compared
to SQLite2.

Also, I had sent the snippet from the test driver in previous posts, 
which

can be used to derive
observation at your end, which I'm sure will concure with mine.



Nitin,

Can you supply the complete source for your test application, rather 
than just the snippets posted earlier? I would like to review your tests 
to see why this is happening.


One thing I noticed is the queries you posted are sorting the results 
since there is no suitable index, which means that sqlite is building 
temporary tables in memory prior to sorting. It is known that in memory 
tables have slowed down in sqlite3 since they have been merged with the 
normal disk based table handling code (in sqlite2 they were handled with 
special in memory tree code). This may be the cause of part of your 
speed difference.


Dennis Cote






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

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

[Nitin Kashyap]

 "Nitin Kashyap" <[EMAIL PROTECTED]> wrote:


>
> My Intentions are towards exploring the reason behind these differences;
> and what can be done to counter these performance differences. I'm
> seeking some pointers from the Community.
>

I'm guessing that the poor performance you are getting
from SQLite3 is a test artifact of some kind.



Drh

I have tried to verify for any test artifact as suggested by you; but still
the result were same.
Requesting for further guidance as to what can cause the slow responnce from
SQLite3 compared
to SQLite2.

Also, I had sent the snippet from the test driver in previous posts, which
can be used to derive
observation at your end, which I'm sure will concure with mine.

Thanks & Regards
Nitin K

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

[Nitin Kashyap]

Hi Doug,

My bad... the getTickCount() in the snippet is basically
a wrapper over ftime call, and the same is used on the
linux platform also for timing.

time_t getTickCount()
{
 timeb tm = {0};
 ftime();
 return ( (tm.time*1000) + ((time_t)tm.millitm) );
}

Thanks & Regards
Nitin K

On 3/13/07, Doug Nebeker <[EMAIL PROTECTED]> wrote:


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


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

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

[drh]

"Nitin Kashyap" <[EMAIL PROTECTED]> wrote:
> 
> My Intentions are towards exploring the reason behind these differences;
> and what can be done to counter these performance differences. I'm 
> seeking some pointers from the Community.
> 

I use SQLite for a lot of different things (as you would expect)
over the course of many years.  And my experience has been that 
SQLite3 is almost always faster than SQLite2.  Sometimes remarkably 
faster.  I'm guessing that the poor performance you are getting
from SQLite3 is a test artifact of some kind.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

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, , ) ==
SQLITE_OK )
{
while( sqlite_step( pVm
   ,
   ,(const char ***) 
   ,(const char ***) ) == SQLITE_ROW
)
{
 i++;
}
sqlite_finalize(pVm, );
}
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, , ) ==
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]
-

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, , ) == SQLITE_OK )
   {
   while( sqlite_step( pVm
  ,
  ,(const char ***) 
  ,(const char ***) ) == SQLITE_ROW )
   {
i++;
   }
   sqlite_finalize(pVm, );
   }
   else
   {
   printf("err in sql:  : %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, , ) ==
SQLITE_OK )
   {
   while( sqlite3_step(pSqlStmt) == SQLITE_ROW )
   {
   i++;
   }
   sqlite3_finalize(pSqlStmt);
   }
   else
   {
   printf("err in sql:  : %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

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

[Dan Kennedy]

> My Intentions are towards exploring the reason behind these differences; and
> what can be
> done to counter these performance differences. I'm seeking some pointers
> from the Community.

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.

> Many Thanks In Advance.
> 
> Test Setups:
> +--+--+-+-+-+
> | CPU   |(x86) |  (x86_64)| |
> | |
> |---|   P4 2.4 GHz |  Core2Duo| |   Athlon|
> Davinci |
> | OS|  | 1.86GHz x 2  |BSP15|   X2 (64)
> | |
> +---+--+--+-+-+-+
> | WinXP(32) |  X   |  X   | |
> | |
> +---+--+--+-+-+-+
> | WinXP(64) |  |  X   | |  X
> | |
> +---+--+--+-+-+-+
> | Linux(32) |  X   |  X   |  X  |
> |X|
> +---+--+--+-+-+-+
> | Linux(64) |  |  X   | |  X
> | |
> +---+--+--+-+-+-+
> 
> DB Schema:
>  It Consists of 4 Identical Tables
> tbl01{ code integer primary key
>   ,code01
>   ,code02
>   ,code03
>   ,code04
>   ,orderField
>   ,field01 }
> 
> Implementation:
> Application were written in C using SQLite & SQLite3's C API sets.
> 
>   Case I:
> SQL Insert Queries where fired in Sequential Progression; making 10
> Entries
> in First Table; 100 Entries in 2nd Table; 1000 Entries in 3rd Table and
> finally 1 Entries in 4th Table; Data below is Collective Time Taken
> to make Inserts in all 4 tables, expressed in millisecs.
> 
>  Insert |   SQLite|   SQLite3-> 0 Entries
> +-+-
>  Win32 x86  |78896| 97800
>  Win32 x86_64   |82100| 85000
>  Win64 x86_64   |  -  |   -
>  Linux32 x86|76900|100016
>  Linux32 x86_64 |87728| 99004
>  Linux64 x86_64 |79200| 99102
>  Linux64 x64|79788| 98794
>  Linux BSP15|37888| 37566
>  Linux Davinci  |  -  |   -
> --+-
> 
>Case II:
>  SQL Select with simple query on a single table fetching all records.
> 
>  Select on Simple Qry|   SQLite|   SQLite3   -> 1 (x 8 Cols)
> Entries
> -+-+-
>  Win32 x86   | 125 |   578
>  Win32 x86_64| |
>  Win64 x86_64|  -  |   -
>  Linux32 x86 |   8 |   297
>  Linux32 x86_64  |   6 |   251
>  Linux64 x86_64  |   6 |   149
>  Linux64 x64 |   7 |   144
>  Linux BSP15 | 287 | 22069
>  Linux Davinci   |  -  |   -
> ---+-
> 
>Case III:
>  SQL Select with Join of 2 Tables fetching all records.
> 
>  Select on Moderate Qry|   SQLite|   SQLite3 -> 1 (x 15
> Cols) [2 Table Join]
> ---+-+-
>  Win32 x86 | 5532|  1172
>  Win32 x86_64  | |
>  Win64 x86_64  |  -  |   -
>  Linux32 x86   |  439|   669
>  Linux32 x86_64|  251|  1108
>  Linux64 x86_64|  272|  1120
>  Linux64 x64   |  259|  1090
>  Linux BSP15   | 9258| 49773
>  Linux Davinci |  -  |   -
> ---+-+-
> 
>Case IV:
>  SQL Select with Join of 3 Tables fetching redundant records.
> 
>  Select on Complex Qry|   SQLite|   SQLite3  -> 9 (x 22
> Cols) [3 Table Join with redundant entries]
> --+-+-
>  Win32 x86| 6593|110157
>  Win32 x86_64 | |
>  Win64 x86_64 |  -  |  

[sqlite] SQLite v/s SQLite3 Performance Assay

[Nitin Kashyap]

Hi All,

I Carried out a small assay comparing performance between SQLite-2.8.16 &
SQLite-3.3.9;
Over Multiple platforms & Operating Environments.

Please consider the observations of the assay below. There seems to be a
very clear
and visible difference in performance of SQLite & SQLite3; for which
performance
acceptability seems questionable on some embedded platforms: -
 - SQLite & SQLite3 on the same platform.
 - SQLite on 2 different OS on same platform
 - SQLite3 on 2 differnt OS on same platform

My Intentions are towards exploring the reason behind these differences; and
what can be
done to counter these performance differences. I'm seeking some pointers
from the Community.

Many Thanks In Advance.

Test Setups:
+--+--+-+-+-+
| CPU   |(x86) |  (x86_64)| |
| |
|---|   P4 2.4 GHz |  Core2Duo| |   Athlon|
Davinci |
| OS|  | 1.86GHz x 2  |BSP15|   X2 (64)
| |
+---+--+--+-+-+-+
| WinXP(32) |  X   |  X   | |
| |
+---+--+--+-+-+-+
| WinXP(64) |  |  X   | |  X
| |
+---+--+--+-+-+-+
| Linux(32) |  X   |  X   |  X  |
|X|
+---+--+--+-+-+-+
| Linux(64) |  |  X   | |  X
| |
+---+--+--+-+-+-+

DB Schema:
It Consists of 4 Identical Tables
   tbl01{ code integer primary key
 ,code01
 ,code02
 ,code03
 ,code04
 ,orderField
 ,field01 }

Implementation:
   Application were written in C using SQLite & SQLite3's C API sets.

 Case I:
   SQL Insert Queries where fired in Sequential Progression; making 10
Entries
   in First Table; 100 Entries in 2nd Table; 1000 Entries in 3rd Table and
   finally 1 Entries in 4th Table; Data below is Collective Time Taken
   to make Inserts in all 4 tables, expressed in millisecs.

Insert |   SQLite|   SQLite3-> 0 Entries
   +-+-
Win32 x86  |78896| 97800
Win32 x86_64   |82100| 85000
Win64 x86_64   |  -  |   -
Linux32 x86|76900|100016
Linux32 x86_64 |87728| 99004
Linux64 x86_64 |79200| 99102
Linux64 x64|79788| 98794
Linux BSP15|37888| 37566
Linux Davinci  |  -  |   -
   --+-

  Case II:
SQL Select with simple query on a single table fetching all records.

Select on Simple Qry|   SQLite|   SQLite3   -> 1 (x 8 Cols)
Entries
   -+-+-
Win32 x86   | 125 |   578
Win32 x86_64| |
Win64 x86_64|  -  |   -
Linux32 x86 |   8 |   297
Linux32 x86_64  |   6 |   251
Linux64 x86_64  |   6 |   149
Linux64 x64 |   7 |   144
Linux BSP15 | 287 | 22069
Linux Davinci   |  -  |   -
   ---+-

  Case III:
SQL Select with Join of 2 Tables fetching all records.

Select on Moderate Qry|   SQLite|   SQLite3 -> 1 (x 15
Cols) [2 Table Join]
   ---+-+-
Win32 x86 | 5532|  1172
Win32 x86_64  | |
Win64 x86_64  |  -  |   -
Linux32 x86   |  439|   669
Linux32 x86_64|  251|  1108
Linux64 x86_64|  272|  1120
Linux64 x64   |  259|  1090
Linux BSP15   | 9258| 49773
Linux Davinci |  -  |   -
   ---+-+-

  Case IV:
SQL Select with Join of 3 Tables fetching redundant records.

Select on Complex Qry|   SQLite|   SQLite3  -> 9 (x 22
Cols) [3 Table Join with redundant entries]
   --+-+-
Win32 x86| 6593|110157
Win32 x86_64 | |
Win64 x86_64 |  -  |   -
Linux32 x86  |  484|   1059861
Linux32 x86_64   | |
Linux64 x86_64   | |
Linux64