[EMAIL PROTECTED] wrote:
"Paul Harris" <[EMAIL PROTECTED]> wrote:
A thought along the same lines, can sqlite create a unique index that
is hash-based? this would provide the UNIQUE support, but it wouldn't
provide a sorted index.
That should resolve the massive-insert-too-slow problem, and
afterwards he can create a sorted index on the column if he needs
ordered lookups.
SQLite *could* create a hash index. But I seriously doubt
that would do anything to solve this problem. In fact, it
would probably make the problem worse. The problem arises
from the lose of cache locality. The size of the database
has grown to the point where the disk cache can no longer
hold the entire database file. Each insert must check and
update random pages within the database file. Because the
cache cannot hold the whole database, accessing the pages
that must be checked and updated involves real disk I/O
rather than just a copy of a page out of cache memory.
Doing real disk I/O takes much, much longer than copying
memory out of cache.
The way to fix this problem is to improve the locality of
reference and thus get the cache working better. This can
be done using B-Trees. I just haven't found the time to do
it yet. But because hashing is pseudo-random, hashing actually
makes locality or reference worse and makes the problem much
harder to solve.
--
D. Richard Hipp <[EMAIL PROTECTED]>
I haven't looked at how Sqlite manages its cache, so my suggestion may
be irrelevant but we have had success in such a cacheing situation by
crafting the cache with pages organized in most recently used order.
The least recently used pages is the one replaced when there is no cache
hit. When a page is accessed it is linked to the most recently used
position.
B-Tree interior nodes stay in cache.
Another improvement was achieved by implementing enhanced B-Trees which
try to merge adjacent nodes before splitting or deleting a node. This
keeps the trees flatter and less checkerboarded and cuts back on
expensive splits when inserting randomly ordered keys.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
