svn commit: r1342524 - in /subversion/trunk/subversion: libsvn_wc/wc-queries.sql libsvn_wc/wc_db.c tests/libsvn_wc/wc-queries-test.c

[rh...@apache.org]

Author: rhuijben
Date: Fri May 25 08:23:39 2012
New Revision: 1342524

URL: http://svn.apache.org/viewvc?rev=1342524&view=rev
Log:
Avoid nested full table scans on two tables in a single query by rewriting the
query to a completely different pattern.

Only operate on the relevant subset of the actual table to avoid doing unneeded
work on all other nodes in the working copy.

The IN (select local_relpath FROM .. WHERE .. AND kind = 'qqq') style
queries are not optimized by Sqlite to use a select on local_relpath.

Depending on the number of affected nodes and nodes in the working copy, this
query is now orders of magnitude faster.

* subversion/libsvn_wc/wc-queries.sql
  (STMT_UPDATE_ACTUAL_CHANGELISTS): Rewrite query.

* subversion/libsvn_wc/wc_db.c
  (set_changelist_txn): Pass the operation root as second argument.

* subversion/tests/libsvn_wc/wc-queries-test.c
  (slow_statements): Remove another slow statement from the list.

Modified:
    subversion/trunk/subversion/libsvn_wc/wc-queries.sql
    subversion/trunk/subversion/libsvn_wc/wc_db.c
    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=1342524&r1=1342523&r2=1342524&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_wc/wc-queries.sql (original)
+++ subversion/trunk/subversion/libsvn_wc/wc-queries.sql Fri May 25 08:23:39 2012
@@ -378,9 +378,13 @@ INSERT INTO actual_node (
 VALUES (?1, ?2, ?3, ?4)
 
 -- STMT_UPDATE_ACTUAL_CHANGELISTS
-UPDATE actual_node SET changelist = ?2
-WHERE wc_id = ?1 AND local_relpath IN
-(SELECT local_relpath FROM targets_list WHERE kind = 'file' AND wc_id = ?1)
+UPDATE actual_node SET changelist = ?3
+WHERE wc_id = ?1
+  AND (local_relpath = ?2 OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))
+  AND local_relpath = (SELECT local_relpath FROM targets_list AS t
+                       WHERE wc_id = ?1
+                         AND t.local_relpath = actual_node.local_relpath
+                         AND kind = 'file')
 
 -- STMT_UPDATE_ACTUAL_CLEAR_CHANGELIST
 UPDATE actual_node SET changelist = NULL

Modified: subversion/trunk/subversion/libsvn_wc/wc_db.c
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_wc/wc_db.c?rev=1342524&r1=1342523&r2=1342524&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_wc/wc_db.c (original)
+++ subversion/trunk/subversion/libsvn_wc/wc_db.c Fri May 25 08:23:39 2012
@@ -5091,7 +5091,8 @@ set_changelist_txn(void *baton,
   /* Update our changelists. */
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_UPDATE_ACTUAL_CHANGELISTS));
-  SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id, scb->new_changelist));
+  SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id, local_relpath,
+                            scb->new_changelist));
   SVN_ERR(svn_sqlite__step_done(stmt));
 
   if (scb->new_changelist)

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=1342524&r1=1342523&r2=1342524&view=diff
==============================================================================
--- subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c (original)
+++ subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c Fri May 25 08:23:39 2012
@@ -98,9 +98,6 @@ static const int slow_statements[] =
   STMT_SELECT_ALL_FILES,
   STMT_HAS_ACTUAL_NODES_CONFLICTS,
 
-  /* Join on targets table */
-  STMT_UPDATE_ACTUAL_CHANGELISTS,
-
   /* Need index? */
   STMT_SELECT_TARGETS,
   STMT_INSERT_ACTUAL_EMPTIES,