Re: performance with triggers depends on table size?

On Thu, 15 Aug 2002 02:17:10 +0200, Christian Mock <cm(at)coretec(dot)at>
wrote:
>So this means my kludgy solution is as good as it can get ATM? That
>would mean going back to boring CGI script coding instead of fiddling
>with performance :-/

Christian,

do you *need* to update event_stats in a trigger? What I mean is, if
you have tight control over INSERTs to ac_event, you could remove that
part of the trigger and
BEGIN;

INSERT INTO ac_event SELECT * FROM in_event;

UPDATE event_stats
SET count = count + t.cnt
FROM (SELECT c1, c2, ..., COUNT(*) AS cnt
FROM in_event
GROUP BY c1, c2, ...) AS t
WHERE event_stats.c1 = t.c1 AND event_stats.c2 = t.c2 ...;

INSERT INTO event_stats (c1, c2, ..., count)
SELECT c1, c2, ..., COUNT(*)
FROM in_event AS e
WHERE NOT EXISTS (SELECT *
FROM event_stats AS s
WHERE s.c1 = e.c1 AND s.c2 = e.c2 ...)
GROUP BY c1, c2, ...;

DELETE FROM in_event; -- ??

COMMIT;

This assumes you want to move rows from in_event to ac_event. If you
want to keep rows in in_event, you will probably need an additional
condition in the SELECTs from in_event ...

Servus
Manfred