On Sat, Apr 16, 2011 at 5:57 PM, Richard Hipp <d...@sqlite.org> wrote:
> > > On Sat, Apr 16, 2011 at 5:35 PM, Kenneth Ballard < > kball...@kennethballard.com> 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; >> > > What's the schema for table_b and table_c? > Please also send the content of the sqlite_stat1 and sqlite_stat2 tables. > > >> >> 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 >> > > > > -- > D. Richard Hipp > d...@sqlite.org > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
