Moderator: SourceGear
Code: Select all
SET STATISTICS TIME ON
SET STATISTICS IO ON
DECLARE @@currenttxid bigint
SELECT @@currenttxid = currenttxid FROM sgvault.dbo.tblrepositories WHERE repid = 2
SELECT trf.treelevel, trf.fullpathhash, trf.parentpathhash, v.objid, v.objverid
FROM sgvault.dbo.ufngettreerevisionfolders(2, @@currenttxid) trf
INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = trf.objverid)
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
Should I turn Folder Security again on before performing above statements?jclausius wrote:Let's start at the beginning for checkout list security. Let's see how fast the current tree builds:
Again, I'm not interested in the results, just the message output.Code: Select all
SET STATISTICS TIME ON SET STATISTICS IO ON DECLARE @@currenttxid bigint SELECT @@currenttxid = currenttxid FROM sgvault.dbo.tblrepositories WHERE repid = 2 SELECT trf.treelevel, trf.fullpathhash, trf.parentpathhash, v.objid, v.objverid FROM sgvault.dbo.ufngettreerevisionfolders(2, @@currenttxid) trf INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = trf.objverid) SET STATISTICS IO OFF SET STATISTICS TIME OFF GO
With Folder Security turned OFF:jclausius wrote:It doesn't matter for this query, since we are directly accessing the database.
However, it might not be a bad idea to turn it back on, so we don't forget later.
Code: Select all
DECLARE @@repid_param [int],
@@userid_param [int],
@@currenttxid [bigint],
@@rowsaffected [int],
@@treelevel [int],
@@leaflevel [int],
@@defaultrights [int],
@@listid [bigint]
DECLARE @@tmpsecurityrights TABLE
( objid [bigint] NOT NULL,
type [nchar](1) COLLATE DATABASE_DEFAULT NOT NULL,
securityrights [int] NOT NULL,
PRIMARY KEY (objid, type)
)
CREATE TABLE #tbltreerevfolders
( treelevel [int] NOT NULL,
fullpathhash [binary](16) NOT NULL,
parentpathhash [binary](16) NOT NULL,
objid [bigint] NOT NULL,
objverid [bigint] NOT NULL,
securityrights [int] NOT NULL DEFAULT -1,
PRIMARY KEY (treelevel, fullpathhash, parentpathhash, objid)
)
SELECT @@repid_param = 2, -- Repository 2
@@userid_param = 21 -- Valik's ID
SELECT @@currenttxid = currenttxid, @@listid = currentcolistid FROM sgvault.dbo.tblrepositories WHERE repid = @@repid_param
SET STATISTICS TIME ON
PRINT N'Getting Tree'
INSERT INTO #tbltreerevfolders (treelevel, fullpathhash, parentpathhash, objid, objverid)
SELECT trf.treelevel, trf.fullpathhash, trf.parentpathhash, v.objid, v.objverid
FROM sgvault.dbo.ufngettreerevisionfolders(@@repid_param, @@currenttxid) trf
INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = trf.objverid)
PRINT N'Get Security Rights'
INSERT INTO @@tmpsecurityrights (objid, type, securityrights)
SELECT DISTINCT objid, type, securityrights + CASE WHEN ( type = N'U' ) THEN 100 ELSE 0 END FROM sgvault.dbo.ufngetusersecurityrights(@@repid_param, @@userid_param)
PRINT N'Apply rights to tree'
UPDATE #tbltreerevfolders SET #tbltreerevfolders.securityrights = r.securityrights FROM @@tmpsecurityrights r
WHERE (#tbltreerevfolders.treelevel >= 0) AND (#tbltreerevfolders.objid = r.objid)
IF ( @@ROWCOUNT > 0 )
BEGIN
-- apply security settings down the tree (inherited rights)
SET @@treelevel = 1
SELECT @@leaflevel = MAX(treelevel) + 1 FROM #tbltreerevfolders WHERE (treelevel >= 0)
WHILE ( @@treelevel < @@leaflevel )
BEGIN
UPDATE trf SET trf.securityrights = tprev.securityrights FROM
#tbltreerevfolders trf INNER JOIN
#tbltreerevfolders tprev ON ((tprev.treelevel=(@@treelevel-1)) AND (tprev.fullpathhash=trf.parentpathhash) )
WHERE (trf.treelevel = @@treelevel) AND (trf.securityrights < tprev.securityrights)
SET @@treelevel = @@treelevel + 1
END -- WHILE
PRINT N'Finished applying rights'
END
PRINT N'Default Rights'
SELECT @@defaultrights = COALESCE(securityrights, 0) FROM @@tmpsecurityrights WHERE (objid = 0) AND (type = N'D')
UPDATE #tbltreerevfolders SET securityrights = CASE WHEN (securityrights = -1) THEN @@defaultrights ELSE (securityrights - 100) END WHERE (treelevel >= 0) AND ((securityrights = -1) OR (securityrights >= 100))
PRINT N'Join to get rights'
SELECT DISTINCT lf.objid, lf.hostname, lf.locktype, lf.folderobjid, lf.lockwhen,
lf.fullpath, lf.localpath, lf.comment, lf.miscinfo, lf.userid, lf.login, lf.objversion, lf.colistid
FROM #tbltreerevfolders trf INNER JOIN sgvault.dbo.tblfolderentries fe ON (fe.folderobjverid = trf.objverid),
sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf INNER JOIN
sgvault.dbo.tblfsobjectversions v ON (v.objid = lf.objid) AND (v.objversion = lf.lastobjversion)
WHERE (trf.treelevel >= 0) AND (trf.securityrights <> 0) AND (fe.objverid = v.objverid)
SET STATISTICS TIME OFF
DROP TABLE #tbltreerevfolders Jeff,jclausius wrote:Valik:
I have some personal stuff that needs my attention. I'll be away for a while.
If you saw my last post, can you post the results? I'll check this out for any potential bottleneck later tonight.
Thanks,
It sounds like you're getting beat up from all sides. But, I agree with your assessment. Let's take these things one at a time. Once we determine what needs to be done to improve checkout list performance, we can start looking at other problems.Valik wrote:We are getting everyday more complaints about the performance and there are also some issues with the IDE (Visual Studio 6 and Visual Studio .Net) and some other stuff but I think is beter to first improve the performance.
I'll take this into account, but our experience with VSS in combination with SourceOffsite that it did not matter how many files where checkout. I would recommend this to be a improvement for a future release of Vault.jclausius wrote:Valik:
Sorry. I wasn't specific enough on my last post. Like the other queries, can you send me the messages output? At this point, the data is not important as the statistics from the query.
I should note from examining the data, I'd like to recommend to people to only check out files they think they will be modifying. Now, I haven't examined your I/O data from the query, so I'm not all that sure this is the problem. It was just something I wanted to bring to your attention.
The data looks like people are checking out more than what will be changed. The explanation for this follows - Currently, the Vault architecture deals with entire locked file lists for a repository. So when things are calculated, the entire lock list is used. Unfortunately, the more items are found in the checkout list the more processing time is required for working with the list. By only checking out a file that is intended to be modified, the list can be kept somewhat small.
Yes we will do after this performance problem is solvedjclausius wrote:It sounds like you're getting beat up from all sides. But, I agree with your assessment. Let's take these things one at a time. Once we determine what needs to be done to improve checkout list performance, we can start looking at other problems.Valik wrote:We are getting everyday more complaints about the performance and there are also some issues with the IDE (Visual Studio 6 and Visual Studio .Net) and some other stuff but I think is beter to first improve the performance.
Here the results:jclausius wrote:Unfortunately, I have some family engagements in the morning / afternoon. I should be able to take a look later in the evening. BTW, I'm GMT -6, so I think you should be about 6 or 7 hours ahead of me.
Code: Select all
-- REMOVE THIS FROM THE ENTIRE QUERY -
SELECT DISTINCT lf.objid, lf.hostname, lf.locktype, lf.folderobjid, lf.lockwhen,
lf.fullpath, lf.localpath, lf.comment, lf.miscinfo, lf.userid, lf.login, lf.objversion, lf.colistid
FROM #tbltreerevfolders trf INNER JOIN sgvault.dbo.tblfolderentries fe ON (fe.folderobjverid = trf.objverid),
sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf INNER JOIN
sgvault.dbo.tblfsobjectversions v ON (v.objid = lf.objid) AND (v.objversion = lf.lastobjversion)
WHERE (trf.treelevel >= 0) AND (trf.securityrights <> 0) AND (fe.objverid = v.objverid)If I remove the above mentioned statement then it takes less than one second otherwise it takes about 15 seconds.jclausius wrote:OK. This seemed to take 15 seconds. Is that correct?
What happens if you remove the last SQL Statementfrom the entire query. Does it still take 15 seconds? I'm hoping it does not. That means I'm reading the output correctly.Code: Select all
-- REMOVE THIS FROM THE ENTIRE QUERY - SELECT DISTINCT lf.objid, lf.hostname, lf.locktype, lf.folderobjid, lf.lockwhen, lf.fullpath, lf.localpath, lf.comment, lf.miscinfo, lf.userid, lf.login, lf.objversion, lf.colistid FROM #tbltreerevfolders trf INNER JOIN sgvault.dbo.tblfolderentries fe ON (fe.folderobjverid = trf.objverid), sgvault.dbo.ufngetlockedfiles(@@repid_param, @@listid) lf INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objid = lf.objid) AND (v.objversion = lf.lastobjversion) WHERE (trf.treelevel >= 0) AND (trf.securityrights <> 0) AND (fe.objverid = v.objverid)
this statement is also in the query you mentioned before, do I have to run it separately?jclausius wrote:If the last statement is causing the 14-20 seconds, let's take a look at the execution plan for these statements:
1) Run - CREATE TABLE #tbltreerevfolders
( treelevel [int] NOT NULL,
fullpathhash [binary](16) NOT NULL,
parentpathhash [binary](16) NOT NULL,
objid [bigint] NOT NULL,
objverid [bigint] NOT NULL,
securityrights [int] NOT NULL DEFAULT -1,
PRIMARY KEY (treelevel, fullpathhash, parentpathhash, objid)
)
OKjclausius wrote:2) Run - SET SHOWPLAN_ALL ON
Which query you mean, sorry I'm getting confused.jclausius wrote:3) Run the query
The result in the message output you mean?jclausius wrote:4) Save / Paste the results ( as before )
OKjclausius wrote:5) Run - SET SHOWPLAN_ALL OFF
this statement is also in the query you mentioned before, do I have to run it separately?jclausius wrote:6) Run - DROP TABLE #tbltreerevfolders