You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@subversion.apache.org by rh...@apache.org on 2012/05/23 14:53:11 UTC
svn commit: r1341848 - in /subversion/trunk/subversion:
libsvn_wc/wc-queries.sql tests/libsvn_wc/wc-queries-test.c
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,
Re: svn commit: r1341848 - in /subversion/trunk/subversion:
libsvn_wc/wc-queries.sql tests/libsvn_wc/wc-queries-test.c
Posted by Greg Stein <gs...@gmail.com>.
On May 23, 2012 8:53 AM, <rh...@apache.org> wrote:
>...
> +++ subversion/trunk/subversion/libsvn_wc/wc-queries.sql Wed May 23
12:53:10 2012
>...
> @@ -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)
The change is not described.
>...
Cheers,
-g
Re: svn commit: r1341848 - in /subversion/trunk/subversion:
libsvn_wc/wc-queries.sql tests/libsvn_wc/wc-queries-test.c
Posted by Paul Burba <pt...@gmail.com>.
On Wed, May 23, 2012 at 8:53 AM, <rh...@apache.org> wrote:
> 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.
Hi Bert,
Could you explain in a bit more detail how/why this optimization works?
--
Paul T. Burba
CollabNet, Inc. -- www.collab.net -- Enterprise Cloud Development
Skype: ptburba
> * 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,
Re: svn commit: r1341848 - in /subversion/trunk/subversion:
libsvn_wc/wc-queries.sql tests/libsvn_wc/wc-queries-test.c
Posted by Paul Burba <pt...@gmail.com>.
On Wed, May 23, 2012 at 8:53 AM, <rh...@apache.org> wrote:
> 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.
Hi Bert,
Could you explain in a bit more detail how/why this optimization works?
--
Paul T. Burba
CollabNet, Inc. -- www.collab.net -- Enterprise Cloud Development
Skype: ptburba
> * 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,