Re: Reports in OLTP system

On 03.08.2007, joel garry <joel-garry@xxxxxxxx> wrote:
On Jul 31, 8:09 am, Wiktor Moskwa <wiktorDOTmos...@xxxxxxxxx> 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
.

Relevant Pages

  • Re: One or many DataSet
    ... > Is it best practice to keep the amount of DataSets small but with many ... dataset is not a database, and it shouldn't be abused as one either. ... > Table2 has 2 columns, CustomerID and OrderID ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ASP Classic - Alphabetical lists
    ... Select * is bad practice. ... So, this puts a lot of strain on the database, as well as spiking the ... provided that the programmer knows his stuff, ... So the extra strain in these circumstances will be minimal. ...
    (microsoft.public.inetserver.asp.general)
  • RE: newbie - lists vs. database
    ... My question is more related to best practice than what is possible. ... to create a database or to let SP handle this for me by ... what is best practice for creating data entry screens when writing a ... Should I just use the edit in dataheet functionality or ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: Autoupdate field with number of related registers
    ... In my setup, as long as a school is not giving a student ID by ... I give an ID that has no meaning too. ... Just with a tiny change of the setup of the database, ... In practice school data contain many faulty information or some pupils ...
    (comp.databases.filemaker)
Loading