sqlite database performance analysis

In this article I have posted an analysis that I performed on sqlite database taking various aspects into consideration. Below, you will find the comparisons in the form of a table along with its graphical representation.


Machine spec used to collect these data :
CPU : Intel(R) Core(TM)2 Duo CPU     E6550  @ 2.33GHz
RAM : 3914524 kB
Cache Size : 4096 KB

1. Per Unit creation Time

TableSize Creation Time Per Unit Creation Time
1000.0002670292.67029E-06
5000.0012970.000002594
10000.002671962.67196E-06
50000.0132722.6544E-06
100000.0387393.8739E-06
500000.1453732.90746E-0
1000000.2891022.89102E-06
5000001.408982.81796E-06
10000004.163534.16353E-06
20000009.667534.83377E-06


sqlite db performance analysis - 1


2. Per Unit Query Time (without Indexing)

TableSize Query Time Without Indexing(150 Queries) Query Time/TableSize Per Unit Query Time (without indexing)
100 0.0577531 0.0005775 0.000385
500 0.211629 0.0004233 0.001411
1000 0.409095 0.0004091 0.002727
5000 2.75264 0.0005505 0.018351
10000 5.05658 0.0005057 0.033711
50000 17.102 0.000342 0.114013
100000 32.2116 0.0003221 0.214744
500000 153.639 0.0003073 1.02426
1000000 317.506 0.0003175 2.116707
2000000 638.897 0.0003194 4.259313



sqlite db performance analysis - 2


3. Per Unit Query Time (with Indexing)

TableSize Indexing Time Query Time With Indexing(150 Queries) Per Unit Query Time(with Indexing)
100 0.00176001 0.014441 9.63E-05
500 0.0136271 0.012418 8.28E-05
1000 0.0286882 0.005842 3.89E-05
5000 0.157743 0.0193071 0.000129
10000 0.338535 0.050318 0.000335
50000 2.17958 0.0541899 0.000361
100000 5.44195 0.046885 0.000313
500000 35.41 0.100924 0.000673
1000000 94.695 0.344231 0.002295
2000000 193.37 0.170012 0.001133


sqlite db performance analysis - 3


sqlite db performance analysis - 4



4. Database Size (with and without indexing)

TableSize dbSize(without Indexing)MB dbSize(with indexing) MB
100 N/A N/A
500 N/A N/A
1000 N/A N/A
5000 N/A N/A
10000 0.996 1.7
50000 5.1

9
100000 11 19
500000 53 92
1000000 107 187
2000000 215 375


sqlite db performance analysis - 5

  -Thanks,
  Manish
comments powered by Disqus