The same tests that were performed on the desktop are now applied to the Mobile platform here. The only difference is I used a subset of the data for the mobile platform, otherwise it would've been way too much for my little phone.
The Databases
Sql Server Mobile Edition 3.0
This is a pretty decent mobile database engine, all things considered. It is a pretty hefty install onto any Pocket PC however. The CAB file is about 1.5mb. It features merge replication with Sql Server 2005, but there are some licensing restrictions on the desktop if you want to go that route.
SQLite 3.3.3 w/ ADO.NET 2.0 Provider 1.0.25.0
SQLite is definitely the lighter database weighing in at 361kb for the whole thing. It doesn't support row-level or even table-level locking (all locks are on the file itself), but it does support multiple processes accessing the database simultaneously. It doesn't support stored procedures. It does however, allow for user-defined functions, aggregate functions and collating sequences, and is ACID compliant. It also implements most of SQL92 and is fully mobile. Databases can be copied freely across *nix and Windows, all CPU types and byte orders, and supports mobile environments.
The Test Machine
Samsung SCH-I730 Pocket PC Phone
Windows Mobile 2003 SE
Intel XScale PXA272 CPU @ 520mhz
128mb RAM
The Data
The following 4 tables were created in each environment. Variations were made to allow for each individual engine's SQL, but in all cases, each table had a primary key defined. Aside from the Invoice table, all the tables also had a foreign key which was not explicitly declared, but on which an index was created.
CREATE TABLE [Invoice] ([PKeyInvoice] INTEGER PRIMARY KEY, [InvoiceNum] VARCHAR(15), [InvoiceDate] DATETIME, [NetCharge] CURRENCY, [ChargeCurrency] VARCHAR(3))
CREATE TABLE [Shipment] ([PKeyShipment] INTEGER PRIMARY KEY, [InvoiceKey] INTEGER, [TrackingId] VARCHAR(16), [ShipDate] DATETIME, [NetCharge] CURRENCY, [ChargeCurrency] VARCHAR(3));
CREATE INDEX ix_invoice ON [Shipment] ([InvoiceKey])
CREATE TABLE [Packages] ([PKeyPackages] INTEGER PRIMARY KEY, [ShipmentKey] INTEGER, [Pieces] INTEGER, [WeightUnits] CHAR, [Weight] REAL, [DimLength] INTEGER, [DimWidth] INTEGER, [DimHeight] INTEGER, [DimUnits] CHAR);
CREATE INDEX ix_packages_shipment ON [Packages] ([ShipmentKey])
CREATE TABLE [Charges] ([PKeyCharges] INTEGER PRIMARY KEY, [ShipmentKey] INTEGER, [Code] VARCHAR(3), [Amount] CURRENCY, [ChargeCurrency] VARCHAR(3));
CREATE INDEX ix_charges_shipment ON [Charges] ([ShipmentKey])
For each table, a comma-delimited file was constructed with a number of rows: 106 invoices, 25799 shipments, 25807 packages, and 80999 charges for a total of 132711 rows.
Importing the Data
An optimized method was chosen for each database engine to import the rows into the tables:
- For SQLite, a parameterized insert using standard SQL statements.
- For Sql Mobile I used an updatable SqlCeResultSet and SqlCeUpdatableRecord.
The Tests
For each SELECT test, a command was created, ExecuteReader() was called with no parameters, and the Read() method of the DataReader was called until no more rows were returned. Inside the read loop, a single column of the row was read.
In addition, I performed the test three times. First in main memory (M), then in safestore (S) memory, and then finally on a 1gb SD card (C).
| BULK INSERT 132711 rows (milliseconds) |
| SQLite (M) |
|
210789 |
| SqlMobile (M) |
|
272550 |
| SQLite (S) |
|
346435 |
| SqlMobile (S) |
|
346442 |
| SQLite (C) |
|
256261 |
| SqlMobile (C) |
|
282686 |
| 0 |
Scale |
347000 |
| Database size (kb) |
| SQLite |
|
6110 |
| Sql Mobile |
|
7760 |
| 0 |
Scale |
7800 |
Join Test #1 (milliseconds)
SELECT * FROM Invoice A
INNER JOIN Shipment B
ON A.PKeyInvoice = B.InvoiceKey |
| SQLite (M) |
|
4463 |
| SqlMobile (M) |
|
12992 |
| SQLite (S) |
|
6344 |
| SqlMobile (S) |
|
27532 |
| SQLite (C) |
|
7129 |
| SqlMobile (C) |
|
13652 |
| 0 |
Scale |
27600 |
Join Test #2 (milliseconds)
SELECT B.PKeyShipment, SUM(C.Amount)
FROM Invoice A
INNER JOIN Shipment B ON A.PKeyInvoice = B.InvoiceKey
INNER JOIN Charges C ON B.PKeyShipment = C.ShipmentKey
GROUP BY B.PKeyShipment
ORDER BY B.PKeyShipment
|
| SQLite (M) |
|
10243 |
| SqlMobile (M) |
|
142953 |
| SQLite (S) |
|
25149 |
| SqlMobile (S) |
|
209831 |
| SQLite (C) |
|
17073 |
| SqlMobile (C) |
|
129704 |
| 0 |
Scale |
210000 |
INSERT 1 Row Test (implicit transaction) (milliseconds)
INSERT INTO Invoice
(PKeyInvoice, InvoiceNum, InvoiceDate, NetCharge, ChargeCurrency)
SELECT MAX(PKeyInvoice) + 1, '123456789', '2005-01-01', 100.5, 'USD'
FROM Invoice |
| SQLite (M) |
|
14 |
| SqlMobile (M) |
|
85 |
| SQLite (S) |
|
225 |
| SqlMobile (S) |
|
201 |
| SQLite (C) |
|
304 |
| SqlMobile (C) |
|
128 |
| 0 |
Scale |
310 |
Scan Test #1 (milliseconds)
SELECT A.TrackingId, B.Weight
FROM Shipment A
INNER JOIN Packages B ON A.PKeyShipment = B.ShipmentKey
WHERE B.Weight > 10 |
| SQLite (M) |
|
2196 |
| SqlMobile (M) |
|
6725 |
| SQLite (S) |
|
8410 |
| SqlMobile (S) |
|
7925 |
| SQLite (C) |
|
5622 |
| SqlMobile (C) |
|
10172 |
| 0 |
Scale |
10200 |
Scan Test #2 (milliseconds)
SELECT A.InvoiceNum, B.TrackingId
FROM Invoice A
INNER JOIN Shipment B ON A.PKeyInvoice = B.InvoiceKey
WHERE B.TrackingId LIKE '%55%' |
| SQLite (M) |
|
1123 |
| SqlMobile (M) |
|
7887 |
| SQLite (S) |
|
3306 |
| SqlMobile (S) |
|
8791 |
| SQLite (C) |
|
3294 |
| SqlMobile (C) |
|
16102 |
| 0 |
Scale |
16200 |
Scan Test #3 (milliseconds)
SELECT InvoiceNum
FROM Invoice
WHERE PKeyInvoice IN (SELECT InvoiceKey FROM Shipment WHERE NetCharge > 100) |
| SQLite (M) |
|
419 |
| SqlMobile (M) |
|
2630 |
| SQLite (S) |
|
2261 |
| SqlMobile (S) |
|
3881 |
| SQLite (C) |
|
2850 |
| SqlMobile (C) |
|
11886 |
| 0 |
Scale |
12000 |