On one CollabNet's forums a user reported that a single delete command
of 2000+ WC files took well over three hours to complete with 1.7.5
(see
http://subversion.open.collab.net/ds/viewMessage.do?dsForumId=4&dsMessageId=456214).
I'm able to replicate similar behavior with a partial checkout of
^/subversion/tags
My test WC:
WC Size: 437 MB 21,012 Files, 2,717 Folders
wc.db Size: 18,108,000 bytes
Deletion Targets: 2642 files
Using 1.7.5 this takes almost 27 minutes on my machine:
C:\SVN\sandbox\subversion-tags>timethis svn delete -q --targets
del-target.2463.txt
TimeThis : Command Line : svn delete -q --targets del-target.2463.txt
TimeThis : Start Time : Fri May 18 11:27:04 2012
TimeThis : Command Line : svn delete -q --targets del-target.2463.txt
TimeThis : Start Time : Fri May 18 11:27:04 2012
TimeThis : End Time : Fri May 18 11:53:58 2012
TimeThis : Elapsed Time : 00:26:53.758
trunk@1341851 is significantly faster, taking only 16 minutes:
C:\SVN\sandbox\subversion-tags>timethis svn delete -q --targets
del-target.2463.txt
TimeThis : Command Line : svn delete -q --targets del-target.2463.txt
TimeThis : Start Time : Wed May 23 10:56:41 2012
TimeThis : Command Line : svn delete -q --targets del-target.2463.txt
TimeThis : Start Time : Wed May 23 10:56:41 2012
TimeThis : End Time : Wed May 23 11:13:17 2012
TimeThis : Elapsed Time : 00:16:35.873
Using optimizations similar to what Bert used in r1341848 and creating
a new , the attach patch cuts this down to just over 4 seconds:
C:\SVN\sandbox\subversion-tags>timethis svn delete -q --targets
del-target.2463.txt
TimeThis : Command Line : svn delete -q --targets del-target.2463.txt
TimeThis : Start Time : Wed May 23 17:41:12 2012
TimeThis : Command Line : svn delete -q --targets del-target.2463.txt
TimeThis : Start Time : Wed May 23 17:41:12 2012
TimeThis : End Time : Wed May 23 17:41:16 2012
TimeThis : Elapsed Time : 00:00:04.116
...So, WCNG gurus, does this look ok?
[[[
Speed up WC deletions.
* subversion/libsvn_wc/wc-queries.sql
(STMT_DELETE_NODES_ABOVE_DEPTH_RECURSIVE,
STMT_INSERT_DELETE_FROM_NODE_RECURSIVE): Make the OR operation the
outer operation by duplicating some cheap tests.
(STMT_INSERT_DELETE_LIST_RECURSIVE,
STMT_INSERT_DELETE_LIST): Split old STMT_INSERT_DELETE_LIST into two
new versions, one recursive, and one not.
* subversion/libsvn_wc/wc_db.c
(delete_node): Use the faster non-recursive query when operating on a file.
]]]
--
Paul T. Burba
CollabNet, Inc. -- www.collab.net -- Enterprise Cloud Development
Skype: ptburba
Index: subversion/libsvn_wc/wc-queries.sql
===================================================================
--- subversion/libsvn_wc/wc-queries.sql (revision 1342072)
+++ subversion/libsvn_wc/wc-queries.sql (working copy)
@@ -587,11 +587,15 @@
WHERE wc_id = ?1 AND local_relpath = ?2
-- STMT_DELETE_NODES_ABOVE_DEPTH_RECURSIVE
+/* ### The Sqlite optimizer needs help here ###
+ * WHERE wc_id = ?1
+ * AND (local_relpath = ?2
+ * OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))
+ * AND op_depth >= ?3 */
DELETE FROM nodes
-WHERE wc_id = ?1
- AND (local_relpath = ?2
- OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))
- AND op_depth >= ?3
+WHERE (wc_id = ?1 AND local_relpath = ?2 AND op_depth >= ?3)
+ OR (wc_id = ?1 AND IS_STRICT_DESCENDANT_OF(local_relpath, ?2)
+ AND op_depth >= ?3)
-- STMT_DELETE_ACTUAL_NODE
DELETE FROM actual_node
@@ -828,16 +832,25 @@
/* If this query is updated, STMT_INSERT_DELETE_LIST should too. */
-- STMT_INSERT_DELETE_FROM_NODE_RECURSIVE
+/* ### The Sqlite optimizer needs help here ###
+ * WHERE wc_id = ?1
+ * AND (local_relpath = ?2
+ * OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))
+ * AND op_depth = ?3
+ * AND presence NOT IN ('base-deleted', 'not-present', 'excluded',
+ * 'absent') */
INSERT INTO nodes (
wc_id, local_relpath, op_depth, parent_relpath, presence, kind)
SELECT wc_id, local_relpath, ?4 /*op_depth*/, parent_relpath, 'base-deleted',
kind
FROM nodes
-WHERE wc_id = ?1
- AND (local_relpath = ?2
- OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))
- AND op_depth = ?3
- AND presence NOT IN ('base-deleted', 'not-present', 'excluded', 'absent')
+WHERE (wc_id = ?1 AND local_relpath = ?2 AND op_depth = ?3
+ AND presence NOT IN ('base-deleted', 'not-present', 'excluded',
+ 'absent'))
+ OR (wc_id = ?1 AND IS_STRICT_DESCENDANT_OF(local_relpath, ?2)
+ AND op_depth = ?3
+ AND presence NOT IN ('base-deleted', 'not-present', 'excluded',
+ 'absent'))
-- STMT_INSERT_WORKING_NODE_FROM_BASE_COPY
INSERT INTO nodes (
@@ -1229,7 +1242,7 @@
/* This matches the selection in STMT_INSERT_DELETE_FROM_NODE_RECURSIVE.
A subquery is used instead of nodes_current to avoid a table scan */
--- STMT_INSERT_DELETE_LIST
+-- STMT_INSERT_DELETE_LIST_RECURSIVE
INSERT INTO delete_list(local_relpath)
SELECT local_relpath FROM nodes AS n
WHERE wc_id = ?1
@@ -1241,6 +1254,17 @@
AND s.local_relpath = n.local_relpath)
AND presence NOT IN ('base-deleted', 'not-present', 'excluded', 'absent')
+-- STMT_INSERT_DELETE_LIST
+INSERT INTO delete_list(local_relpath)
+SELECT local_relpath FROM nodes AS n
+WHERE wc_id = ?1
+ AND (local_relpath = ?2)
+ AND op_depth >= ?3
+ AND op_depth = (SELECT MAX(s.op_depth) FROM nodes AS s
+ WHERE s.wc_id = ?1
+ AND s.local_relpath = n.local_relpath)
+ AND presence NOT IN ('base-deleted', 'not-present', 'excluded', 'absent')
+
-- STMT_SELECT_DELETE_LIST
SELECT local_relpath FROM delete_list
ORDER BY local_relpath
Index: subversion/libsvn_wc/wc_db.c
===================================================================
--- subversion/libsvn_wc/wc_db.c (revision 1342072)
+++ subversion/libsvn_wc/wc_db.c (working copy)
@@ -6728,8 +6728,15 @@
}
/* ### Put actual-only nodes into the list? */
- SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
- STMT_INSERT_DELETE_LIST));
+ /* If LOCAL_RELPATH at SELECT_DEPTH is a file we can use
+ the (faster) non-recursive query. */
+ if (kind == svn_kind_file)
+ SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
+ STMT_INSERT_DELETE_LIST));
+ else
+ SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
+ STMT_INSERT_DELETE_LIST_RECURSIVE));
+
SVN_ERR(svn_sqlite__bindf(stmt, "isd",
wcroot->wc_id, local_relpath, select_depth));
SVN_ERR(svn_sqlite__step_done(stmt));
