Daniel Franke wrote:
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 :(
Why don't you post your complete schema (the output of the ".schema"
command in the command-line shell) and the query that you are
running. That will help us to better understand your problem.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
