Raymond,
So far on this list I have only seen a reports of scalability problems
with sqlite when the real problems were specific queries being given to
sqlite that were constructed in a way that sqlite does not process well.
In every such case I can recall an alternative form of the query was able
to be produced (usually by DRH personally) that did not exhibit the
problem. Knowing the code, there is no reason for sqlite to be scaling
linearly in queries unless it is being forced to do table scans.
It is true that sqlite doesn't optimise queries as well as major
databases. It's not designed to. Users must take some care in constructing
their queries and ensuring the queries are suited to sqlite's design if
performance might be a problem. At the same time, such queries usually
perform much better than those of major databases because of the vastly
reduced optimisation and connection overhead that sqlite affords.
As with every database technology. If you care about performance you have
to understand some things about the design of your underlying technology.
For sqlite the design is simple and the experts are extremely responsive.
If you're having problems and can provide a clear, specifc description of
your problem you will get help. While queries such as "I think sqlite
scales linearly, but I can't tell you want queries I'm issuing to make
that happen" and "My scroll list seems slow, but I don't know what is
happening between the GUI and the database or what queries are going on"
are unlikely to solicit helpful response, "I am issuing this query on that
database schema with about 100 thousand rows each carrying 2k of data" is
likely to be something members of this list can help you solve.
I'd like to take this soapbox opportunity to again thank drh and his
associates for the wonderful work they put into sqlite and their genine
personal commitment to this project. Sqlite is a great product, and a
well-targeted one.
Benjamin
Raymond Irving <[EMAIL PROTECTED]>
18/06/2004 12:09 AM
To: [EMAIL PROTECTED]
cc:
Subject: Re: [sqlite] SQLite performance with mid-size databases
Well this does not sound good at all. I would think
that SQLite would at least do a better job at queries.
The issues with performance and scalability is of
great concern. I was planning on create some new apps
that use SQLite, but now I'm wondering is this little
database is capable of handle over 2 Gigabytes (GB) of
data even though the docs says it can handle up to 2
Terabytes (TB).
Does it really make sense to cut back on performance
in order to keep the library size small?
Will SQLite 3.0 fix these problems?
__
Raymond Irving
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> Richard Kuo wrote:
> >
> > I suspect some unnecessary disk access has to be
> the problem...despite
> > the small amount of new guide information being
> queried out, disk bytes
> > read is several times higher than with MS access
> and scrolling back over
> > previously accessed areas of data is visibly
> faster...indicating that
> > the disk cache is very favorably impacting the
> speed of the queries.
> >
>
> If each of your rows contains 2K of data, that means
> each database entry
> is using about 2 overflow pages. You can change
> this by increasing the
> page size. Try recompiling SQLite after changing
> the SQLITE_PAGE_SIZE
> macro to 16384.
>
> You might also trying switching to SQLite version
> 3.0.0 which will be
> released tomorrow.
>
> Also tomorrow, I will be making available a database
> analysis tool
> for version 2.8 databases that will help us to
> better understand
> how information is stored on disk for your database,
> and possibly
> provide some clues about why you are having
> problems.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
