On 03.08.2007, joel garry wrote:
> On Jul 31, 8:09 am, Wiktor Moskwa wrote:
>> Thanks for a reply. Let me change my question a little.
>> I wonder what is a usual practice of serving aggregate data in
>> a standard database (not a warehouse, used to store current data).
>> By aggregate data I mean for example monthly sales summary.
>>
>> The example environment is a web application with 2000 users
>> (each is a different <very> small business, not related to others,
>> with distinct subset of data). How to provide them with effective
>> up-to-date summaries? (triggers? MVs? queuing? calculation on
>> request and storing for later use?)
>
> Triggers not so good. In the past, when hardware was much slower, I'd
> do the calculation on request with storing for later use. I don't
> think I'd do that nowadays, but with the faster hardware the wheel
> that's already installed is like instant virtual DW's. Partioning
> would perhaps be more appropriate, but then again, we're talking
> organizations that are too cheap to have a second machine for
> reporting or DW, much less pay big licensing bucks if they don't
> _have_ to.
>
Hi Joel,
Thanks for your advice. That's exactly how you guessed - second DB
or partitioning is not an option.
We don't have any performance problems at the moment but I'd like
to design new parts and redesign older parts of this app "in
a correct way". In the application that I've recently iherited
the biggest problems are:
1. A lot of business logic in triggers
2. Undocumented, unpackaged procedures, some of them called from
application, some used internally - big chaos; and most of them
had been written without thinking about concurrency...
3. Java object-relational mapping framework that "simplifies database
access and isolates you from the complexity of databases" which
practically means that you can't force it do anything useful.
Curse on it!
> Some people are fans of VPD, I'm still reserving judgement on that,
> given the limitations it adds. For the situations I see, at any rate.
>
I've never used it but for me the idea behind VPD fits quite
well into our environment - each user (a small business) "owns"
a disjoint part of a table. But it would be useful for security
reasons only I think and wouldn't solve our current problems.
>> And a sub-question: with two tables INVOICE (parent) and
>> INVOICE_ITEM (child) - do you usually keep sum of values
>> from child rows in parent row or calculate in application
>> every time?
>
> Calculate every time, except for a modification where they need a
> snapshot to be able to reprint what was printed previously (I added a
> table for that, since this is a packaged app that makes a big deal
> about the dynamism). There are a lot more than two tables involved.
>
Two tables were used for simplification. Of course an invoice has to
be recorded in few "files" (I'm not sure if it's a correct English
word for it). Is my guess correct that your application would invoke
a single stored procedure that implements whole transaction and puts
invoice's data into some tables and updates other tables?
That's at least how I'd do it - a facade of packaged procedures that do
all the business logic, queries executed by functions returning ref
cursor. But I wonder if it is a good idea to put virtually everything
into procedures so that application never issues anything else than
PL/SQL call. For example simple create/update/delete/browse involving
one table at the time would need procedures for all those actions for
every such table. What are "best practices" in this area?
Thanks for patience.
--
Wiktor Moskwa
>> Stay informed about: Reports in OLTP system 
FAQ
Search
Profile
Private Messages
Log in
