Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Getting recursive data

Author  Topic 

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-09-24 : 11:44:16
Hi have the Stored Proc code below, which gets recursive data for each UserGroupId passed in, if only one UserGroupId is passed in then the SP runs very fast, if i add several then it slows down a lot.

UserGroupId is a pipe deliminated string

the function "SplitCommaList" splits the string into a table, i really need to speed this up but not sure the best way?

Many thanks
Craig







declare @UserGroupId nvarchar(1024) = '1|2|3|4'
declare @parentId bigint = 0



WITH Emp_CTE AS
(
SELECT t.Id, t.ParentId, t.Title, t.Ordinal, t.HelpTopicId, t.IsFolder, t.SmallIcon, t.LargeIcon, ttug.WritePermission, ttug.ReadPermission,
ttug.AuthPermission, ttug.DeletePermission, t.CmsTemplateId, t.IsSystemFolder, t.ContextMenu, t.DateModified,
CASE WHEN t.ToolTip IS NULL THEN t.Title ELSE t.ToolTip END AS ToolTip
FROM Tree t
INNER JOIN TreeToUserGroups ttug ON ttug.TreeId = t.Id
where t.ParentId = @parentId
AND ttug.UserGroupId in (select ResultList from dbo.SplitCommaList(@UserGroupId, '|'))
AND ttug.ReadPermission = 1
AND t.IsDeleted = 0

UNION ALL

SELECT t.Id, t.ParentId, t.Title, t.Ordinal, t.HelpTopicId, t.IsFolder, t.SmallIcon, t.LargeIcon, ttug.WritePermission, ttug.ReadPermission,
ttug.AuthPermission, ttug.DeletePermission, t.CmsTemplateId, t.IsSystemFolder, t.ContextMenu, t.DateModified,
CASE WHEN t.ToolTip IS NULL THEN t.Title ELSE t.ToolTip END AS ToolTip
FROM Tree t
INNER JOIN Emp_CTE ecte ON ecte.Id = t.ParentId
INNER JOIN TreeToUserGroups ttug ON ttug.TreeId = t.Id
WHERE ttug.UserGroupId in (select ResultList from dbo.SplitCommaList(@UserGroupId, '|'))
AND ttug.ReadPermission = 1
AND t.IsDeleted = 0
)

SELECT *
FROM Emp_CTE
GROUP BY Id, ParentId, Title, Ordinal, HelpTopicId, IsFolder, SmallIcon, LargeIcon, WritePermission, ReadPermission,
AuthPermission, DeletePermission, CmsTemplateId, IsSystemFolder, ContextMenu, DateModified,
ToolTip

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 12:11:36
Use a join instead of the IN clause, like shown below (this is only an example, you should make the change in both places).
....
CASE WHEN t.ToolTip IS NULL THEN t.Title
ELSE t.ToolTip
END AS ToolTip
FROM Tree t
INNER JOIN TreeToUserGroups ttug ON ttug.TreeId = t.Id
INNER JOIN dbo.SplitCommaList(@UserGroupId, '|') s ON
ttug.UserGroupId = s.ResultList
WHERE t.ParentId = @parentId

AND ttug.ReadPermission = 1
AND t.IsDeleted = 0
UNION ALL
....


2. Also, what is the algorithm you are using in the SplitCommaList? There are a number of loop based and xml based functions floating around on the net that are very poor performers. If it is one of those, use Jeff Moden's delimitedSplit8K instead. You can find that function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

