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