"Show labels" from $/ is VERY slow

[davenovak]

So slow, in fact, that we've seen timeouts on the database. When we get errors, here's an example of what the error looks like in the log file:

Code: Select all

----3/28/2011 2:10:28 PM     dnovak--dnovak-uberpc.xaviant.net(192.168.200.102)--SSL Disabled    System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at VaultServiceSQL.VaultSqlSCC.BeginLabelQuery(VaultSqlConn conn, String strSessionID, Int32 nRepID, Int32 nUserID, Int16 nLabelTreeType, String strLCItemPath, Int64 nObjID, UInt16 nObjType, Boolean bGetRecursive, Boolean bGetInherited, UInt16 nObjTypeFlags, Int64 nRowLimit, Int64& nRowsRetrieved, Int64& nRowsRetrievedRecursive, String& strQryToken)    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at VaultServiceSQL.VaultSqlSCC.BeginLabelQuery(VaultSqlConn conn, String strSessionID, Int32 nRepID, Int32 nUserID, Int16 nLabelTreeType, String strLCItemPath, Int64 nObjID, UInt16 nObjType, Boolean bGetRecursive, Boolean bGetInherited, UInt16 nObjTypeFlags, Int64 nRowLimit, Int64& nRowsRetrieved, Int64& nRowsRetrievedRecursive, String& strQryToken)
----3/28/2011 2:10:28 PM     dnovak--dnovak-uberpc.xaviant.net(192.168.200.102)--SSL Disabled    BeginLabelQuery returned: FailDBInsert 

After running some typical database maintenance on the vault database (which included re-indexing the DB), the results now return without error, but still take nearly 5 full minutes to show the labels off of root $/. What I find surprising is that we only have 12 labels off of the root at this time, yet it takes that long to return the results.

Why in the world is this so darn slow? It's practically unusable in it's current form.

In contrast, running Show Labels... from any branch directly off of root (such as $/CryENGINE3/) returns results in about 5 seconds.

Here are some stats on this repository (if that helps):

Code: Select all

Revisions:	18720
Folders:	28761 (+19413 deleted)
Files:	352325 (+229157 deleted)
Tree Size:	121.48 GB
Disk Space Needed:	242.96 GB
Database Size:	58.5 GB

Any ideas? We are using Vault Standard, version 5.1.1.19215

[lbauer]

Are these labels applied directly on root $/?

Do you have an idea of how many folders/nodes are in your tree?

[davenovak]

Yes of course these labels were applied directly on root $/, which is why I am asking Vault for those labels.

As to folders/nodes in the tree, I really have no idea short of a manually counting (and Vault itself doesn't apparently answer this question either from what I've seen). Is there, perhaps, a query I can run against the database to get an answer to that question?

Also, I should point out that adding a new label to the $/ does not take that long (something along the lines of 30 seconds), it's just showing the labels that takes forever.

[lbauer]

Maybe a better question would be -- do you have a lot of labels applied to folders under Root? It may be a taking a long time to get information on recursive labels.

[davenovak]

No, we do not have a lot of labels applied to folders under $/. We have a total of 12 labels applied to $/, we have 4 folders directly off of $/, and only one folder contains additional labels (other than those inherited from $/). And the one with additional labels has only 4 additional labels and 2 buried labels found through recursive search.

Therefore, we have something along the lines of 18 total labels in the repository, 12 of which were applied at the root, 4 applied to a child off of the root, and 2 applied deeper in the tree.

So again, why is requesting a show of labels off of $/ so slow? Is there any way to make it faster (such as telling it I don't care about recursive labels and I only want to know about the ones off of $/ or my starting location)?

[davenovak]

Problem Solved

It turns out that 100% of the slowness is due to Show Labels... defaulting to recursive search. A colleague here found the Act on queries recursively checkbox option (under Options --> History/Labels) and un-checking that yields nearly instant label results.

Personally, I think that the default should be not to act recursively (for performance reasons). Then, if someone wants to see recursive labels, they merely need to go to the Recursive Labels tab and check Act recursively.

[lbauer]

Actually, I found we have bug 6499 logged to optimize a recursive Show Labels.

The database function to get the label list builds the entire tree in order to find the objects underneath the tree, so if you have a big tree, it can take some time.

I've logged feature request 15908 to make the Show Labels non-recursive by default. It will be evaluated for an upcoming release of Vault.

[davenovak]

Thanks for the update.

I suspect that our usage of Vault is quite typical, yet we've managed to populate this one repository with over 28,000 folders in just 1 1/2 years. And with that many folders, I can see why a non-optimized query would return very slow results for a recursive search.

Changing Vault to show non-recursive labels by default is clearly "low hanging fruit" here, especially given that I almost never care about recursive labels.

[lbauer]

We appreciate your feedback.