On Sat, Apr 16, 2011 at 6:29 PM, Kenneth Ballard < kball...@kennethballard.com> wrote:
> The schema for these two tables is unchanged between the 3.7.5 and 3.7.6 > version with the exception of the sort modifiers on table_b's unique index. > Using the schema and sqlite_stat1 data you provide, I get identical query plans for 3.7.5 and 3.7.6.1. So I do not know what might have changed. Can you put together a reasonably sized test case that demonstrates the performance regression? > > Table table_b: > > CREATE TABLE [table_b] ( > [col_a] INTEGER NOT NULL CONSTRAINT [fk_tableb_tablec] REFERENCES > [tablec]([col_a]) ON DELETE CASCADE, > [col_b] INTEGER NOT NULL CONSTRAINT [fk_tableb_tabled] REFERENCES > [tabled]([col_a]) ON DELETE CASCADE, > [col_c] INTEGER, > [col_d] INTEGER, > [col_e] INTEGER, > [col_f] INTEGER, > CONSTRAINT [VALID_OFFER] CHECK(((col_c IS NOT NULL AND col_c > 0) AND > (col_d IS NOT NULL AND col_d >= 0)) > OR > ((col_e IS NOT NULL AND col_e > 0) AND (col_f IS NOT NULL AND col_f >= > 0)))); > > CREATE UNIQUE INDEX [idx_tableb_tablec_tabled] ON [table_b] ([col_b] > ASC, [col_a] ASC); > > Table table_c: > > CREATE TABLE [table_c] ( > [col_a] INTEGER NOT NULL PRIMARY KEY, > [col_b] INTEGER NOT NULL CONSTRAINT [fk_tablec_tablee] REFERENCES > [table_e]([col_a]), > [col_c] TEXT NOT NULL); > > And the sqlite_stat1 table data: > > table_a,<null>,1 > table_b,idx_tableb_tablec_tabled,6745 42 1 > table_c,<null>974 > > > On 4/16/2011 4:35 PM, Kenneth Ballard wrote: > > Good afternoon, > > > > Here is an issue I started to experience after upgrading from SQLite > > 3.7.5 to 3.7.6 involving a trigger I have on a database table. > > > > The table with the trigger is a 2-column table with the following > > trigger installed to it: > > > > CREATE TABLE [table_a] ( > > [col_a] INTEGER NOT NULL PRIMARY KEY, > > [col_b] INT64 NOT NULL); > > > > CREATE TRIGGER [RemoveOffers] > > BEFORE DELETE > > ON [table_a] > > BEGIN > > DELETE FROM table_b > > WHERE col_a IN > > (SELECT col_a FROM table_c WHERE col_b = old.col_a); > > END; > > > > On 3.7.5, a single delete statement from this table executed almost > > instantaneously even on the largest sets of data it would be clearing > > out. However, after upgrading to 3.7.6 and 3.7.6.1, this isn't the case. > > To have the same performance as I did on 3.7.5, I have to use the > > trigger query separately -- a delete statement on table_a and the delete > > statement on table_b separately. > > > > Again, as a trigger this statement takes seconds to run -- one run took > > as much as 15 seconds to execute. As separate delete statements, it > > takes a fraction of a second, similar to how it ran as a trigger in > 3.7.5. > > > > I've also noticed issues where queries with an IN clause (like the > > trigger above) took significantly longer to execute than before, but > > that was cleared up by adding some more indexes to the table > > definitions. But even adding additional indexes didn't help the > > performance of this trigger running as a trigger. > > > > So this begs the question: what changed? > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
