On 11/16/2010 06:25 AM, Bernard Ertl wrote: > Hi, > > I'm experiencing some performance issues with triggers at the moment and > hoping someone can help shed some light on what is happening. > > I have a database with ~20 tables and>100 triggers. I noticed a severe > performance degradation after adding the last few triggers and it puzzled me > because the triggers I added should not be executed by the statements I was > testing. > > In a nutshell, I am inserting a record into a table. The last few triggers > causing the severe performance degradation should only be triggered if there > is an update to the table. > > I tried capturing the output from the EXPLAIN statement and, as best as I can > tell, SQLite is queing up triggers if there is a possibility that they will > be needed (but before an evaluation confirms it). > > For example, I have a trigger that, upon an insert, tests a condition and > possibly performs an update pending the results of the condition. As a > result, a whole slew of triggers conditioned to an update on the table are > showing up in the EXPLAIN output (immediately after the insert trigger) even > though the result of the initial condition is false and the update is not > executed. From what I'm seeing there is a huge cascade of triggers listed in > the EXPLAIN output that should never be executed because the conditions for > executing them are never met. > > Can anyone confirm how SQLite processes triggers? Am I interpreting the > EXPLAIN results correctly?
Sounds like it. Calling sqlite3_prepare_v2() generates the VM code for all triggers that could possibly be invoked by your statement. All it considers when determining which triggers might be needed is the type of statement (UPDATE, DELETE, INSERT) and for UPDATES, the columns updated. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
