Re: issues explaining performance issues to clients
Date: Thu, 23 Jul 2009 08:38:30 -0600
Message-ID: <4025610e0907230738o729aece1x4ebfcd0c51722d62_at_mail.gmail.com>
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 -- http://www.freelists.org/webpage/oracle-l