Big performance problems with Vault 3.0.1

[valik]

OK. Did you get a chance to run the queries w/ SHOWPLAN_ALL set to ON?

I'm going to look at this again to see if I can find a way to improve performance, but I'm going to need a day or two to hash things out. In the mean time, if you could get the execution plan, I can see if a modification to the query will improve things.

Thanks again,

OK I did understand it now, see the attachment. I'll be also off for two days

Valik

[valik]

OK. Did you get a chance to run the queries w/ SHOWPLAN_ALL set to ON?

I'm going to look at this again to see if I can find a way to improve performance, but I'm going to need a day or two to hash things out. In the mean time, if you could get the execution plan, I can see if a modification to the query will improve things.

Thanks again,

Today sunday I'll be able to answer your questions.

Valik

[jclausius]

Valik:

The execution plan looks like it is using the tables where I would expect it to.

If it is OK, I want to make sure the problem is in the JOIN not the gathering of locked files. If you replace the last query with:

SELECT lf.* FROM sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf

And then replace that query with this one:

SELECT lf.* FROM sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objid = lf.objid) AND (v.objversion = lf.lastobjversion)


Can you give a rough time estimate for each run of the query?

[jclausius]

Valik:

After you've tested the join statement from the last post, I have four more tests:

Can you run the queries, but again ( as above ) replace the next to last statements with

Test #1:

Code: Select all

SELECT DISTINCT li.objid, li.hostname, li.locktype, li.folderobjid, li.lockwhen, 
   li.fullpath, li.localpath, li.comment, li.miscinfo, li.userid, u.login, v.objversion, li.colistid 
   FROM #tbltreerevfolders trf INNER JOIN sgvault.dbo.tblfolderentries fe ON (fe.folderobjverid = trf.objverid),
sgvault.dbo.tblcheckoutlistitems li 
INNER JOIN sgvault.dbo.tblfsobjects o ON ((o.repid = @@repid_param) AND (o.objid = li.objid)) 
INNER JOIN sgvault.dbo.tblfsobjectversions vlatest ON (vlatest.objid = li.objid) AND (vlatest.objversion = o.lastobjversion)
INNER JOIN sgvault.dbo.tblusers u ON (u.userid = li.userid) 
INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = li.objverid)
WHERE (li.colistid = @@listid) AND 
(trf.treelevel >= 0) AND (trf.securityrights <> 0) AND (fe.objverid = vlatest.objverid)

Next, try it out but use Test #2:

Code: Select all

SELECT li.objid, li.hostname, li.locktype, li.folderobjid, li.lockwhen, 
   li.fullpath, li.localpath, li.comment, li.miscinfo, li.userid, u.login, v.objversion, li.colistid 
   FROM #tbltreerevfolders trf INNER JOIN sgvault.dbo.tblfolderentries fe ON (fe.folderobjverid = trf.objverid),
sgvault.dbo.tblcheckoutlistitems li 
INNER JOIN sgvault.dbo.tblfsobjects o ON ((o.repid = @@repid_param) AND (o.objid = li.objid)) 
INNER JOIN sgvault.dbo.tblfsobjectversions vlatest ON (vlatest.objid = li.objid) AND (vlatest.objversion = o.lastobjversion)
INNER JOIN sgvault.dbo.tblusers u ON (u.userid = li.userid) 
INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = li.objverid)
WHERE (li.colistid = @@listid) AND 
(trf.treelevel >= 0) AND (trf.securityrights <> 0) AND (fe.objverid = vlatest.objverid) 

Test #3:

Code: Select all

SELECT DISTINCT li.objid, li.hostname, li.locktype, li.folderobjid, li.lockwhen,
   li.fullpath, li.localpath, li.comment, li.miscinfo, li.userid, u.login, v.objversion, li.colistid
   FROM sgvault.dbo.tblcheckoutlistitems li
   INNER JOIN sgvault.dbo.tblusers u ON (u.userid = li.userid)
   INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = li.objverid)
   WHERE (li.colistid = @@listid) 
   AND EXISTS 
   ( SELECT trf.treelevel FROM 
      sgvault.dbo.tblfsobjects o INNER JOIN sgvault.dbo.tblfsobjectversions vlatest ON (vlatest.objid = o.objid) AND (vlatest.objversion = o.lastobjversion), 
      #tbltreerevfolders trf, sgvault.dbo.tblfolderentries fe 
     WHERE ((o.repid = @@repid_param) AND (o.objid = li.objid)) AND 
         ((trf.treelevel >= 0) AND (trf.securityrights > 0)) AND 
         ((fe.folderobjverid = trf.objverid) AND (fe.objverid = vlatest.objverid)) )

Finally Test #4:

Code: Select all

SELECT DISTINCT li.objid, li.hostname, li.locktype, li.folderobjid, li.lockwhen,
   li.fullpath, li.localpath, li.comment, li.miscinfo, li.userid, u.login, v.objversion, li.colistid
   FROM sgvault.dbo.tblcheckoutlistitems li
   INNER JOIN sgvault.dbo.tblusers u ON (u.userid = li.userid)
   INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = li.objverid)
   INNER JOIN sgvault.dbo.tblfsobjects o ON ((o.repid = @@repid_param) AND (o.objid = li.objid))
         INNER JOIN sgvault.dbo.tblfsobjectversions vlatest ON (vlatest.objid = o.objid) AND (vlatest.objversion = o.lastobjversion)
   WHERE (li.colistid = @@listid) 
   AND EXISTS 
      ( SELECT trf.treelevel FROM 
         #tbltreerevfolders trf INNER JOIN sgvault.dbo.tblfolderentries fe ON (fe.folderobjverid = trf.objverid) AND (fe.objverid = vlatest.objverid) 
         WHERE (trf.treelevel >= 0) AND (trf.securityrights > 0) )

