Mimer SQL vs SQLite Performance Benchmark #2 - Home

apatheticyogurtSoftware and s/w Development

Dec 13, 2013 (3 years and 7 months ago)

101 views


Fredrik Malmström

Mimer Information Technology


Developer of the Mimer SQL product family


Enterprise Solutions


Industrial/Embedded Solutions


Mobile Solutions


Used in mission critical systems world wide


World
-
wide presence through customers and partners


HQ in Uppsala, Sweden


Development started in the 1970s

Mimer SQL
positioning



Footprint

Mimer SQL Product

Family

Large

Systems

Resource constrained

Systems

Mimer SQL Mobile

Mimer SQL Enterprise

Mimer SQL Embedded & Real
-
Time





Mimer SQL Micro

Mimer SQL Nano



Mimer SQL in Mission
-
Critical Systems


Controls flow of parts to the assembly lines at the
Volvo car plant in Gothenburg


Mission critical


In production since 20+ years

Mimer SQL in Life
-
Critical System


National Blood Service, England & Wales


Nation
-
wide system r
esponsible for all donated blood and tissues


System controls 2.5 million donations annually


3,500+ active users


Database of 1 billion records


+8 million / week



Our experience is that

Mimer SQL rarely

g
ives us

a problem and has a very

lo
w
maintenance overhead,

so there’s no need

f
or any

expensive database

administrators to run the system
.”

Ian Henderson,

MD at Savant Enterprises,

the developer of PULSE


Stock Level on 09 Oct 2008

Mobile Reference Customer

Mimer products
are
included in approximately

5
-
7% of all mobile phones in 2008.

Some recent devices with Mimer Inside


Phones based on Mimer SQL


K850


Z750


W910




C902


C702


W760i

Sony Ericsson applications

-

using Mimer


PIM


Phonebook


Calendar


To
-
do


Sync


Messaging


SMS


MMS


E
-
mail


File Content Library (FCL)


Media center


Media player


Photo album


MTP


Active Sync


Mobile TV


Java Payment API



3 more in development!

Sony Ericsson’s use of Mimer SQL


Uses SQL extensively, such as:


Procedures


Triggers


Views


Constraints


One additional Mimer SQL installation per second


Upgrades databases and applications


Over the air


Via USB
-
cable

Mimer SQL Mobile

Mimer concept


The single content engine

Data

Data

Data

Data

Data

Applications

User interface

Application services

OS services

Base services

Kernel

Application Centric Approach

Data Centric Approach

Mimer SQL key features


High performance


Scalable


Self
-
maintaining


Small footprint


Rich functionality


Platform independent


Full SQL support


Designed for embedded and mobile solutions


Performance


Effecient server communication


Bulk operations


Low level I/O


Operations performed in the background


Transaction system highly optmized


Group commit


B*
-
trees perfectly balanced

Scalability


When the amount of data grows


Or when there is an increasing number of clients


Mimer uses the same database kernel for mobile phones as for
large enterprise systems



Database cache is efficient both when


There is a small amount of primary memory


There is large amount of memory available



Algorithms are designed to be more efficient when load increases

Self
-
maintaining


There is no need to run compression/cleanup/vacuum
utilities


In fact, there are no such utilities


Mimer performs this automatically as the data is
inserted/deleted

Small footprint


A server with support for stored procedures, triggers,
and all other features in Mimer SQL occupies less
than 300k on the OSE platform

Mimer SQL Footprint


0
100
200
300
400
500
600
kb
RAM
152
24
ROM
405
273
Micro Server
Nano Server
Mimer SQL Footprint details


0,0
100,0
200,0
300,0
400,0
500,0
600,0
kb
Data
10,2
0,1
0,1
DB Cache
100,0
16,5
Heap
36,2
3,4
Stack
5,6
4,3
0,2
Const data
143,6
39,3
0,0
Code
261,3
234,0
10,2
Micro Server
Nano Server
Client
Constant data
-

Unicode


Mimer SQL Micro: 144k


Collation and language support: 134k


Mimer SQL Nano: 39k


Unicode normalization: 32k



Allows support for national character data type


Collations for 160+ languages

Application structure


Smaller applications


Validation done by
database server
automatically


Less communication


Allow other
applications to access
the database


Platform independent

Data validation

Insert record

Insert record

Update record

Read input from user

Read input from user

Proprietary,

ISAM,

Etc.

Mimer SQL

Procedure

Higher level abstraction


The application is
less dependent on
database structure

Data validation

Insert record

Insert record

Update record

Input from user

Input from user

Store Contact

Proprietary,

ISAM,

Etc.

Mimer SQL

Procedure

Store ContactId

Store Name

Store IdEmail

Store …

Mimer SQL API:s


Mimer SQL Enterprise


JDBC, .NET, ODBC, Embedded SQL


Mimer SQL Mobile


J2ME, .NET Compact Framework, Symbian C++ API


Mimer SQL Mobile, Micro Edition


Micro C API

Upgrade


Replace system databank


Contains changes since last version(s)


New/altered/dropped tables, indexes, procedures,…


All changes are automatically applied


Can handle interruptions of the upgrade process


This is difficult for an application to handle…


Upgrade may happen over time


When an external flash is inserted with old versions of
database files

Real
-
time

application

Mixed soft and real
-
time support

Soft

applications

Database

Server

Real
-
time

applications

Guaranteed

response
times

Database

Cache

Why Mimer SQL Mobile

SMS, Photo, Music, Email, MMS,

GPS, Localization, Telephony,

Contacts, Calendar, Radio, TV, etc


Gigabytes of data, Multi
-
tasking,

Upgrades, Limited HW resources,

Flash
-
cards, Data consistency,

Multilingual, File
-
system,

