On Wed, Apr 20, 2011 at 12:37 AM, Kenneth Ballard < kball...@kennethballard.com> wrote:
> Okay I'm going to just give up on creating a specific test case for this > one and I'm just attaching the original database file. Every time I tried to > isolate out just the tables applicable to the trigger code, the issue is not > reproducible. So attached to this message is the database in question with > data. > This mailing list strips attachments. Why don't you email the database directly to me at d...@sqlite.org? > > To reproduce the issue, merely execute this query: > > DELETE FROM age WHERE island = ?; > > where ? is one of the values in the table -- 140 will likely provide the > largest wait time. However, if you execute the trigger's code as a > stand-alone query, it executes almost instantly. > > DELETE FROM offers > WHERE shoppe_id IN > (SELECT id FROM shoppes WHERE island = ?); > > where ? is the same island id you use in the previous query. > > So until this is figured out, I guess my application code is going to have > to execute the queries separately -- not a huge deal, but the trigger > simplifies the application code a little. Let me know if you have any luck > reproducing the issue. > > Kenneth > > > On 4/16/2011 8:09 PM, Richard Hipp wrote: > >> On Sat, Apr 16, 2011 at 6:29 PM, Kenneth Ballard< >> kball...@kennethballard.com> wrote: >> >> 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. >>> >>> Using the schema and sqlite_stat1 data you provide, I get identical >> query >> plans for 3.7.5 and 3.7.6.1. So I do not know what might have changed. >> Can >> you put together a reasonably sized test case that demonstrates the >> performance regression? >> >> >> >> 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 >>> >>> >> >> > > _______________________________________________ > 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
