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.
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
