I created the two additional indexes and ran some timing tests on the applicable tables and the timings came out much faster than anything I'd seen before, so thank you very much for your help on this. It is very interesting that you found the query to be performing "universally slow" all the way back to 3.6.23 as that does not fit my observations at all. The database file I sent you was initially created with an earlier version of SQLite and isn't the file automatically rebuilt with every build iteration, so perhaps that has something to do with it. If you were to dump the database and recreate it with an earlier version, you may not see the performance hit with the earlier versions, but will see it with 3.7.6.
As I provided, it was only with 3.7.6 that I started noticing the performance degradation, and not just with this query but with a lot of the queries in my application. Something definitely changed, and it forced me to make several schema changes to the database that actually turned out to be for the better anyway. So again, thanks for your help on this. Kenneth On April 20, 2011 at 10:46 AM 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. > > > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
