On 2/6/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
--- Shane Harrelson <[EMAIL PROTECTED]> wrote:
> On 2/5/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> > > Yes, it's typical. Each database instance is typically composed of
> > > around 50k records, all inserted in a single pass. If I could do
> > > larger transactions (or not do them at all) I would, for if I
> > > encounter an error I have to discard all records - my application is
> > > extremely non-fault tolerant.
> > >
> > > Is there anyway to disable journaling completely? I'm not certain
> > > that for my application it gains me anything.
> >
> > 50k records is nothing. Just sort it entirely in memory outside of the
> > database and blast in the results via just plain inserts in the two tables
> > in sorted order so sqlite will always perform a table append and not
> > have to shuffle the pages around. It will be significantly faster if you
> > handle the conflicts yourself in your code in memory using STL or a
> > conventional data structure than using a general purpose database such
> > as SQLite.
> >
> >
>
> I'm working in an embedded environment, I have a total of 64mb of RAM
> for everything - O/S, file system, application usage, etc., and my
> table structure is more complicated (and larger) than the examples
> I've given. We've considered rolling our on data storage format, but
> wanted to explore the flexibility and ease of use of SQLite. So far,
> it's been a star. Kudos to DRH, et.al. on such a nice product.
Fair enough. I wasn't aware of your constraints and how much time
you wanted to dedicate to optimization. I thought you were looking for
a 10X speedup as opposed to a 1.3X speedup.
In any event, pre-sorting data in primary key order prior to insert
is always a big win in SQLite. You might consider doing that, even if
in small batches of rows (a thousand at a time). I generally get a 2X
speedup with this simple trick.
We've declared an INTEGER column as the PRIMARY KEY on all tables as below:
CREATE TABLE Strings ( StringId INTEGER PRIMARY KEY, Value VARCHAR(30) UNIQUE )
It is my understanding that this makes it an alias to the "internal"
ROWID used by SQLite. On insertions we don't specifiy the key,
allowing SQLite to generate this for us
(http://www.sqlite.org/faq.html#q1).
Would there be any benefit to "pre-sorting" in this scenario?
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