3. Instead of splitting the string at every recursion, split it in a CTE at the beginning and then use that like shown below:
WITH  SplitString AS
(
SELECT ResultList FROM dbo.SplitCommaList(@UserGroupId, '|')
),
Emp_CTE
AS ( SELECT t.Id ,
t.ParentId ,
t.Title ,
t.Ordinal ,
t.HelpTopicId ,
t.IsFolder ,
t.SmallIcon ,
t.LargeIcon ,
ttug.WritePermission ,
ttug.ReadPermission ,
ttug.AuthPermission ,
ttug.DeletePermission ,
t.CmsTemplateId ,
t.IsSystemFolder ,
t.ContextMenu ,
t.DateModified ,
CASE WHEN t.ToolTip IS NULL THEN t.Title
ELSE t.ToolTip
END AS ToolTip
FROM Tree t
INNER JOIN TreeToUserGroups ttug ON ttug.TreeId = t.Id
INNER JOIN SplitString s ON
ttug.UserGroupId = s.ResultList
WHERE t.ParentId = @parentId

AND ttug.ReadPermission = 1
AND t.IsDeleted = 0
UNION ALL
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-09-24 : 12:49:14
Hi many thanks, but this has gone from around 4 seconds to 18 seconds to run
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 12:57:44
quote:
Originally posted by craigmacca

Hi many thanks, but this has gone from around 4 seconds to 18 seconds to run

Which of the three that I posted caused that?
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-09-24 : 13:01:41
I used to below, i changed the split function to a declared table as that would only need to run once, see the code below, thanks

declare @tempTbl2 table(GroupId bigint)
insert into @tempTbl2
SELECT ResultList FROM dbo.SplitCommaListToBigInt(@UserGroupId, '|')

BEGIN

WITH SplitString AS(
SELECT GroupId FROM @tempTbl2
),
Emp_CTE
AS (
SELECT t.Id, t.ParentId, t.Title, t.Ordinal, t.HelpTopicId, t.IsFolder, t.SmallIcon, t.LargeIcon, ttug.WritePermission, ttug.ReadPermission,
ttug.AuthPermission, ttug.DeletePermission, t.CmsTemplateId, t.IsSystemFolder, t.ContextMenu, t.DateModified,
CASE WHEN t.ToolTip IS NULL THEN t.Title ELSE t.ToolTip END AS ToolTip
FROM Tree t
INNER JOIN TreeToUserGroups ttug ON ttug.TreeId = t.Id
INNER JOIN SplitString s ON ttug.UserGroupId = s.GroupId
WHERE t.ParentId = @parentId AND ttug.ReadPermission = 1 AND t.IsDeleted = 0

UNION ALL


SELECT t.Id, t.ParentId, t.Title, t.Ordinal, t.HelpTopicId, t.IsFolder, t.SmallIcon, t.LargeIcon, ttug.WritePermission, ttug.ReadPermission,
ttug.AuthPermission, ttug.DeletePermission, t.CmsTemplateId, t.IsSystemFolder, t.ContextMenu, t.DateModified,
CASE WHEN t.ToolTip IS NULL THEN t.Title ELSE t.ToolTip END AS ToolTip
FROM Tree t
INNER JOIN Emp_CTE ecte ON ecte.Id = t.ParentId
INNER JOIN TreeToUserGroups ttug ON ttug.TreeId = t.Id
INNER JOIN SplitString s ON ttug.UserGroupId = s.GroupId
WHERE ttug.ReadPermission = 1 AND t.IsDeleted = 0
)


SELECT * FROM Emp_CTE

END
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-09-24 : 13:03:30
ps. runs fast when only one groupid is passed in, if i add 2 groupids then its very slow
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 15:09:23
Even if it runs slow, does it produce the right results? If one groupid runs fast and two is very slow, that could point to logical errors that result in too many rows being returned, or infinite recursions.

If that is the cause, you would need to compare the execution plans to see why they are so drastically different.
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-09-24 : 15:36:28
Yeah the right results are returned, if I pass 10|13 in its slow, if I pass 10 or 13 on its own it's fast. Could I do a separate cte for each groupid and then combine them in a temp table
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 16:45:34
I don't think running the cte for each groupid and then combining is good or scalable. There is something that we are missing. Can you look at the query plan to see where the bottleneck is? Press control-m and run the query. The query plan would tell you the relative costs. Compare to see if the plans are very different.

If you can create DDLs for your tables and some test data that would enable someone to reproduce the problem, that would help a lot. Take a look at this blog on how to do that: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-09-25 : 04:17:26
Hi I have ran the plan whats the best way to get this to you, cant attach files on here?, I cannot give you a dll with data as is sensitive.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-25 : 08:21:14
You don't have to post the actual data. You can make up some examples using ficitious data as long as it is representative and demonstrates the problem.

As for query plans, you can post it as XML; someone might be able to look at it and make suggestions. I cannot download such data on my computer - I am using a work computer.
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2013-09-25 : 09:51:31
ok here is the xml, i have our SQL guy ate work look over it, he cannot see any major issues, if someone could have a look that would be great

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1.1" Build="10.0.5512.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="3" StatementEstRows="1" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0132874" StatementText="insert into @tempTbl2#xD;#xA;SELECT ResultList FROM dbo.SplitCommaListToBigInt(@UserGroupId, '|')#xD;#xA;#xD;" StatementType="INSERT" QueryHash="0x50488B795654C6A7" QueryPlanHash="0xA48CC4DF88A32129">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="216">
<RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="0.0132874">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sequence>
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="1" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<TableValuedFunction>
<DefinedValues />
<Object Database="[NgageCms]" Schema="[dbo]" Table="[SplitCommaListToBigInt]" />
<ParameterList>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(2048),[@UserGroupId],0)">
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Convert DataType="varchar" Length="2048" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@UserGroupId" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="'|'">
<Const ConstValue="'|'" />
</ScalarOperator>
</ParameterList>
</TableValuedFunction>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Insert" NodeId="4" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="0.0132842">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Table="[@tempTbl2]" />
<SetPredicate>
<ScalarOperator ScalarString="[GroupId] = [NgageCms].[dbo].[SplitCommaListToBigInt].[ResultList]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Column="GroupId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[SplitCommaListToBigInt]" Column="ResultList" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0032832">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[SplitCommaListToBigInt]" Column="ResultList" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="6" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[SplitCommaListToBigInt]" Column="ResultList" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[SplitCommaListToBigInt]" Column="ResultList" />
</DefinedValue>
</DefinedValues>
<Object Database="[NgageCms]" Schema="[dbo]" Table="[SplitCommaListToBigInt]" />
</TableScan>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</Sequence>
</RelOp>
<ParameterList>
<ColumnReference Column="@UserGroupId" ParameterRuntimeValue="N'9|10|'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="20.6459" StatementId="5" StatementOptmLevel="FULL" StatementSubTreeCost="1.38848" StatementText="WITH SplitString AS(#xD;#xA;SELECT GroupId FROM @tempTbl2#xD;#xA;),#xD;#xA;Emp_CTE#xD;#xA;AS ( #xD;#xA;SELECT t.Id, t.ParentId, t.Title, t.Ordinal, t.HelpTopicId, t.IsFolder, t.SmallIcon, t.LargeIcon, ttug.WritePermission, ttug.ReadPermission, #xD;#xA;ttug.AuthPermission, ttug.DeletePermission, t.CmsTemplateId, t.IsSystemFolder, t.ContextMenu, t.DateModified,#xD;#xA;CASE WHEN t.ToolTip IS NULL THEN t.Title ELSE t.ToolTip END AS ToolTip#xD;#xA;FROM Tree t#xD;#xA;INNER JOIN TreeToUserGroups ttug ON ttug.TreeId = t.Id#xD;#xA;INNER JOIN SplitString s ON ttug.UserGroupId = s.GroupId#xD;#xA;WHERE t.ParentId = @parentId AND ttug.ReadPermission = 1 AND t.IsDeleted = 0#xD;#xA;#xD;#xA;UNION ALL#xD;#xA;#xD;#xA;#xD;#xA;SELECT t.Id, t.ParentId, t.Title, t.Ordinal, t.HelpTopicId, t.IsFolder, t.SmallIcon, t.LargeIcon, ttug.WritePermission, ttug.ReadPermission, #xD;#xA;ttug.AuthPermission, ttug.DeletePermission, t.CmsTemplateId, t.IsSystemFolder, t.ContextMenu, t.DateModified,#xD;#xA;CASE WHEN t.ToolTip IS NULL THEN t.Title ELSE t.ToolTip END AS ToolTip#xD;#xA;FROM Tree t#xD;#xA;INNER JOIN Emp_CTE ecte ON ecte.Id = t.ParentId#xD;#xA;INNER JOIN TreeToUserGroups ttug ON ttug.TreeId = t.Id#xD;#xA;INNER JOIN SplitString s ON ttug.UserGroupId = s.GroupId#xD;#xA;WHERE ttug.ReadPermission = 1 AND t.IsDeleted = 0#xD;#xA;)#xD;#xA;#xD;#xA;#xD;#xA;SELECT * FROM Emp_CTE#xD;#xA;#xD;" StatementType="SELECT" QueryHash="0xC04B0FADBC6339DB" QueryPlanHash="0x21E8D40A2B2EB672">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="160" CompileTime="28" CompileCPU="28" CompileMemory="1216">
<RelOp AvgRowSize="470" EstimateCPU="1.0323E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20.6459" LogicalOp="Lazy Spool" NodeId="0" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="1.38848">
<OutputList>
<ColumnReference Column="Expr1056" />
<ColumnReference Column="Recr1035" />
<ColumnReference Column="Recr1036" />
<ColumnReference Column="Recr1037" />
<ColumnReference Column="Recr1038" />
<ColumnReference Column="Recr1039" />
<ColumnReference Column="Recr1040" />
<ColumnReference Column="Recr1041" />
<ColumnReference Column="Recr1042" />
<ColumnReference Column="Recr1043" />
<ColumnReference Column="Recr1044" />
<ColumnReference Column="Recr1045" />
<ColumnReference Column="Recr1046" />
<ColumnReference Column="Recr1047" />
<ColumnReference Column="Recr1048" />
<ColumnReference Column="Recr1049" />
<ColumnReference Column="Recr1050" />
<ColumnReference Column="Recr1051" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="147892" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Spool Stack="true">
<RelOp AvgRowSize="470" EstimateCPU="2.06459E-08" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20.6459" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="1.32787">
<OutputList>
<ColumnReference Column="Expr1056" />
<ColumnReference Column="Recr1035" />
<ColumnReference Column="Recr1036" />
<ColumnReference Column="Recr1037" />
<ColumnReference Column="Recr1038" />
<ColumnReference Column="Recr1039" />
<ColumnReference Column="Recr1040" />
<ColumnReference Column="Recr1041" />
<ColumnReference Column="Recr1042" />
<ColumnReference Column="Recr1043" />
<ColumnReference Column="Recr1044" />
<ColumnReference Column="Recr1045" />
<ColumnReference Column="Recr1046" />
<ColumnReference Column="Recr1047" />
<ColumnReference Column="Recr1048" />
<ColumnReference Column="Recr1049" />
<ColumnReference Column="Recr1050" />
<ColumnReference Column="Recr1051" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147892" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Concat>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1056" />
<ColumnReference Column="Expr1053" />
<ColumnReference Column="Expr1055" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1035" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1036" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1037" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1038" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1039" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1040" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1041" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1042" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1043" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1044" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1045" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1046" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1047" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1048" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1049" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1050" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Recr1051" />
<ColumnReference Column="Expr1008" />
<ColumnReference Column="Expr1034" />
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="470" EstimateCPU="2.06459E-07" EstimateIO="0" EstimateRebinds="20.6459" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.06459E-07">
<OutputList>
<ColumnReference Column="Expr1053" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Column="Expr1008" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1053" />
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="346" EstimateCPU="0.0185639" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.323" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.0723875">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
<ColumnReference Column="Expr1008" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Column="GroupId" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[NgageCms].[dbo].[TreeToUserGroups].[UserGroupId] as [ttug].[UserGroupId]=[GroupId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="GroupId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="4" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Column="GroupId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="GroupId" />
</DefinedValue>
</DefinedValues>
<Object Table="[@tempTbl2]" TableReferenceId="1" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="354" EstimateCPU="0.000502684" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="120.259" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0505375">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
<ColumnReference Column="Expr1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="28" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
</OuterReferences>
<RelOp AvgRowSize="345" EstimateCPU="1.53062E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15.3062" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00330037">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Column="Expr1008" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="CASE WHEN [NgageCms].[dbo].[Tree].[ToolTip] as [t].[ToolTip] IS NULL THEN [NgageCms].[dbo].[Tree].[Title] as [t].[Title] ELSE [NgageCms].[dbo].[Tree].[ToolTip] as [t].[ToolTip] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ToolTip" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ToolTip" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="345" EstimateCPU="0.000173837" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15.3062" LogicalOp="Index Seek" NodeId="7" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329884" TableCardinality="15421">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ToolTip" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ToolTip" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
</DefinedValue>
</DefinedValues>
<Object Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Index="[_dta_index_Tree_12_555149023__K2_K20_K1_K3_K4_K8_K7_K5_K6_K11_K9_K13_K15_10]" Alias="[t]" TableReferenceId="1" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsDeleted" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@parentId],0)">
<Identifier>
<ColumnReference Column="ConstExpr1052">
<ScalarOperator>
<Convert DataType="bigint" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@parentId" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.000165643" EstimateIO="0.003125" EstimateRebinds="14.3062" EstimateRewinds="0" EstimateRows="7.85692" LogicalOp="Index Seek" NodeId="11" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0466768" TableCardinality="121189">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="28" ActualEndOfScans="3" ActualExecutions="3" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
</DefinedValue>
</DefinedValues>
<Object Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Index="[IX_Auto_2501]" Alias="[ttug]" TableReferenceId="1" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="TreeId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[NgageCms].[dbo].[Tree].[Id] as [t].[Id]">
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[NgageCms].[dbo].[TreeToUserGroups].[ReadPermission] as [ttug].[ReadPermission]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="470" EstimateCPU="1.73426E-06" EstimateIO="0" EstimateRebinds="20.6459" EstimateRewinds="0" EstimateRows="2" LogicalOp="Assert" NodeId="16" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="1.25548">
<OutputList>
<ColumnReference Column="Expr1055" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Column="Expr1034" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147888" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="470" EstimateCPU="1.73426E-06" EstimateIO="0" EstimateRebinds="20.6459" EstimateRewinds="0" EstimateRows="2" LogicalOp="Inner Join" NodeId="17" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.25548">
<OutputList>
<ColumnReference Column="Expr1055" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Column="Expr1034" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147888" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="Expr1055" />
<ColumnReference Column="Recr1011" />
<ColumnReference Column="Recr1012" />
<ColumnReference Column="Recr1013" />
<ColumnReference Column="Recr1014" />
<ColumnReference Column="Recr1015" />
<ColumnReference Column="Recr1016" />
<ColumnReference Column="Recr1017" />
<ColumnReference Column="Recr1018" />
<ColumnReference Column="Recr1019" />
<ColumnReference Column="Recr1020" />
<ColumnReference Column="Recr1021" />
<ColumnReference Column="Recr1022" />
<ColumnReference Column="Recr1023" />
<ColumnReference Column="Recr1024" />
<ColumnReference Column="Recr1025" />
<ColumnReference Column="Recr1026" />
<ColumnReference Column="Recr1027" />
</OuterReferences>
<RelOp AvgRowSize="470" EstimateCPU="2.06459E-07" EstimateIO="0" EstimateRebinds="20.6459" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="18" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.06459E-07">
<OutputList>
<ColumnReference Column="Expr1055" />
<ColumnReference Column="Recr1011" />
<ColumnReference Column="Recr1012" />
<ColumnReference Column="Recr1013" />
<ColumnReference Column="Recr1014" />
<ColumnReference Column="Recr1015" />
<ColumnReference Column="Recr1016" />
<ColumnReference Column="Recr1017" />
<ColumnReference Column="Recr1018" />
<ColumnReference Column="Recr1019" />
<ColumnReference Column="Recr1020" />
<ColumnReference Column="Recr1021" />
<ColumnReference Column="Recr1022" />
<ColumnReference Column="Recr1023" />
<ColumnReference Column="Recr1024" />
<ColumnReference Column="Recr1025" />
<ColumnReference Column="Recr1026" />
<ColumnReference Column="Recr1027" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1055" />
<ScalarOperator ScalarString="[Expr1054]+(1)">
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1054" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="470" EstimateCPU="2.06459E-07" EstimateIO="0" EstimateRebinds="20.6459" EstimateRewinds="0" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="19" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="2.06459E-07">
<OutputList>
<ColumnReference Column="Expr1054" />
<ColumnReference Column="Recr1011" />
<ColumnReference Column="Recr1012" />
<ColumnReference Column="Recr1013" />
<ColumnReference Column="Recr1014" />
<ColumnReference Column="Recr1015" />
<ColumnReference Column="Recr1016" />
<ColumnReference Column="Recr1017" />
<ColumnReference Column="Recr1018" />
<ColumnReference Column="Recr1019" />
<ColumnReference Column="Recr1020" />
<ColumnReference Column="Recr1021" />
<ColumnReference Column="Recr1022" />
<ColumnReference Column="Recr1023" />
<ColumnReference Column="Recr1024" />
<ColumnReference Column="Recr1025" />
<ColumnReference Column="Recr1026" />
<ColumnReference Column="Recr1027" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="147892" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Spool Stack="true" PrimaryNodeId="0" />
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="470" EstimateCPU="0.0185639" EstimateIO="0" EstimateRebinds="19.6459" EstimateRewinds="0" EstimateRows="10.323" LogicalOp="Inner Join" NodeId="23" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.25548">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
<ColumnReference Column="Expr1034" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147888" ActualEndOfScans="147892" ActualExecutions="147892" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Column="GroupId" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[NgageCms].[dbo].[TreeToUserGroups].[UserGroupId] as [ttug].[UserGroupId]=[GroupId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="GroupId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="15" EstimateCPU="7.96E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="19.6459" EstimateRows="1" LogicalOp="Table Scan" NodeId="24" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.00484692" TableCardinality="0">
<OutputList>
<ColumnReference Column="GroupId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="443676" ActualEndOfScans="147892" ActualExecutions="147892" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="GroupId" />
</DefinedValue>
</DefinedValues>
<Object Table="[@tempTbl2]" TableReferenceId="2" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="478" EstimateCPU="0.000502684" EstimateIO="0" EstimateRebinds="19.6459" EstimateRewinds="0" EstimateRows="120.259" LogicalOp="Inner Join" NodeId="25" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.867303">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
<ColumnReference Column="Expr1034" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="757714" ActualEndOfScans="147892" ActualExecutions="147892" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
</OuterReferences>
<RelOp AvgRowSize="469" EstimateCPU="1.53062E-06" EstimateIO="0" EstimateRebinds="19.6459" EstimateRewinds="0" EstimateRows="15.3062" LogicalOp="Compute Scalar" NodeId="26" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0637723">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
<ColumnReference Column="Expr1034" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1034" />
<ScalarOperator ScalarString="CASE WHEN [NgageCms].[dbo].[Tree].[ToolTip] as [t].[ToolTip] IS NULL THEN [NgageCms].[dbo].[Tree].[Title] as [t].[Title] ELSE [NgageCms].[dbo].[Tree].[ToolTip] as [t].[ToolTip] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ToolTip" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ToolTip" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="469" EstimateCPU="0.000173837" EstimateIO="0.003125" EstimateRebinds="19.6459" EstimateRewinds="0" EstimateRows="15.3062" LogicalOp="Index Seek" NodeId="27" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0637407" TableCardinality="15421">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ToolTip" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="75940" ActualEndOfScans="147892" ActualExecutions="147892" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Title" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Ordinal" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="SmallIcon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="LargeIcon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsFolder" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="HelpTopicId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsSystemFolder" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ToolTip" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="CmsTemplateId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ContextMenu" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="DateModified" />
</DefinedValue>
</DefinedValues>
<Object Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Index="[_dta_index_Tree_12_555149023__K2_K20_K1_K3_K4_K8_K7_K5_K6_K11_K9_K13_K15_10]" Alias="[t]" TableReferenceId="2" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="ParentId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="IsDeleted" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Recr1011]">
<Identifier>
<ColumnReference Column="Recr1011" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.000165643" EstimateIO="0.003125" EstimateRebinds="315.01" EstimateRewinds="0" EstimateRows="7.85692" LogicalOp="Index Seek" NodeId="31" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.791961" TableCardinality="121189">
<OutputList>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="757714" ActualEndOfScans="75940" ActualExecutions="75940" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="UserGroupId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="WritePermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="AuthPermission" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="DeletePermission" />
</DefinedValue>
</DefinedValues>
<Object Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Index="[IX_Auto_2501]" Alias="[ttug]" TableReferenceId="2" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="TreeId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[NgageCms].[dbo].[Tree].[Id] as [t].[Id]">
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[Tree]" Alias="[t]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[NgageCms].[dbo].[TreeToUserGroups].[ReadPermission] as [ttug].[ReadPermission]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NgageCms]" Schema="[dbo]" Table="[TreeToUserGroups]" Alias="[ttug]" Column="ReadPermission" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Hash>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1055]>(100) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1055" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(100)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</Concat>
</RelOp>
</Spool>
</RelOp>
<ParameterList>
<ColumnReference Column="@parentId" ParameterRuntimeValue="(1)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Go to Top of Page
   

- Advertisement -