Binary storage

  • Thread starter Thread starter Peter Morris
  • Start date Start date
P

Peter Morris

Hi all

I am currently using SQLite as my database, which I found to be much faster
than SQL CE, however I still require faster binary storage.

My database consists only of a single table

create table Instances (
Oid INT Primary Key,
InstanceType Text,
InstanceData BLOB
);
create index InstancesInstanceTypeIndex on Instances(InstanceType);

I serialise / derserialise my objects myself and store my business object ID
(int) in Oid, the Type.FullName in InstanceType, and the object's serialised
data in InstanceData. My requirements are

1: Add a new record
2: Update a record by OID
3: Get a record by OID
4: Get a list of OID with a specific class name "InstanceType"
5: Get the highest OID in the database

I might need to remove a record in the future, but at the moment I don't
need to. The InstanceData consists of variable sized data.

I am wondering:
A: Is there an embedded DB for CF that is faster than SQLite?
B: Is there a non-DB way (single file) that I could achieve this which would
be faster?


Any help would be appreciated. My XML->DB import routine is taking an hour
and this is far too long.

Pete
 
Peter,

When I looked at SQLite a couple of years ago, it was faster than SQL Mobile
for some things and slower for others. Have you tested blob storage
performance with SQL Server Compact Edition?
 
When I looked at SQLite a couple of years ago, it was faster than SQL
Mobile for some things and slower for others. Have you tested blob storage
performance with SQL Server Compact Edition?
Click to expand...

No, I'll give it a try. Any other ideas? Considering my requirements are
so simple I was hoping there would be a non-SQL approach that would be
lightening fast.


Pete
 
Pete,

I doubt anything will be lightning fast on a mobile device. What does the
blob start as? Would storing each blob to a file be an option?
 
Seems really simple to me. Are the blobs fixed size? If so, you could just
store them in a fixed field binary file. If you need lookup speed create an
index for the OIDs and you're set. The idex is simple enough it could be at
the start of the binary file, so you'd still have just one file.
 
Seems really simple to me. Are the blobs fixed size? If so, you could
just store them in a fixed field binary file. If you need lookup speed
create an index for the OIDs and you're set. The idex is simple enough it
could be at the start of the binary file, so you'd still have just one
file.
Click to expand...

No, the blobs are serialised information for my classes, so they vary quite
a lot.
 
Would storing each blob to a file be an option?

I need transactional support, I need all or no changes to be applied when I
update.
 
Peter,

Then I hope the performance for SSCE will be adequate for you. Please let me
know how it works out for you after you have a chance to try it. Also I'd be
curious to know how much time is spent serializing and how much in
storing/retrieving - probably you're looking at that already.
 
curious to know how much time is spent serializing and how much in
storing/retrieving - probably you're looking at that already.
Click to expand...

I ran a desktop version of the framework through Ants compiler and about 99%
of the "UpdateDatabase" code is spent in SqlLite. I originally dropped SQL
CE because I found SQLite was at least twice as fast, but back then I wasn't
using BLOB columns I was mapping each property to its own native column.


Pete
 
Peter,

As well as the column type making a difference, the desktop vs. CF could
make a differnece in performance characteristics too. Anyway, please le me
know how it goes...
 
Well it could still be done, but generating a database with variable length
fields makes it a lot more difficult. Having written a relational database
before I can say that if you only want these features (a couple fixed fields
and a simple index on the DWORD), then it's not too painful - maybe a few
weeks of work. But you'd not have any flexibility, so altering the schema
would require fixing the engine. Writing it so it's dynamic is probably
several months of work, and adding transactional support would be on top of
that.

Basically, for what you want (especailly ACID transactions) you must go with
a real, existing DBMS unless you have a lot of free time to create another.
 
Back
Top