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. > Using the database you sent me by private email, I found that SQLite was uniformly slow for all versions of SQLite back through 3.6.23. (I didn't test anything further back than that.) There was no speed difference going from 3.7.5 to 3.7.6. However, I did find that I could make the query fast as follows: CREATE INDEX newidx1 ON shoppes(island); CREATE INDEX newidx2 ON offers(shoppe_id); As a rule of thumb, you should always have in index on foreign keys. SQLite does *not* create such indices for you automatically. > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
