I hear you. If nothing else works then I will have to switch to using Oracle. it would require a substancial code rewrite though. sigh.
Thanks for the adice. Andrew Finkenstadt wrote: > > On 6/19/07, pompomJuice <[EMAIL PROTECTED]> wrote: >> >> >> Running a huge Oracle 10G database >> Running a 3rd party application that generates HUGE IO. >> Part of this 3rd party application is my application that does lookups. >> >> 1.) Data comes in in the form of files. >> 2.) 3rd party application decodes and prepares these tickets or records >> for >> insert into Oracle >> 3.) As these records flow by, we use key fields inside them to do lookups >> into this lightweight SQL database as doing it into oracle would be even >> slower. >> > > > When doing lookup tables in Oracle (9i or later stable versions), you are > better off doing an index-organized table to reduce by 1 seek the amount > of > I/O Oracle does, and using a hash-key index-organized table to reduce the > seeks to (usually) ONLY 1. Without knowing where in your box the Oracle > instance is, it's difficult to say for sure, but assuming its on the same > machine, I would try this out, based on my experience: > > PIN the lookup table (index) into the buffer cache, giving Oracle the > extra > 5GB of memory space to do so. Assuming you have a persistent connection > to > Oracle from your fly-by-update process and your fly-by-lookup process, you > might find Oracle performs adequately. Either way I'd measure what's > taking > the time in doing lookups in Oracle that would be even slower; I assume > you > use bind parameters and cached SQL cursors (you get this automatically > with > most PL/SQL constructs [excepting dynamic sql type 4]), and basically have > 1 > parse per cursor prepare at application startup time. > > -- View this message in context: http://www.nabble.com/Cache-invalidation-after-insert-statements.-tf3944908.html#a11195405 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
