• Welcome, Guest. Please login.
February 27, 2020, 03:41:15 am


Welcome to the SQLitening support forums!

Performance issues with TRIGGERS

Started by Bern Ertl, November 11, 2010, 06:28:22 pm

Previous topic - Next topic

Bern Ertl

I've spent the better part fo the day tweaking my code and observing a very strange phenomenon.  My code creates a database with ~20 tables and ~100 triggers.  Up until yesterday, performance when inserting a record in table T1 (in a brand new database with no records) was excellent.  Today, after adding 9 triggers, inserting into table T1 takes roughly 6 times longer per record (as measured using PowerBASIC's TIX statements).

What I found very odd is that none of the 9 new triggers are executed by inserting on T1.  They pertain to inserts/updates on another table (T2) and *updates* on T1. 

Further testing showed that the one trigger that should be executed upon an update to T1 is responsible for a 4x performance hit when I'm *inserting*.

I've tested to ensure that the trigger is not executing (added an unconditional update to a safe field) and it isn't being fired.

I thought perhaps it might have somthing to do with just the number of triggers or something, but the results are not conclusive yet - if I remove one of my older triggers (update on T1 like the 4x hit newer trigger) while leaving the new ones intact, the performance hit is not the same as if I remove the trigger responsible for the 4x hit while leaving the old ones intact.

Any ideas what could be causing this phenomenon?

Bern Ertl

Anyone know how I can use the EXPLAIN statement to see what SQLite is doing?  I tried:

slExe( "EXPLAIN " + slBuildInsertOrUpdate( ... ), E1)

but I just got an error message that it didn't like the EXPLAIN statement.

Fred Meier

November 12, 2010, 12:57:24 pm #2 Last Edit: November 12, 2010, 01:02:02 pm by Fred Meier
Explain is a query so you need to use slSel.  But I do not get an error using slExe nor slSel as follows: slExe "Explain " & slBuildInsertOrUpdate("tblStress", "", _
                                                  "UCount", _
                                                  "ProcessID=" & format$(1))
      slSel "Explain " & slBuildInsertOrUpdate("tblStress", "", _
                                                  "UCount", _
                                                  "ProcessID=" & format$(1))
Using slExe is of no value cause the Explan is a query but I don't get an error.

I have not used more than one trigger in a database so can't help with you
problem.  There is a SQLite forum at http://old.nabble.com/SQLite-f797.html
that may be able to help. Even Richard Hipp (SQLite creator) contributes.  Of
course they would not understand SQLitening syntax.

If you are still getting error on slSel "Explain .... then post the error and the
actual code.

Bern, I assume your are working this problem in local mode to eliminate
any network/server involvement. 

Bern Ertl

Thanks Fred.  I revamped my test code to use slSel and it worked fine.

I was originally working in remote mode and the server was timing out (error -18).  I changed to local mode and it seemed to get locked up in a loop while processing the results, so I added some code to review the results line by line and output to a file whenever I wanted to exit.

I lost patience after it got to roughly 7,000 records, but upon examining the output, I am seeing something curious.  It appears as if 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 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.

I need to confirm that I'm interpreting these results correctly.