Bug 456029 - optimize the temp table triggers
authorMarco Bonardo <mak77@bonardo.net>
Fri, 17 Oct 2008 06:12:52 -0400
changeset 20571 dd03f065fcf11957b58d57e8c5e6be01522ea1de
parent 20570 49bf2417efdaaa0f990374e4029dab47ec14f416
child 20572 b65ff41ab65888f5546bcd2b430f0e5521233298
push id2972
push usertmielczarek@mozilla.com
push dateFri, 17 Oct 2008 10:35:06 +0000
treeherdermozilla-central@f31b66fd4192 [default view] [failures only]
perfherder[talos] [build metrics] [platform microbench] (compared to previous push)
bugs456029
milestone1.9.1b2pre
Bug 456029 - optimize the temp table triggers This changeset makes the triggers work much faster which should greatly reduce the timings of adding a visit and a bookmark. r=sdwilsh
toolkit/components/places/src/nsPlacesTriggers.h
--- a/toolkit/components/places/src/nsPlacesTriggers.h
+++ b/toolkit/components/places/src/nsPlacesTriggers.h
@@ -100,27 +100,28 @@
   "END" \
 )
 
 /**
  * This trigger allows for updates to a record in moz_places_view.  It first
  * copies the row from the permanent table over to the temp table if it does not
  * exist in the temporary table.  Then, it will update the temporary table with
  * the new data.
+ * We use INSERT OR IGNORE to avoid looking if the place already exists in the
+ * temp table.
  */
 #define CREATE_PLACES_VIEW_UPDATE_TRIGGER NS_LITERAL_CSTRING( \
   "CREATE TEMPORARY TRIGGER moz_places_view_update_trigger " \
   "INSTEAD OF UPDATE " \
   "ON moz_places_view " \
   "BEGIN " \
-    "INSERT INTO moz_places_temp " \
+    "INSERT OR IGNORE INTO moz_places_temp " \
     "SELECT * " \
     "FROM moz_places " \
-    "WHERE id = OLD.id " \
-    "AND id NOT IN (SELECT id FROM moz_places_temp); " \
+    "WHERE id = OLD.id; " \
     "UPDATE moz_places_temp " \
     "SET url = IFNULL(NEW.url, OLD.url), " \
         "title = IFNULL(NEW.title, OLD.title), " \
         "rev_host = IFNULL(NEW.rev_host, OLD.rev_host), " \
         "visit_count = IFNULL(NEW.visit_count, OLD.visit_count), " \
         "hidden = IFNULL(NEW.hidden, OLD.hidden), " \
         "typed = IFNULL(NEW.typed, OLD.typed), " \
         "favicon_id = IFNULL(NEW.favicon_id, OLD.favicon_id), " \
@@ -129,74 +130,89 @@
   "END" \
 )
 
 /**
  * This trigger allows for an insertion into  moz_historyvisits_view.  It enters
  * the new data into the temporary table, ensuring that the new id is one
  * greater than the largest id value found.  It then updates moz_places_view
  * with the new visit count.
+ * We use INSERT OR IGNORE to avoid looking if the place already exists in the
+ * temp table. 
  */
 #define CREATE_HISTORYVISITS_VIEW_INSERT_TRIGGER NS_LITERAL_CSTRING( \
   "CREATE TEMPORARY TRIGGER moz_historyvisits_view_insert_trigger " \
   "INSTEAD OF INSERT " \
   "ON moz_historyvisits_view " \
   "BEGIN " \
     "INSERT INTO moz_historyvisits_temp ( " \
       "id, from_visit, place_id, visit_date, visit_type, session " \
     ") " \
     "VALUES (MAX((SELECT IFNULL(MAX(id), 0) FROM moz_historyvisits_temp), " \
                 "(SELECT IFNULL(MAX(id), 0) FROM moz_historyvisits)) + 1, " \
             "NEW.from_visit, NEW.place_id, NEW.visit_date, NEW.visit_type, " \
             "NEW.session); " \
-    "UPDATE moz_places_view " \
+    "INSERT OR IGNORE INTO moz_places_temp " \
+    "SELECT * " \
+    "FROM moz_places " \
+    "WHERE id = NEW.place_id " \
+    "AND NEW.visit_type NOT IN (0, 4, 7); " \
+    "UPDATE moz_places_temp " \
     "SET visit_count = visit_count + 1 " \
     "WHERE id = NEW.place_id " \
     "AND NEW.visit_type NOT IN (0, 4, 7); " /* invalid, EMBED, DOWNLOAD */ \
   "END" \
 )
 
 /**
  * This trigger allows for the deletion of a record in moz_historyvisits_view.
  * It removes any entry in the temporary table, and removes any entry in the
  * permanent table as well.  It then updates moz_places_view with the new visit
  * count.
+ * We use INSERT OR IGNORE to avoid looking if the place already exists in the
+ * temp table.
  */
 #define CREATE_HISTORYVISITS_VIEW_DELETE_TRIGGER NS_LITERAL_CSTRING( \
   "CREATE TEMPORARY TRIGGER moz_historyvisits_view_delete_trigger " \
   "INSTEAD OF DELETE " \
   "ON moz_historyvisits_view " \
   "BEGIN " \
     "DELETE FROM moz_historyvisits_temp " \
     "WHERE id = OLD.id; " \
     "DELETE FROM moz_historyvisits " \
     "WHERE id = OLD.id; " \
-    "UPDATE moz_places_view " \
+    "INSERT OR IGNORE INTO moz_places_temp " \
+    "SELECT * " \
+    "FROM moz_places " \
+    "WHERE id = OLD.place_id " \
+    "AND OLD.visit_type NOT IN (0, 4, 7); " \
+    "UPDATE moz_places_temp " \
     "SET visit_count = visit_count - 1 " \
-    "WHERE moz_places_view.id = OLD.place_id " \
+    "WHERE id = OLD.place_id " \
     "AND OLD.visit_type NOT IN (0, 4, 7); " /* invalid, EMBED, DOWNLOAD */ \
   "END" \
 )
 
 /**
  * This trigger allows for updates to a record in moz_historyvisits_view.  It
  * first copies the row from the permanent table over to the temp table if it
  * does not exist in the temporary table.  Then it will update the temporary
  * table with the new data.
+ * We use INSERT OR IGNORE to avoid looking if the visit already exists in the
+ * temp table.
  */
 #define CREATE_HISTORYVISITS_VIEW_UPDATE_TRIGGER NS_LITERAL_CSTRING( \
   "CREATE TEMPORARY TRIGGER moz_historyvisits_view_update_trigger " \
   "INSTEAD OF UPDATE " \
   "ON moz_historyvisits_view " \
   "BEGIN " \
-    "INSERT INTO moz_historyvisits_temp " \
+    "INSERT OR IGNORE INTO moz_historyvisits_temp " \
     "SELECT * " \
     "FROM moz_historyvisits " \
-    "WHERE id = OLD.id " \
-    "AND id NOT IN (SELECT id FROM moz_historyvisits_temp); " \
+    "WHERE id = OLD.id; " \
     "UPDATE moz_historyvisits_temp " \
     "SET from_visit = IFNULL(NEW.from_visit, OLD.from_visit), " \
         "place_id = IFNULL(NEW.place_id, OLD.place_id), " \
         "visit_date = IFNULL(NEW.visit_date, OLD.visit_date), " \
         "visit_type = IFNULL(NEW.visit_type, OLD.visit_type), " \
         "session = IFNULL(NEW.session, OLD.session) " \
     "WHERE id = OLD.id; " \
   "END" \