Database Alternative

[bumerang]

Hello All,

We are developing .NET CF & DESKTOP interface for SQLITE database
(www.sqlite.org) Its in a beta state and I want to inform you some test
results about it. You can use it with VB. NET or C# for CF or DESKTOP. You
can easily transfer SQLITE database device to desktop or else, and you can
create/fill/use your db on desktop and then just copy to device for use.

Test Device : IPAQ 2210,

Library returns query results as DataTable objects so, you can use it
directly with databound objects likes dbgrid etc.

Record Length (100 byte, 1 integer primary key)

Insert 10,000 record takes 17 second
Select Query for 10,000 record takes 8 second

Insert 10,000 record to MMC card takes 24 second
Select Query for 10,000 record from MMC card takes 10 second

Regards.

PocketSYNC Team

 

[ralvaradot]

Hi bumerang

Examples? How can install SQLite in PPC? its special way?
How is ConnectionString, in PPC & Desktop?

Cheers,

RA

 

[bumerang]

Hello,

We will publish demo version at next week, I will send web link to group.

Thanks.

 

[Jamie Macleod]

What kind of size are we talking about? What kind of space does it take on
the device?

 

[bumerang]

Just 220 Kb !

What kind of size are we talking about? What kind of space does it take on
the device?

 

[Jamie Macleod]

Nice!

Just 220 Kb !

 

[Carl Rosenberger]

We are developing .NET CF & DESKTOP interface for SQLITE database
(www.sqlite.org) Its in a beta state and I want to inform you some test
results about it. You can use it with VB. NET or C# for CF or DESKTOP. You
can easily transfer SQLITE database device to desktop or else, and you can
create/fill/use your db on desktop and then just copy to device for use.

Test Device : IPAQ 2210,

Library returns query results as DataTable objects so, you can use it
directly with databound objects likes dbgrid etc.

Record Length (100 byte, 1 integer primary key)

Insert 10,000 record takes 17 second
Select Query for 10,000 record takes 8 second

Insert 10,000 record to MMC card takes 24 second
Select Query for 10,000 record from MMC card takes 10 second

Hi bumerang,

I take your postings as a challenge to post the results of our object
database engine for a similar task.

We store objects, so I can't provide a result for an integer on it's
own. We should run another benchmark with something more complex with
some inheritance hierarchies.

However our figures for the simplest equivalent possible...

public class SimpleInt
{
public int myInt;
}

....look pretty good to me. Our indexing system clearly beats yours:


db4o version: db4o 3.0 B71 (alpha version)
Test Device: iPAQ 5450

(1) Indexed
------------
Inserting 10,000 objects takes 171 seconds.
A query for one indexed object in 10,000 objects takes 1.1 seconds.
The indexed database file has a size of 470kB.

(2) No Index
------------
Inserting 10,000 objects takes 128 seconds.
A query (scan) for one object in 10,000 objects takes 37 seconds.
The database file has a size of 390kB.


db4o 3.0 with indexed fields will be available for public BETA testing
by the end of this month. The release is planned for the end of May.

Of course I can provide test case and engine for the above results today,
if anyone would like to check my results.


I am positive that we will provide the fastest query processor available
for the CompactFramework and I am looking forward to further races with you.


Kind regards,
Carl

 

[bumerang]

Hi Carl,

We are still faster than you. Searching single record in 10,000 record takes
<1 sec from none indexed table (I did not meausure yet but I know it just
takes several hundred milliseconds).

Regards.

 

[Carl Rosenberger]

We are still faster than you. Searching single record in 10,000 record takes
<1 sec from none indexed table (I did not meausure yet but I know it just
takes several hundred milliseconds).

Hi bumerang,

judging from your first posting, the query takes 8 seconds with
your engine.

Why don't you simply prove your statement by providing a simple
..NET App to support your claims?

Here is mine:
http://www.db4o.com/db4o/race10000.zip

The above Zip File (127 kB in size) contains the current development
build of our object database engine and a small Windows forms
application that tests and documents query execution speed.

The Zip is supplied as a VS.NET 2003 project along with the source
code of the application. You may need to reset the reference to
db4o.dll since VS.NET projects loose references if a project is
moved (I suppose this is a bug.).

(The db4o 3.0 build can not be used for productive use. It's our
current development build and it's not stable yet. The first 3.0
beta versions are scheduled for the end of this month. For
productive projects, please use db4o 2.8.)

I would appreciate if you could produce something similar, bumerang.
Feel free to use the sources of my small test application.

Kind regards,
Carl

 

[Jamie Macleod]

What is the size of your database engine on the PocketPC device?

 

[Jamie Macleod]

Sorry. This was meant for Carl.

What is the size of your database engine on the PocketPC device?

 

[Carl Rosenberger]

Sorry. This was meant for Carl.

The released db4o 2.8 DLL for CompactFramework has a size of 225kB.

