Author: rhuijben
Date: Wed May 23 12:53:10 2012
New Revision: 1341848
URL: http://svn.apache.org/viewvc?rev=1341848&view=rev
Log:
Help the Sqlite query planner a bit by rewriting two queries in a way that
makes it use indexes, where it didn't before.
* subversion/libsvn_wc/wc-queries.sql
(STMT_RECURSIVE_UPDATE_NODE_REPO,
STMT_SELECT_EXTERNALS_DEFINED): Make the OR operation the outer operation
by duplicating some cheap tests.
* subversion/tests/libsvn_wc/wc-queries-test.c
(slow_statements): Remove two slow statements.
Modified:
subversion/trunk/subversion/libsvn_wc/wc-queries.sql
subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c
Modified: subversion/trunk/subversion/libsvn_wc/wc-queries.sql
URL:
http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_wc/wc-queries.sql?rev=1341848&r1=1341847&r2=1341848&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_wc/wc-queries.sql (original)
+++ subversion/trunk/subversion/libsvn_wc/wc-queries.sql Wed May 23 12:53:10
2012
@@ -328,10 +328,15 @@ WHERE dav_cache IS NOT NULL AND wc_id =
-- STMT_RECURSIVE_UPDATE_NODE_REPO
UPDATE nodes SET repos_id = ?4, dav_cache = NULL
-WHERE wc_id = ?1
- AND repos_id = ?3
- AND (local_relpath = ?2
- OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))
+/* ### The Sqlite optimizer needs help here ###
+ * WHERE wc_id = ?1
+ * AND repos_id = ?3
+ * AND (local_relpath = ?2
+ * OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))*/
+WHERE (wc_id = ?1 AND local_relpath = ?2 AND repos_id = ?3)
+ OR (wc_id = ?1 AND IS_STRICT_DESCENDANT_OF(local_relpath, ?2)
+ AND repos_id = ?3)
+
-- STMT_UPDATE_LOCK_REPOS_ID
UPDATE lock SET repos_id = ?2
@@ -995,6 +1000,7 @@ SELECT local_relpath, kind, repos_id, de
FROM externals
LEFT OUTER JOIN repository ON repository.id = externals.repos_id
WHERE wc_id = ?1
+ AND IS_STRICT_DESCENDANT_OF(local_relpath, ?2)
AND def_revision IS NULL
AND repos_id = (SELECT repos_id FROM nodes
WHERE nodes.local_relpath = ?2)
@@ -1014,9 +1020,12 @@ WHERE wc_id = ?1
-- STMT_SELECT_EXTERNALS_DEFINED
SELECT local_relpath, def_local_relpath
FROM externals
-WHERE wc_id = ?1
- AND (def_local_relpath = ?2
- OR IS_STRICT_DESCENDANT_OF(def_local_relpath, ?2))
+/* ### The Sqlite optimizer needs help here ###
+ * WHERE wc_id = ?1
+ * AND (def_local_relpath = ?2
+ * OR IS_STRICT_DESCENDANT_OF(def_local_relpath, ?2)) */
+WHERE (wc_id = ?1 AND def_local_relpath = ?2)
+ OR (wc_id = ?1 AND IS_STRICT_DESCENDANT_OF(def_local_relpath, ?2))
-- STMT_DELETE_EXTERNAL
DELETE FROM externals
Modified: subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c
URL:
http://svn.apache.org/viewvc/subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c?rev=1341848&r1=1341847&r2=1341848&view=diff
==============================================================================
--- subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c (original)
+++ subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c Wed May 23
12:53:10 2012
@@ -79,7 +79,6 @@ static const int schema_statements[] =
static const int slow_statements[] =
{
/* Operate on the entire WC */
- STMT_RECURSIVE_UPDATE_NODE_REPO,
STMT_HAS_SWITCHED_WCROOT,
STMT_HAS_SWITCHED_WCROOT_REPOS_ROOT,
STMT_SELECT_ALL_NODES,
@@ -93,7 +92,6 @@ static const int slow_statements[] =
/* Need review: */
STMT_SELECT_COMMITTABLE_EXTERNALS_BELOW,
- STMT_SELECT_EXTERNALS_DEFINED,
STMT_SELECT_EXTERNAL_PROPERTIES,
STMT_DELETE_ACTUAL_EMPTIES,
