Am Dienstag, 27. Juli 2004 16:30 schrieb D. Richard Hipp:
> > The trigger statement:
> > --
> > CREATE TRIGGER haplo_share_of_p1p2 BEFORE INSERT ON haploshare
> > WHEN new.p1p2 == 0 OR new.p1p2 IS NULL
> > BEGIN
> > UPDATE haploshare
> > SET p1p2 = (SELECT count(*) FROM haploshare
> > WHERE p1p2 == -1 AND id1 == new.id1 AND id2 ==
> > new.id2) - 1
> > WHERE p1p2 == -1 AND id1 == new.id1 AND id2 == new.id2;
> > END;
>
> The trigger is probably doing two complete table scans for
> each insert. I'd suggest you add an index:
>
> CREATE INDEX haploshare_gofaster ON haploshare(p1p2,id1,id2);
I actually had single indices on id1, id2 and p1p2, but none on all three of
them. So I gave it a try. As you can see in the `top` snippet below, the new
version (test-5) has accumulated 100h of cpu-time already - still
calculating :(
--
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
3819 franke 1 0 0 1907M 1906M cpu/3 126.0H 1.56% mhs-test-4
28918 franke 1 0 0 2074M 2074M cpu/480 98.2H 1.56% mhs-test-5
--
Any other suggestions?
Daniel
--
Dipl.-Math. (FH) Daniel Franke
Institut fuer Medizinische Biometrie und Statistik
Medizinische Universit�t zu Luebeck
Ratzeburger Allee 160, Haus 4
23538 Luebeck
Telefon: 0451-500-2786
Telefax: 0451-500-2999
[EMAIL PROTECTED]
