In my case Delete happens reasonably OK but Vaccuuming takes incredibly long?
On 10/21/05, Allan, Mark <[EMAIL PROTECTED]> wrote: > > > Thanks to both Christian Smith and John Stanton for your posts. > > > On Wed, 19 Oct 2005, Christian Smith wrote: > > From the VDBE output you originally posted, you are doing a > > fair amount of > > work for each deleted row: > > - Index search to find the next row from EXAMINATIONS to delete > > - Removing the row from 3 indexes on EXAMINATIONS > > - (trigger) Remove related row in SPIRO_TEST from 2 indexes > > on SPIRO_TEST > > - (trigger) Remove related row in SPIRO_TEST > > - Remove the row from EXAMINATIONS > > > > Check your cache size. If the above work is causing the 75 page entry > > cache to thrash, you're likely to hit worst case performance as the > > thrashing pages may be being accessed in a cyclical fashion. > > Not sure how > > like it is that your page cache is not big enough. How big is a row of > > data, typically? > > Our cache size is 75 pages of 8192 bytes = 600Kb. > > The maximum size of an EXAMINATIONS record is about 500 bytes, 450 bytes > of this is a varchar field. In the test example the average size of an > EXAMINATIONS record is 60 bytes as not much text is saved. > > The maximum size of a SPIRO_TEST record is about 5Kb, these will vary from > test to test, but for the test example the size of each SPIRO_TEST record is > fixed to approx 1Kb. > > Based on this I dont think that we should be thrashing the cache. I am > however unsure how SQlite works here. > > > Also, a 60x slowdown is not to be unexpected. The PC version, while > > probably having the same SQLite page cache size in the SQLite > > app itself, > > will most likely be reading and writing to the OSes cache at memory to > > memory copy speed most of the time, with synchronous writes > > only done when > > needed. The embedded platform you're using probably writes straight to > > FLASH, which is necassarily a synchronous operation if your OS doesn't > > have a cache between your app and the FLASH FS. While flash > > writes are low > > latency, they are also low bandwidth, and won't be within an order of > > magnitude of performance when compared to a desktop PC write to OS > > filesystem cache. > > > > Finally, you give no indication on the actual CPU speed of > > the embedded > > platform. It's quite reasonable to assume a development PC could be an > > order of magnitude faster on sheer integer throughput. I'm > > amazed how slow > > my 50MHz microSPARC based SPARCclassic is. Such a platform > > would not be > > much, if at all, slower than a modern embedded platform, and has the > > benefit of gobs of RAM, but still runs the same code two orders of > > magnitude slower at least than my Athlon XP 1700 based > > desktop. You have > > to keep your performance expectations realistic. You are, afterall, > > running a complete, ACID transaction, SQL relational database. > > > The maximum CPU speed of our ARM7 chip is 71Mhz. > > > Others have indicated that dropping indexes might help when > > deleting or > > inserting records. However, have you tried simply not having > > indexes at > > all? Would that cause unacceptable slowdown? Perhaps, for the > > demo query > > from the original post, just keep the DATE index on > > EXAMINATIONS, and use > > full table scans for queries based on EXAM_TYPE and > > STATUS_FLAG. Truth is, > > given the small number of EXAM_TYPE and STATUS_FLAG values (I > > presume), > > you're as well just doing table scans when looking for > > specific exam types > > and statuses. Indexes only really help when you have a large > > variation in > > values with few collisions. Doing this will leave a single > > index update in > > addition to the actual row removals, which should improve performance. > > I have tried permanently dropping the indexes on EXAM_TYPE and STATUS_FLAG > and this gives some improvement in time. Indeed it does seem that the > STATUS_FLAG index is worthless and in the initial version of the software we > will have only 1 EXAM_TYPE (although this will increase for each module we > release over the next few months). > > I have also tried the suggested method of dropping the > EXAM_PATIENT_ID_INDEX index on the examinations table before delete and > rebuilding it on completion. I cannot delete the remaining indexes as they > are used during the delete operation and this slows the whole operation > down. > > The latest changes have reduced the time to delete the same number of > records from 3:45 minutes to 2:53 minutes. Still a long time to wait but any > time saving is welcome, especially as the test is for a 50% full scenario so > at 99% we can expect it to take 6 minutes. > > Thanks again for your help. > > If there are any other ideas on how we can optimise this further then > please let me know. > > > Mark > > > 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. > >