db4o 3.0 will have a size of about 270kB.


For special usecases we offer to remove unneeded features from our
engine. Depending on what you need, it is possible to get close to
or below 100kB.

We also offer source code licensing, if you want to tune our engine
yourself.


Kind regards,
Carl

 

[Boris Nienke]

Hi Carl,

We are still faster than you. Searching single record in 10,000 record takes
<1 sec from none indexed table (I did not meausure yet but I know it just
takes several hundred milliseconds).

Do you mean:
- running an application on the PocketPC (CF)
- having a database on the PocketPC
- Doing a "Select * From MyTable Where MyID = 8324" will take less than a
second if there are 10000 records in "MyTable"?

- Inserting (Insert Into MyTable (MyID) Values MyID) ) will take about 17
Seconds for 10000 records ... running ON THE PocketPC?

If so, i'm very interested in this test-case:

Create Table MyTable
Word1 varchar,
Word2 varchar;

Create Index x1 on MyTable(Word1);
Create Index x2 on MyTable(Word2);

Now insert 90.000 records (a dictionary word-list) to it. Words are between
one char ('a') and >40 Chars (if there are more alternatives like 'dealer,
supplier, furnisher, purveyor, victualer, ' etc.)

Questions:
1.) how long will it take to insert those records running on a PocketPC?

2.) how long will it take to get all records starting with 'cat' (select...
like 'cat%')?

3.) how long will it take to do a fulltext search (...like '%cat%')?

4.) what do i need to install the database on the PocketPC? Just copying
one DLL?

5.) Are there parametical queries?
"select .. where Word1 like :MyWord"
Parameter("MyWord") = "%cat%";

6.) what would be the size of the resulting database?

7.) what would be the price of this database? What do i have to pay for it?
Pay once use many?

I have this testcase for the db4o-database (which is great BTW because you
simply can work with your objects - no need to transfer the properties to
SQL-Statements etc.) and i would be glad to run the same test against your
SQL-Database. Could you send me a test-version with a little "how to start"
document?

Boris

 

[bumerang]

Hi Carl,

Read that again, querying and filling 10,000 records to datatable object
takes 8 seconds !!

 

[Christian Schwarz]

We are developing .NET CF & DESKTOP interface for SQLITE database

(www.sqlite.org) Its in a beta state and I want to inform you some test
results about it. You can use it with VB. NET or C# for CF or DESKTOP. You
can easily transfer SQLITE database device to desktop or else, and you can
create/fill/use your db on desktop and then just copy to device for use.

Very interesting ! Some weeks ago I compiled SQLite for Windows CE
successfully but quickly ran into big trouble getting the Mono ADO.NET
components working for Compact Framework. IIRC, they were using an ugly
callback to an unmanaged SQLite function ...

Greetings, Christian

 

[Carl Rosenberger]

Read that again, querying and filling 10,000 records to datatable object
takes 8 seconds !!

bumerang,

I don't think it's a very useful database task on a handheld to
load 10000 ints in 8 seconds. By the way, db4o can store and
load 10000 ints in less than half the time. [1]

What would be your timed result to *find* a record with a key.
The typical handheld usecase:
"Given some user entry, find corresponding data."

People don't like to wait 8 seconds for a table scan.

Can you please post your result for a lookup on an indexed field
and a small application to prove it?

Thanks.

Kind regards,
Carl
--
Carl Rosenberger
db4o - database for objects - http://www.db4o.com

[1] Here is my result:

Time to store 10000 ints: 3137ms
Time to load 10000 ints: 3554ms

And here is the source code for this test:

public class IntArray {
public int[] arr;
}

int count = 10000;
IntArray ia = new IntArray();
ia.arr = new int[count];
for(int i = 0; i < count; i++){
ia.arr = i;
}
ObjectContainer objectContainer = Db4o.openFile("intarr.yap");
long start = Environment.TickCount;
objectContainer.set(ia);
long stop = Environment.TickCount;
objectContainer.close();
long elapsed = stop - start;
WriteLine("Time to store " + count + " ints: " + elapsed + "ms");
objectContainer = Db4o.openFile("intarr.yap");
start = Environment.TickCount;
ObjectSet objectSet = objectContainer.get(new IntArray());
ia = (IntArray)objectSet.next();
stop = Environment.TickCount;
objectContainer.close();
elapsed = stop - start;
WriteLine("Time to load " + count + " ints: " + elapsed + "ms");

 

[bumerang]

First of all I dont talk about integer values (record length = 100 bytes,
with autoincrement key) , with database applicaton we need frequently
browser based interface so we still thinking to filling grid with datatable
object is useful way, we will post sample application and basic
documentation in a week. Meanwhile, we are talking about very very different
things, our product can works with databound objects, datatables and use
ANSI92 SQL standarts. Here is the some samples with VB.NET CF.

