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.
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