What are the timings for each of these tests?

Thanks,

[ablankert]

If you replace the last query with:

SELECT lf.* FROM sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf

And then replace that query with this one:

SELECT lf.* FROM sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objid = lf.objid) AND (v.objversion = lf.lastobjversion)

I am taking over from Valik and tried to reconstruct the script from previous posts, but now I cannot find the above query to be replaced in my version of the script.

Could you send the complete updated script to me again?

[ablankert]

After some testing, I think I figured out what I should do.

I replaced the large 14 seconds consuming query:

SELECT DINSTINCT ....

by

SELECT lf.* FROM sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf

after this the complete script executes allmost instantly.

Then I replaced this query by

SELECT lf.* FROM sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objid = lf.objid) AND (v.objversion = lf.lastobjversion)

after which the script still runs allmost instantly.

[jclausius]

Yes, I was just about to post... Can you try the same script, but use the four different tests for the SELECT statements. What is the timing of those?


Also, if you would please, could you add:

SET STATISTICS IO ON

< PLACE TEST SQL STATEMENT HERE >

SET STATISTICS IO OFF


This will produce data on the bottom messages tab within Query Analyzer. I'd like to see the impact of each type of statement.

[ablankert]

I ran the tests and got the following results:

Original query: 15 seconds

TEST #1: about 3 seconds
TEST #2: about 5 seconds
TEST #3: about 4 seconds
TEST #4: about 3 seconds

[jclausius]

OK... That's good to know.

Anyway, if you get a chance to get the I/O timings, I'd like to see those as well.

[ablankert]

With
SET STATISTICS IO
the results are:


Original query:
Table '#tbltreerevfolders__________________________________________________________________________________________________0000000001B2'. Scan count 1, logical reads 58, physical reads 0, read-ahead reads 0.
Table 'tblfolderentries'. Scan count 2654, logical reads 8240, physical reads 0, read-ahead reads 0.
Table 'tblfsobjectversions'. Scan count 2654, logical reads 7973, physical reads 0, read-ahead reads 0.
Table '#6CE1E2A8'. Scan count 1, logical reads 147, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 14913 ms.


TEST #1:
Table 'tblfolderentries'. Scan count 2654, logical reads 8588, physical reads 0, read-ahead reads 0.
Table 'tblfsobjectversions'. Scan count 5308, logical reads 16653, physical reads 0, read-ahead reads 0.
Table 'tblcheckoutlistitems'. Scan count 1, logical reads 140, physical reads 0, read-ahead reads 0.
Table 'tblfsobjects'. Scan count 1, logical reads 258, physical reads 0, read-ahead reads 0.
Table 'tblusers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table '#tbltreerevfolders__________________________________________________________________________________________________0000000001B2'. Scan count 1, logical reads 58, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 171 ms, elapsed time = 285 ms.


TEST #2:
Table 'tblfolderentries'. Scan count 2654, logical reads 8588, physical reads 0, read-ahead reads 0.
Table 'tblfsobjectversions'. Scan count 5308, logical reads 16653, physical reads 0, read-ahead reads 0.
Table 'tblcheckoutlistitems'. Scan count 1, logical reads 140, physical reads 0, read-ahead reads 0.
Table 'tblfsobjects'. Scan count 1, logical reads 258, physical reads 0, read-ahead reads 0.
Table 'tblusers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table '#tbltreerevfolders__________________________________________________________________________________________________0000000001B2'. Scan count 1, logical reads 58, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 3674 ms.

TEST #3:
Table 'tblfsobjects'. Scan count 2, logical reads 259, physical reads 0, read-ahead reads 0.
Table 'tblcheckoutlistitems'. Scan count 2, logical reads 140, physical reads 0, read-ahead reads 0.
Table 'tblfsobjectversions'. Scan count 5308, logical reads 16689, physical reads 0, read-ahead reads 0.
Table 'tblfolderentries'. Scan count 5048, logical reads 21509, physical reads 0, read-ahead reads 0.
Table '#tbltreerevfolders__________________________________________________________________________________________________0000000001B2'. Scan count 2, logical reads 58, physical reads 0, read-ahead reads 0.
Table 'tblusers'. Scan count 2654, logical reads 5308, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 205 ms, elapsed time = 2671 ms.


TEST #4:
Table 'tblfsobjects'. Scan count 2, logical reads 259, physical reads 0, read-ahead reads 0.
Table 'tblcheckoutlistitems'. Scan count 2, logical reads 140, physical reads 0, read-ahead reads 0.
Table 'tblfsobjectversions'. Scan count 5308, logical reads 16689, physical reads 0, read-ahead reads 0.
Table 'tblfolderentries'. Scan count 5048, logical reads 21506, physical reads 0, read-ahead reads 0.
Table '#tbltreerevfolders__________________________________________________________________________________________________0000000001B2'. Scan count 2, logical reads 58, physical reads 0, read-ahead reads 0.
Table 'tblusers'. Scan count 2654, logical reads 5308, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 2447 ms.

[jclausius]

OK... Based on this information, I've created a test stored procedure. I just sent it through a private message on this forum.

Just run the Query against your existing database. It will replace one of the stored procedures with an updated on. Let me know if that solved the problem.

Thanks,

[jclausius]

An update on this. Over the last three days I've spent some time optimizing for folder security enabled repositories' checkout lists.

On Valik's machine configuration I was able to improve the stored procedure time by about 75-80%. While this is a big improvement overall, the stored procedure still seems about 33-40% slower than our own internal testing on larger test repositories.

I want to thank Valik / Anne for their help throughout this thread.