Dim db As New sqlite
Dim tb As New DataTable
Dim sqlcmd As New sqlite.SqlCommand
Dim retval as Integer
Dim i as Integer

db.OpenDB("\program files\sample\test.db")

' you can see your query result with datagrid

sqlcmd.CommandText = "select * from orders where id=? and price=?"
sqlcmd.SetParameter(1, "45")
sqlcmd.SetParameter(2, "5600")

tb = sqlcmd.ExecuteQuery()
dbgrid1.DataSource = tb

' or you can work directly with datatable

Dim myRow As DataRow
Dim myCol As DataColumn

For Each myRow In tb.Rows
For Each myCol In tb.Columns
MessageBox.ShowMessage(myRow(myCol))
Next myCol
Next myRow

' get record count of table

sqlcmd.CommandText = "select count(*) sayac from orders"
retval = sqlcmd.ExecuteScalar()

' insert some records

db.ExecuteNonQuerySQL("begin transaction")

For i = 1 To 10000
db.ExecuteNonQuerySQL("insert into stock (name,price)
values('ipaq','350.0')")
Next

db.ExecuteNonQuerySQL("commit transaction")


db.CloseDB()

Read that again, querying and filling 10,000 records to datatable object
takes 8 seconds !!

bumerang,

I don't think it's a very useful database task on a handheld to
load 10000 ints in 8 seconds. By the way, db4o can store and
load 10000 ints in less than half the time. [1]

What would be your timed result to *find* a record with a key.
The typical handheld usecase:
"Given some user entry, find corresponding data."

People don't like to wait 8 seconds for a table scan.

Can you please post your result for a lookup on an indexed field
and a small application to prove it?

Thanks.

Kind regards,
Carl
--
Carl Rosenberger
db4o - database for objects - http://www.db4o.com

[1] Here is my result:

Time to store 10000 ints: 3137ms
Time to load 10000 ints: 3554ms

And here is the source code for this test:

public class IntArray {
public int[] arr;
}

int count = 10000;
IntArray ia = new IntArray();
ia.arr = new int[count];
for(int i = 0; i < count; i++){
ia.arr = i;
}
ObjectContainer objectContainer = Db4o.openFile("intarr.yap");
long start = Environment.TickCount;
objectContainer.set(ia);
long stop = Environment.TickCount;
objectContainer.close();
long elapsed = stop - start;
WriteLine("Time to store " + count + " ints: " + elapsed + "ms");
objectContainer = Db4o.openFile("intarr.yap");
start = Environment.TickCount;
ObjectSet objectSet = objectContainer.get(new IntArray());
ia = (IntArray)objectSet.next();
stop = Environment.TickCount;
objectContainer.close();
elapsed = stop - start;
WriteLine("Time to load " + count + " ints: " + elapsed + "ms");

 

[Carl Rosenberger]

Meanwhile, we are talking about very very different
things, our product can works with databound objects, datatables and use
ANSI92 SQL standarts.

I wonder why you SQL guys always think that SQL is an advantage.

It's a hassle for the developer to construct SQL strings and it
consumes a lot of time for the database engine to parse strings.

If you want a DataGrid with a DataBinding, it's very easy for me
to provide an equivalent example. Databindings understand IList
and we supply a wrapper of our ObjectSet to IList along with our
download (see the com.db4o.wrap folder).

Working with objects only is so much more comfortable. See for yourself.
The following example stores two new Person objects and displays them
with their properties FirstName and LastName in a Datagrid control:

ObjectContainer objectContainer = Db4o.openFile("datagrid.yap");
Person person = new Person("Carl", "Rosenberger");
objectContainer.set(person);
person = new Person("bumer", "ang");
objectContainer.set(person);
objectContainer.commit();
Query q = objectContainer.query();
q.constrain(typeof(Person));
dataGrid1.DataSource= new ObjectSetCollection(objectContainer, q.execute());

// To make this a runnable example, you need a person class with some
// properties.

public class Person {
private string firstName;
private string lastName;

public Person(){
}

public Person(String firstName, String lastName){
this.firstName = firstName;
this.lastName = lastName;
}

public string FirstName{
get{
return firstName;
}
set{
firstName = value;
}
}

public string LastName{
get{
return lastName;
}
set{
lastName = value;
}
}
}

Kind regards,
Carl

 

[Jamie Macleod]

Carl,

Looks like a good product, but I wouldn't use it because I don't like your
licensing.

Jamie

 

[Ginny Caughey [MVP]]

Hi Jamie,

I don't know what your timeframe is for deployment, but you should keep an
eye on SqlServerCE 3, which will be out next year some time along with the
next version of VS. It will be faster than the current version and will also
allow you do create the database on the desktop machine if you need to
interface with MySql or something like that as well as supporting
Merge/replication and RDA with SQLServer like the current version. I'll be
able to tell you more details after I see it at the MDC in a couple of
weeks.