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 10000 Entries in 4th Table; Data below is Collective Time Taken
to make Inserts in all 4 tables, expressed in millisecs.
Insert | SQLite | SQLite3 -> 11110 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 -> 10000 (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 -> 10000 (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 -> 90000 (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 x64 | |
Linux BSP15 | 13525 | Application Crashed
Linux Davinci | - | -
----------------------+-------------+-------------
Case V:
SQL Select with Join of 4 Tables fetching all & Redundant records
[About
9000000 Records]. Neither SQLite2 nor SQLite3 was able to give results
for
this and program exited with raising exception for Insufficient Memory.
Thanks & Regards
Nitin K
