Re: issues explaining performance issues to clients
- From: Bill Ferguson <wbfergus@xxxxxxxxx>
- To: jachang@xxxxxxxx
- Date: Thu, 23 Jul 2009 08:38:30 -0600
I have a similar situation, but at least I have the privilege of
restricting users access to my Apex application. But, I still had the
need to allow them to search any and all fields out of the 35 tables
(250-300 fields) in the database.
I wound up creating a CLOB field to store an XML tagged representation
of all of the data for each master record, the I use Oracle Text to
retrieve the primary key for everything that meets the users criteria,
then display enough data through a view so they can go through the
resulting list of records retrieved to select which ones they want
further information on. The query 'string' for Oracle Text is actually
fairly simple to use after getting it properly setup. My app just
builds a 'where clause' for the base SQL statement, so I have some
control over what gets submitted. Even if users insist they know what
they're doing, they rarely really do.
Getting it setup was a bit time-consuming at first, but that was
merely my time and then CPU time to populate the CLOB field. Since
implementing it, query time has dropped from 30+ minutes (minumum) to
around 10-30 seconds, so the savings to my users has been quite
substantial.
This may not work for your situation, but if you're interested, drop
me a line and I can give you some more details. It is a tad bit more
complicated than it sounds, there is some additional housekeeping
routine sthat need to be created and run, but the intial time spent
setting it up has proven extremely advantageous.
Just for the record, I have a mineral location database (for internal
use only), but there is a smaller variation of it available for public
use (in MySQL, due to licensing issues) at
http://mrdata.usgs.gov/mineral-resources/mrds-us.html. If you finally
get down to the part where you can display the information for each
site/mine, you can begin to see the various tables/fields. I'm not to
thrilled with the 'public' version, as to me it is far more difficult
to query and navigate than my 'internal' application, but at least
it's one less thing I have to worry about maintaining.
--
-- Bill Ferguson
--
//www.freelists.org/webpage/oracle-l
Other related posts:
