svn commit: r1455239 - /subversion/trunk/subversion/libsvn_wc/wc-queries.sql

[rh...@apache.org]

Author: rhuijben
Date: Mon Mar 11 17:09:33 2013
New Revision: 1455239

URL: http://svn.apache.org/r1455239
Log:
Apply some query tweaks to make sure the upcoming Sqlite 3.7.16 uses the
right index in these queries, where the view behavior is not strictly defined.

* subversion/libsvn_wc/wc-queries.sql
  (STMT_CACHE_TARGET_PROPS,
   STMT_CACHE_TARGET_PRISTINE_PROPS): Avoid using the NODES_CURRENT view in
     this query as the sqlite optimizer can't always look through views.
     Add an (ignored because this query follows the primary key) order by
     to add another hint to the optimizer to use the right index on NODES.

Modified:
    subversion/trunk/subversion/libsvn_wc/wc-queries.sql

Modified: subversion/trunk/subversion/libsvn_wc/wc-queries.sql
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_wc/wc-queries.sql?rev=1455239&r1=1455238&r2=1455239&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_wc/wc-queries.sql (original)
+++ subversion/trunk/subversion/libsvn_wc/wc-queries.sql Mon Mar 11 17:09:33 2013
@@ -1222,10 +1222,15 @@ INSERT INTO target_prop_cache(local_relp
                    AND a.local_relpath = n.local_relpath),
                n.properties)
    FROM targets_list AS t
-   JOIN nodes_current AS n ON t.wc_id= n.wc_id
-                          AND t.local_relpath = n.local_relpath
+   JOIN nodes AS n
+     ON n.wc_id = ?1
+    AND n.local_relpath = t.local_relpath
+    AND n.op_depth = (SELECT MAX(op_depth) FROM nodes AS n3
+                      WHERE n3.wc_id = ?1
+                        AND n3.local_relpath = t.local_relpath)
   WHERE t.wc_id = ?1
     AND (presence=MAP_NORMAL OR presence=MAP_INCOMPLETE)
+  ORDER BY t.local_relpath
 
 -- STMT_CACHE_TARGET_PRISTINE_PROPS
 INSERT INTO target_prop_cache(local_relpath, kind, properties)
@@ -1239,12 +1244,17 @@ INSERT INTO target_prop_cache(local_relp
                  ORDER BY p.op_depth DESC /* LIMIT 1 */)
           ELSE properties END
   FROM targets_list AS t
-  JOIN nodes_current AS n ON t.wc_id= n.wc_id
-                          AND t.local_relpath = n.local_relpath
+  JOIN nodes AS n
+    ON n.wc_id = ?1
+   AND n.local_relpath = t.local_relpath
+   AND n.op_depth = (SELECT MAX(op_depth) FROM nodes AS n3
+                     WHERE n3.wc_id = ?1
+                       AND n3.local_relpath = t.local_relpath)
   WHERE t.wc_id = ?1
     AND (presence = MAP_NORMAL
          OR presence = MAP_INCOMPLETE
          OR presence = MAP_BASE_DELETED)
+  ORDER BY t.local_relpath
 
 -- STMT_SELECT_ALL_TARGET_PROP_CACHE
 SELECT local_relpath, properties FROM target_prop_cache