Re: Strategy for Web based Oracle application

Rick Denoire <100.17706_at_germanynet.de> writes:

>The reason for this is that while the user clicks on different
>buttons and does different choices, queries are composed by the PHP
>application in a "mechanistical" way. Resulting queries are
>syntactically correct, but have a horrible performance.

What you describe sounds like what database gurus call a decision support system or DSS. A not-tiny amount of data with all sorts of ad hoc queries run against it. These systems are usually maintained differently from an ordinary transaction processing system, with lots and lots of indexes to get reasonable performance out of any strange query a user might invent. The price, of course, is that update performance will be horrible.

If you want to allow the users to query anything at all through the web interface then you will have to put up with having a wide range of different queries. If you want to restrict querying to things that have a usable index, you'll have to make your web interface less general.

It may be possible that the generated queries could be optimized by hand in some cases, but you can't do that for all possible generated queries, so unless you want to make the user interface less flexible you'll have to keep using generated code.

I don't know anything about your application, but you might find that making a denormalized schema (populated as a copy of the live data once every day) and indexing it up to the nines could help. Because you will not be making any updates to this copy of the data, the indexes won't hurt performance once they have been created.

Or you could denormalize to a single table, and then even without indexes the worst that any query could do is a single table scan, which may be acceptable.

-- 
Ed Avis <ed_at_membled.com>