This may or may not help, depending upon your schema. We find in
general that insertions and deletions are the major overhead in index
maintenance and there is a point where it is cheaper to drop the indices
you are not using and rebuild them rather than to involve the high
overhead of repeated B-Tree rebalancing etc. The drop and rebuild has
the added benefit of ending up with an optimally organized index free
from fragmentation.
The cost of insertions and deletions in an index is not linear with
index size.
I haven't looked at the Sqlite B-Tree algorithms, so this I can only
suggest this as an experiment. If Sqlite uses some form of B-Tree
optimization, the overhead of insertions and deletions is greater and
the drop and rebuild more likely to be an improvement.
JS
Allan, Mark wrote:
We are able to compile our application for both target and host. When compiled
for host the application runs on Win32 and will create/read/write to a database
file on the host PC.
The performance of the deletions on Win32 will not take the 11 minutes I specified, this is only a problem for our target. The same operation on the PC will take only 3-4 seconds. Please note that since my first email we have tried increasing the page size of SQLite and have increased the page size from 1024 bytes to 8192 bytes. This has decreased the time to process the same delete operation from 11 minutes to 3.75 minutes on our target hardware. Both host and target versions of the software use the same configuration a page size of 8192 bytes and a cache of 75 pages = 600k.
The only real differences are 1) the hardware, 2) the filing system. We would
expect a difference in performance as the PC is much faster than our target
hardware and the write speed to NOR flash is comparatively slow but not as drastic
a drop-off as we see. 3-4 seconds -> 3-4 minutes.
It may be useful for us to know what SQLite is doing during deletion so that we
can try and optimise our code and/or any configuration of SQLite, our
filesystem code or the hardware to try and get this figure down. Can anyone
give me a reasonably detailed description of what is happening during delete.
The documentation on the website has not helped us diagnose where our problem
lies.
Best Regards
Mark
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 18 October 2005 19:06
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Very Slow delete times on larger databases, please
help!
"Allan, Mark" <[EMAIL PROTECTED]> wrote:
Have you been able to investigate this yet?
I have investigated and I found nothing wrong. I am unable
to reproduce the problem.
--
D. Richard Hipp <[EMAIL PROTECTED]>
DISCLAIMER:
This information and any attachments contained in this email message is
intended only for the use of the individual or entity to which it is addressed
and may contain information that is privileged, confidential, and exempt from
disclosure under applicable law. If the reader of this message is not the
intended recipient, or the employee or agent responsible for delivering the
message to the intended recipient, you are hereby notified that any
dissemination, distribution, forwarding, or copying of this communication is
strictly prohibited. If you have received this communication in error, please
notify the sender immediately by return email, and delete the original message
immediately.
