Re: svn status <file> is slow under a large check-out

Bob Cardillo <bob.cardillo_at_gmail.com> writes:

> I'm running Subversion 1.7.4.50525 (r1295709) on Windows 7 Pro SP1.
>
> I have a large repository, and for clean development flow I've checked
> out the root locally. But because of this, when I do:
> svn status C:\mycheckout\trunk\folder1\file1.ext
>
> it takes a very long time, around 5-6 seconds, to finish. It doesn't
> matter if the given file is modified or not.
>
> I've run Sysinternals Process Monitor and found that there are hundreds
> of thousands of ReadFile operations done on \_svn\wc.db. There are no
> network accesses of course, and from an analysis of the procmon results
> it's clear the slowness is from these many wc.db accesses.
>
> But why? I could not find any issue related to this in the issue
> tracker nor any mention of it in the forums, mailing lists, or elsewhere
> on the web.
>
> One more point of interest. If I throw --non-recursive in there, as in:
> svn status --non-recursive C:\mycheckout\trunk\folder1\file1.ext
>
> it comes back immediately, no delay whatsoever. Since this is a file, I
> don't get why --non-recursive should make a difference, but there it is.
>
> Has anyone seen this? Any reason I should not add this to the issue
> tracker for Subversion?

Hmm, I'm not seeing a huge time difference but --non-recursive does have
a similar effect on Linux:

$ strace -cetrace=read svn st --non-recursive subversion/tests/libsvn_wc/wc_db.c
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
  -nan 0.000000 0 99 read
------ ----------- ----------- --------- --------- ----------------
100.00 0.000000 99 total

$ strace -cetrace=read svn st ../src/subversion/tests/libsvn_wc/wc_db.c
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
100.00 0.000025 0 1193 read
------ ----------- ----------- --------- --------- ----------------
100.00 0.000025 1193 total

So that has increased the number of read() calls from 99 to 1193!

Using -DSQLITE_DEBUG the difference is this:

DBG: sqlite.c: 66: sql="SELECT IFNULL((SELECT properties FROM actual_node a WHERE a.wc_id = 1 AND A.local_relpath = n.local_relpath), properties), local_relpath, depth FROM nodes n WHERE wc_id = 1 AND ('A/f' = '' OR local_relpath = 'A/f' OR ((local_relpath) > ('A/f') || '/' AND (local_relpath) < ('A/f') || '0') ) AND kind = 'dir' AND presence='normal' AND op_depth=(SELECT MAX(op_depth) FROM nodes o WHERE o.wc_id = 1 AND o.local_relpath = n.local_relpath) "

which is this statement in svn_wc__db_externals_gather_definitions:

-- STMT_SELECT_EXTERNAL_PROPERTIES
SELECT IFNULL((SELECT properties FROM actual_node a
               WHERE a.wc_id = ?1 AND A.local_relpath = n.local_relpath),
              properties),
       local_relpath, depth
FROM nodes n
WHERE wc_id = ?1
  AND (?2 = ''
       OR local_relpath = ?2
       OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))
  AND kind = 'dir' AND presence='normal'
  AND op_depth=(SELECT MAX(op_depth) FROM nodes o
                WHERE o.wc_id = ?1 AND o.local_relpath = n.local_relpath)

Taking that query out reduces the read() calls back to 99. Do we need
another SQLite index? Can we improve that query?

What does your working copy look like? Do you have svn:externals? Do
you have the sqlite3 tool? What do these commands show:

  sqlite3 .svn/wc.db "select count (*) from actual_node"
  sqlite3 .svn/wc.db "select count (*) from nodes"

-- 
uberSVN: Apache Subversion Made Easy
http://www.uberSVN.com