Re: Reports in OLTP system

On 15.08.2007, Gints Plivna <gints.plivna_at_gmail.com> wrote:
> [..]
> We had similar situation like you (need for complex searches) and we
> simply understood that we cannot generate them from business data and
> we cannot afford on commit MVs, so we got periodically refreshed MVs
> and forced users to accept that. Simple - either pay for Ferrrari or
> if you cannot, you can get Opel ;)
> OK and my experience is described here - Effective search in a
> normalized application at http://www.gplivna.eu/papers/mat_views_search.htm
>

Thanks for this case study, it was very informative. I think I'll leave triggers as they are, at least for now - there are no performance problems at the moment.

I liked your "Do you have data waste or data base?" artictle too - I've seen few applications that accurately match this description. Few weeks ago a friend of mine was repairing (or should I say rebuilding) a database that had no single index - "that would be a premature optimization" the other team said. On the other hand most tables were partitioned by some irrevelant, surrogate id, into over 200 partitions were most queries didn't have this id in WHERE clause - "we've partitioned it because database contains a lot of data and it will increase performance" they said. The db was PostgreSQL and "a lot of data" was around 300 000 rows...

-- 
Wiktor Moskwa