Hey everyone,
currently I have some temporary table which I populate this way (recursive
triggers on)
CREATE TEMPORARY TABLE IF NOT EXISTS NestedEntriesResult_ID (IDX INTEGER NOT
NULL, DBORDER INTEGER PRIMARY KEY NOT NULL);
CREATE TEMPORARY TRIGGER IF NOT EXISTS NestedEntriesPopulate_ID
AFTER INSERT
ON NestedEntriesResult_ID
FOR EACH ROW
BEGIN
INSERT INTO NestedEntriesResult_ID (IDX) SELECT IndexME.IDI FROM IndexME WHERE
IndexME.Parent = New.IDX ORDER BY IndexME.[Order];
END
INSERT INTO NestedEntriesResult_ID (IDX) VALUES (?)
this insert runs chain of triggers which insert all child items in following way
0 - 1 - 2 - 3
- 4
- 5 - 6
- 7
- 8
table IndexME contains tree-like structure where IDI - primary key, Parent -
item tree parent - link to "parent item" IDI, Order - item order within same
Parent (aka unique value for rows which have same Parent). All important
columns are indexed.
As can be guessed populating table is quite slow - ~150ms for around 10k rows
in IndexME.
Suggestions for better (faster) method ?
I've yet to try simply doing it C, but I don't think running 10k inserts (to
get result into table - as it's part of another query) alone will give me
better performance.
thanks in advance,
M.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
