Database Design question

Question

Hello everyone!

I have a Firebird database, which let's say has Tables A and B which look like this:

TableA

TableB's Id | SomeNumber | OtherNumber | ComputedByField              | OtherIntField
    1              5          200           SomeNumber*OtherNumber          10
    1              2          70                  ...                       20

TableB

Id   | Sum                     |  Sum
 1     Sum of OtherIntFIeld       Sum of ComputedByField

where TableA'sId = TableB's Id

But now that TableB is getting populated (it's at 2k rows) accessing it becomes also slow due to that computation.

So my question is : Should I drop TableA's computedby field and add it straight into TableB, and when new TableA rows are added to modify the Corresponding row manually? (This should be faster, but I don't really like it)

Edit: The performance issue comes from the fact that there are two computedby fields which do exactly the same query over TableA but return different fields. I guess that is the part which needs optimization.

Edit2: The rows are computed as follows

TABLEB_FIELD_X COMPUTED BY ((
    select SUM(TableA.FieldX) from TableA
    where TableA.FAT_ID = TableB.ID
))

TABLEB_FIELD_Y COMPUTED BY ((
    select SUM(TableA.FieldY) from TableA
    where TableA.FAT_ID = TableB.ID
))

The main issue I believe comes from the fact these two independent fields query the same rows from TableA they just get a different field as result.

Answer 1

Instead of TableB, why don't you create a view? (Warning: I never used Firebird and I don't know how well it supports views).

Such view will be something like:

 create view Totals (SumX, SumY)
          as
      select SUM(FieldX), SUM(FieldY)
        from TableA
    group by ID

Every time TableA will be changed, such view (Totals) will be updated automatically. A decent DBMS will do a good job at optimizing this: it will recalculate enough of Totals only when TableA is changed, unlike your TableB, which will be recalculated every time is queried (I presume by your performance problems - to be sure one needs to know Firebird internals).

Totals will be like any other table (but read-only); you will filter information with something like:

select * from Totals where ID = ...

P.S.: Walter Mitty brought to my attention in comments below what I assumed about your original solution, i.e. that TableA:

• must be in (at least) First Normal Form (1NF) - otherwise one does not have guaranteed correct results for relational operations (nota bene: one must know your application requirements and conceptual/logical design in detail - which is impractical to acquire in a forum/via indirect communication - to spot eventual problems in a not completely normalized design, such us insert/delete/update anomalies; a good book on DB theory will spare you headaches and unfortunately such books are few);
• it is properly indexed (at least on ID).

It is unfortunate that SQL-based DBMSes require such physical design details from users to have decent performances, it is contrary to what a DBMS is supposed to be good at (it has all the information needed to do a good job automatically, i.e. ownership of physical data structures and data access path statistics).

Answer 2

I would use a trigger on Table A which updates table B with the sum of the fields after any inserts/updates/deletes in Table A.

This would have a small additional impact on any transactions happening on table A, but would be more efficient when querying Table B.

Answer 3

Not sure why would you want to recalculate the whole table each time you update? That's what your code and explanation seem to imply.

The best thing is to store computed value as a regular value integer or whatever is the result. Then you can also add things like indexing and such. If you leave it as a dynamic field your index will also have to be rebuild on the fly if you have one.

The standard procedure is to move this to code, and calculate & update ComputedValue field on the fly when you update either of the other two. And if you have more than a one place in your code where you update that field I would look at optimizing your code.

Also if you REALLY REALLY need to speed it up you can resort to stored procedures ( I don't use Firebird - so I don't know if it supports it) and call them from your code when you need to.