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? > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
