On Thu, 15 Aug 2002 02:17:10 +0200, Christian Mock <cm@coretec.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
