Re: performance with triggers depends on table size?

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