Cross
-
platform, Open APIs,

Tight schedules

Why Mimer SQL Mobile on the device

Why Mimer SQL Mobile 1(5)



Local storage capacity (100 GB+) Music / Books / Videos / Personal life
recorders / Second brains
”, David Wood, EVP, Symbian, 2007


Gigabyte of Data


Capable of handling Tera
-
bytes of data


Proven enterprise
-
class scalability


Multitasking


Multi
-
threaded server architecture


Parallel access to data from applications


Full transaction support


Upgrades


Automated upgrade


Data and SQL statements


All deltas in one file


Over The Air



Why Mimer SQL Mobile 2(5)

Limited Hardware Resources


Small footprint (<300 Kbytes)


Efficient stack and heap optimization


Shared server architecture


Dynamic cache


Less application coding


Flash
-
cards


Data automatically accessible when inserted


Scalability to tera
-
bytes of data


Auto create of database files


Transparent for applications


Over The Air



Flash card support


Create a union view of two
tables on the internal and
external flash


The application retrieves data
transparently


When external flash is
present all data is seen


Auto
-
create also supported!

App

Union

Internal flash

External flash

App

Internal flash

Why Mimer SQL Mobile 3(5)



Data consistency


Fully atomic transaction control


Data integrity rules


Strong data typing


Domains, views


Stored procedures & Triggers


Always correct even if battery is removed


Multilingual


Searching & sorting 160+ languages


SQL Collations


No programming needed


Extendable


Smallest footprint in industry


World class performance



select word from tab

order by word collate japanese;



word

ていねい

-

teinei
-

polite

テープ

-

teepu
-

tape

でぐち

-

deguchi
-

exit

テスト

-

tesuto
-

test

では

-

dewa
-

well, then

デパート

-

depaato
-

department store


Why Mimer SQL Mobile 4(5)

File system


Unified data access API (SQL)


Best performance always guaranteed


Database cache


Max, Min, and Goal size for each file


Handles all types of storage


Shared access under full transactional control


Cross
-
platforms


Supports


EMP


Linux


OSE


Symbian OS




Windows Mobile


Built
-
in portability



New (customized) platforms easily supported


Symbian

Windows

Mobile

OSE

Linux



Why Mimer SQL Mobile 5(5)

Open APIs


ISO/ANSI SQL:1999, SQL:2003


ISO PSM (stored procedures)


Java (JDBC, J2ME)



C/C++


.NET Compact Framework


ODBC


Tight schedules


Handles all data management



Handles all concurrency


Cross platform support


Enables re
-
use


High priority customized extensions


Faster time to market






Mimer SQL vs SQLite Performance
Benchmark #1



Inserting 10,000 rows in a table.


Eight test cases:


1 transaction, 10,000 rows/transaction


5 transactions, 2,000 rows/transaction


20 transactions, 500 rows/transaction


200 transactions, 50 rows/transaction


1,000 transactions, 10 rows/transaction


5,000 transactions, 2 rows/transaction


10,000 transactions, 1 row/transaction

Mimer SQL vs SQLite Performance
Benchmark #1



Insert of 10000 rows
0
200
400
600
800
1000
1
5
20
200
1000
2000
5000
10000
Number of transactions
Elapsed time (s)
Mimer SQL
SQLite
Mimer SQL vs SQLite Performance
Benchmark #1



Conclusion


Mimer SQL offers scalable and predictable performance


The size of the transaction has no impact on response times and
throughput


Transaction commits are slow in SQLite


This is confirmed by Symbian

”…
COMMIT is a relatively slow operation. Hence if performance is
a priority you should strive to minimize the number of COMMITs.”

(Source
Symbian Developer Library: How to make efficient Use of SQL)




Mimer SQL vs SQLite Performance
Benchmark #2



Calculate the average value of a numerical column in a table
containing photos (BLOBs)










Mimer SQL: 0.02 s


SQLite: 32.0 s

Select AVG
0
5
10
15
20
25
30
35
Mimer SQL
SQLite
Elapsed time (s)
Mimer SQL vs SQLite Performance
Benchmark #3



Count the number of rows in the result set of a two table join










Mimer SQL: 0.03 s


SQLite: 1.0 s

Select Count two tables join
0
0,2
0,4
0,6
0,8
1
1,2
Mimer SQL
SQLite
Elapsed time (s)
Mimer SQL vs SQLite Performance
Benchmark #4



Delete all records in a table










Mimer SQL: 0.29 s


SQLite: 179 s

Delete of all records in a table
0
20
40
60
80
100
120
140
160
180
200
Mimer SQL
SQLite
Elapsed time (s)
Mimer Face Recognition

New ways of searching databases



Face detection / recognition


Use any image format (jpeg, bitmap, etc)


Rank pictures according to similarity


Integrated as user defined type with methods to match faces


Automatic multi
-
level indexing of faces in database


Face coordinates


Face identification value


Person id


Coordinates (X1,Y1) (X2, Y2)

FaceId=123456

PersonId=”Kathy”

-

Image processing

Search for faces in photo albums


Seamless integration with SQL


Indexes maintained automatically by Mimer SQL


Find photos of a person with one single SQL
statement


Small footprint


Possible to run on mobile and embedded devices

Example: Search for a person in
the photo album


select

Image
from

Photos

where

Image.
HasFace
(
select

Image.
FaceId
(1)


from

AddressBook


where

Name = ’Gunne’)

and

Image.
DateTaken

> DATE’2007
-
02
-
15’



Column Image in Photos and AddressBook is created
with a user defined type with methods
HasFace
,
FaceId
, and
DateTaken

Want to try Mimer SQL?


Full function versions for all platforms available
for free download from


http://developer.mimer.com/


and visit

www.mimer.com

Questions?