When compiled with SVN_DEBUG and SQLITE3_DEBUG and 'svn st' against a svn trunk WC, a number of things pop out.
We perform 28,062 SQL queries. --- DBG: sqlite.c: 63: sql="select root, uuid from repository where id = 1;" --- We execute *this* query (STMT_SELECT_REPOSITORY_BY_ID) 2215 times. Yikes. I think this has to do with svn_wc__db_base_get_info's call to fetch_repos_info. I'd think we'd be able to cache this result. I'll take a stab and see if this reduction saves us any real time. The root and uuid should be constant for an wc_id...right? For each file that we hit the DB for, we execute the following queries: --- DBG: sqlite.c: 63: sql="select repos_id, repos_relpath, presence, kind, revnum, checksum, translated_size, changed_rev, changed_date, changed_author, depth, symlink_target, last_mod_time, properties from base_node where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select presence, kind, checksum, translated_size, changed_rev, changed_date, changed_author, depth, symlink_target, copyfrom_repos_id, copyfrom_repos_path, copyfrom_revnum, moved_here, moved_to, last_mod_time, properties from working_node where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select prop_reject, changelist, conflict_old, conflict_new, conflict_working, tree_conflict_data, properties from actual_node where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select base_node.repos_id, base_node.repos_relpath, presence, kind, revnum, checksum, translated_size, changed_rev, changed_date, changed_author, depth, symlink_target, last_mod_time, properties, lock_token, lock_owner, lock_comment, lock_date from base_node left outer join lock on base_node.repos_id = lock.repos_id and base_node.repos_relpath = lock.repos_relpath where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select presence, kind, checksum, translated_size, changed_rev, changed_date, changed_author, depth, symlink_target, copyfrom_repos_id, copyfrom_repos_path, copyfrom_revnum, moved_here, moved_to, last_mod_time, properties from working_node where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select prop_reject, changelist, conflict_old, conflict_new, conflict_working, tree_conflict_data, properties from actual_node where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select root, uuid from repository where id = 1;" DBG: sqlite.c: 63: sql="select prop_reject, changelist, conflict_old, conflict_new, conflict_working, tree_conflict_data, properties from actual_node where wc_id = 1 and local_relpath = 'contrib/server-side';" DBG: sqlite.c: 63: sql="SELECT properties, presence FROM WORKING_NODE WHERE wc_id = 1 AND local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select properties from base_node where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select properties from actual_node where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="SELECT properties, presence FROM WORKING_NODE WHERE wc_id = 1 AND local_relpath = 'contrib/server-side/fsfsverify.py';" DBG: sqlite.c: 63: sql="select properties from base_node where wc_id = 1 and local_relpath = 'contrib/server-side/fsfsverify.py';" --- Notably, AFAICT, we're repeating a few of these queries: - STMT_SELECT_WORKING_NODE (2 times) - STMT_SELECT_ACTUAL_NODE (3 times) - STMT_SELECT_WORKING_PROPS (2 times) - STMT_SELECT_BASE_PROPS (2 times) I haven't yet dug into why we're repeating the queries. So, I'd bet we can cut our volume of SQL calls dramatically with some minor rejiggering not to lose the values when we do the first calls of the statement. -- justin
