Daniel Franke wrote:
The statement:
INSERT INTO haploshare
SELECT id1, id2, locus,
CASE WHEN p1 == 0 OR p2 == 0 THEN NULL WHEN p1 != p2 THEN 0 ELSE -1 END,
CASE WHEN p1 == 0 OR m2 == 0 THEN NULL WHEN p1 != m2 THEN 0 ELSE -1 END,
CASE WHEN m1 == 0 OR p2 == 0 THEN NULL WHEN m1 != p2 THEN 0 ELSE -1 END,
CASE WHEN m1 == 0 OR m2 == 0 THEN NULL WHEN m1 != m2 THEN 0 ELSE -1 END,
FROM matched_pairs
ORDER BY id1, id2, locus;
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);
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
