Request For Opinion (data file performance).

I'm writing a program at home for general public which store its data in a
datafile.

Fearing the data loading could be long, I used an embeded datafile engine
(SQLite), because this way I could read/write/delete file incrementally
(unlike what I could do with tar-gzipped XML file for example).

Now I'm hitting a problem:
When I create a new (empty) file, the (embeded) 'database engine' tooks
about 7 seconds to create a new database file with about 10 tables & 30
indexes & trigger.
That's wayyyy too long.

So here is what I'm considering and would appreciate input on:
- maybe try the firefox embeded version to see if it's faster
- maybe used a plain tar-gziped XML serialized file
- maybe used a plain tar-gzipped-serialized data file
- use an other (free or cheap and royalty free) embeded database engine

my concerns are the following:
- if I use serialization I want a given version of the application to be
able to read file created by previous and later version, which comdemn to
XML serialization, I believe
- If I use serialization I am concern about long startup if I have a lot of
data, and heavy memory usage.
- If I choose a DB, I don't want MSDE.. I already would have a 20MB
dependency on .NET 2.0, I find it overkill to bloat it even more with: 20
more MB, a server based product, where as I would like a zero-config, zero
install concerns, embeded DB engine as a simple DLL.
- I though of Firebird, will table & file creatin be fast?

Well in fact I'm a bit in the dark.. but I hope you guys see my problem and
if any of you has experience on similar topic I will appreciate feedback...

Phill
Wed Aug 31 07:22:40 CDT 2005

"Lloyd Dupont" <ld@NewsAccount.galador.net> wrote in message
news:uMzPfehrFHA.3604@tk2msftngp13.phx.gbl...
> When I create a new (empty) file, the (embeded) 'database engine'
> tooks about 7 seconds to create a new database file with about 10
> tables & 30 indexes & trigger.
> That's wayyyy too long.

10 Tables, 30 Indeces and/or Triggers, and all in 7 seconds?
That's not /exactly/ shabby ...

At what point to you create this "empty" file?
If it's when the program is started for the very first time, how about
shoving the creation out onto a background thread? Your users will
still looking at a pretty splash screen or trying to find the "Help" menu
while your "datafile engine" is happily thrashing the data around.

How /big/ is the file?
Could you hold an "empty" one within the program itself, say, in
a resource file or another DLL and just blast that out into the file
system as and when required?

HTH,
Phill W.

Lloyd
Wed Aug 31 08:07:58 CDT 2005

Hi Phil!

I was coming to the same conclusion indeed.
In fact there are 2 costly operation:
- open the very 1st SQLite connection (to any database) of the application
(~2 seconds): I think I will use something similar to the splash screen, the
"Initializing" status in the status bar.
- create a new file (~7 seconds): I think I will save a new file template
somewhere ;-)

--
On a toujours tort d'essayer d'avoir raison devant des gens qui ont
toutes les bonnes raisons de croire qu'ils n'ont pas tort !
"Phill. W" <P.A.Ward@o-p-e-n-.-a-c-.-u-k> wrote in message
news:eBN1vairFHA.248@TK2MSFTNGP14.phx.gbl...
> "Lloyd Dupont" <ld@NewsAccount.galador.net> wrote in message
> news:uMzPfehrFHA.3604@tk2msftngp13.phx.gbl...
>> When I create a new (empty) file, the (embeded) 'database engine'
>> tooks about 7 seconds to create a new database file with about 10
>> tables & 30 indexes & trigger.
>> That's wayyyy too long.
>
> 10 Tables, 30 Indeces and/or Triggers, and all in 7 seconds?
> That's not /exactly/ shabby ...
>
> At what point to you create this "empty" file?
> If it's when the program is started for the very first time, how about
> shoving the creation out onto a background thread? Your users will
> still looking at a pretty splash screen or trying to find the "Help" menu
> while your "datafile engine" is happily thrashing the data around.
>
> How /big/ is the file?
> Could you hold an "empty" one within the program itself, say, in
> a resource file or another DLL and just blast that out into the file
> system as and when required?
>
> HTH,
> Phill W.
>
>