Stored Procedure runs slow, but same code in query window runs fast.

[campbeltr]

I am having trouble with a stored procedure running very slowly in comparison to the same code running very quickly in a separate query window. The Stored Procedure runs in 2 1/2 minutes, but the direct query runs in 1 second.

Some notes:

- I am using SQL Server 2005.
- The query is NOT using cursors.
- I have double and triple checked that both the Stored Procedure and the query are in fact executing the same code.
- I have explored "parameter sniffing", and related solutions to no avail.

Does anyone here have any idea what could be causing this discrepancy?

 

[macleod1021]

Well...you might want to provide a copy of the SP and the code you're using. Kinda hard to look at nothing

One thing you can do is run Profiler and see exactly what's being sent to the server. That typically helps tremendously when find performance problems.

im in ur stakz, overflowin ur heapz!

 

[SQLDenis]

show the execution plans of both

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[campbeltr]

The actual stored procedure... (obtained via Modify)

ALTER        PROCEDURE [dbo].[SubCatInfo]

@nId 		int,
@nUserLang 	int

AS

declare @uAgid 	uniqueidentifier

select @uAgid = AuditGlobalId  from  Audit where WebAuditId = @nId

CREATE  TABLE  #GetCats(	 nOrder  		int NOT NULL,
				 AuditGlobalId  		uniqueIdentifier NOT NULL,
				 CategoryGlobalId 	uniqueIdentifier NOT NULL,
				 ParentId  		uniqueIdentifier NULL,
				 CategoryIdentity  	int NOT NULL,
				 CategoryUniqueID  	nvarchar(50) NULL,
				 CategoryReference  	nvarchar(50) NULL,
				 CategorySequence  	int NOT NULL,
				 CategoryView 		int NOT NULL,
				 CategoryAnswer  	bit NULL,
				 ReAuditCategory 	int NULL,
				 Archived 		bit NULL,
				 Deleted 		bit NULL,
				 CategoryWeight 	int null,
				 nLevel  		int NOT NULL,
				 CategoryViewLabel  	nvarchar(100) NULL, 
				 nSubCats  		int NULL,
				 nQuestions  		int NULL,
				 CategoryName  		nvarchar(250) NULL,
				 CatWeightsOk 		int NULL,
				 CategoryDueDates	datetime NULL)


DECLARE @SubCats TABLE  (	nOrder  			int NOT NULL,
				AuditGlobalId 		uniqueIdentifier NOT NULL,
				CategoryGlobalId  	uniqueIdentifier NOT NULL,
				ParentId  		uniqueIdentifier NULL,
				CategoryIdentity  	int NOT NULL,
				CategorySequence  	int NOT NULL,
				CategoryName  		nvarchar(250) NULL,
				CategoryView 		int NOT NULL,
				ParentIdentity 		int NULL,
				CategoryAnswer  	bit NULL,
				ReAuditCategory 	int NULL,
				nLevel  			int NOT NULL,
				nSubCats  		int NULL,
				nQuestions  		int NULL,
				nCatCompleted  		bit NULL,
				nSubCatsCompleted  	bit NULL)


declare @IQ table
(
	QuestionIdentity	 int,
	DynamicInclude bit,
	QuestionResult 	int,
	Triggered bit,
	activated bit,
	AuditGlobalID uniqueidentifier,
	CategoryGlobalID uniqueidentifier
)


insert 	#GetCats 
exec 	ImpGetCategories @uAgid, @nUserLang

insert 	@SubCats
select 	nOrder, AuditGlobalId, CategoryGlobalId, ParentId, CategoryIdentity, CategorySequence, CategoryName, 
	CategoryView, NULL, CategoryAnswer, ReAuditCategory, nLevel, nSubCats, nQuestions, 0, 0
from 	#GetCats


insert into @IQ
select 	q.QuestionIdentity,
	0, -- DynamicInclude
	qrh.DynamicInclude,-- QuestionREsultn
	CASE  when cql.QuestionGlobalID IS NULL then 0 ELSE 1 end as triggered,
	CASE  when Activated.QActivated IS NULL then 0 When Activated.QActivated = 0 then 0 else 1 end as Activated,
	q.AuditGlobalID,
	q.CategoryGlobalID
from question q
inner join  Audit ma	on 
	ma.AuditGlobalID = q.AuditGlobalID
	AND ma.WebAuditID = @nId -- Restrict to only the questions that are in the webAudit
left join  QuestionResultHistory 	qrh on qrh.QuestionIdentity = q.QuestionIdentity
	and qrh.WebAuditID = @nID
	and qrh.Timestamp = (select Max(timeSTamp) as timestamp 
						from  QuestionREsultHistory 
						where WebAuditID = @nID 
							and QuestionIdentity = qrh.questionIdentity)						
inner join category c on 
	c.CategoryGlobalId = q.CategoryGlobalId
left join 
(
	(select	aq.questionGlobalID, count(*) as QActivated
	from 	question aq
		inner join ChoiceQuestionList cql
			on cql.QuestionGlobalId = aq.QuestionGlobalId
		inner join Choice tc
			on tc.choiceglobalid = cql.choiceglobalid
		inner join question tq
			on tq.questionglobalid = tc.questionglobalid
		inner join  QuestionResultHistory tqrh
			on tqrh.QuestionIdentity = tq.QuestionIdentity
			and tqrh.webauditid = @nId
			and tqrh.DynamicInclude = 1
			and tqrh.choiceidentity = tc.choiceidentity
		inner join
		(
			select Max(timeSTamp) as timestamp, QuestionIdentity from  QuestionREsultHistory where WebAuditID = @nId group by QuestionIdentity
		) mts on mts.TimeStamp = tqrh.Timestamp
		  and mts.QuestionIdentity = tqrh.QuestionIdentity
	Group by aq.QuestionGlobalID)
) Activated on Activated.QuestionGlobalID = q.QuestionGlobalID									
left join ChoiceQuestionList cql on 
	cql.QuestionGlobalID = q.QuestionGlobalID

update 	@IQ
set 	dynamicinclude = 1
where	(questionresult = 1 and triggered = 0)
	or (triggered = 0)
	or (triggered = 1 and activated = 1)

update sc
set nCatCompleted = 1
from @SubCats SC
left join 
(
		select 	q.CategoryGlobalID, count(*) as catCount
		from 	Question q
		join @iq iq 
			on q.AuditGlobalID = iq.AuditGlobalID
			and q.CategoryGlobalid = iq.CategoryGlobalID
			and q.QuestionIdentity = iq.QuestionIdentity
			and dynamicInclude = 1
		where 	q.Archived = 0 and q.Deleted = 0
				and q.ReAuditQuestion <= 2
				and q.required = 1
		group by q.CategoryGlobalID
) total on total.CategoryGlobalID = sc.CategoryGlobalID
left join
(
	select 	CategoryGlobalID, count(mqrh.QuestionIdentity) as catCount 
	from	 QuestionResult mqr,  QuestionResultHistory mqrh,@IQ iq
	where 	mqr.WebAuditId = @nId 
		and (select required from question where questionidentity = mqr.questionidentity) = 1
		--and mqr.CategoryIdentity = sc.CategoryIdentity
		and mqr.WebAuditId = mqrh.WebAuditId
		and mqr.QuestionIdentity = mqrh.QuestionIdentity
		and (ChoiceIdentity is not null or Len(ChoiceText) > 0)
		and IQ.dynamicInclude = 1
		and timestamp = (	select 	max(timestamp)
							from 	 QuestionResultHistory
							where 	WebAuditId = mqrh.WebAuditId
							and QuestionIdentity = mqrh.QuestionIdentity)
		and iq.QuestionIdentity = mqrh.QuestionIdentity
	group by CategoryGlobalID
) TotalComplete on 	total.CategoryGlobalID = totalComplete.CategoryGlobalID				
where isNull(total.CatCount,0) = isNUll(totalComplete.CatCount,0)

update 	s1
set 	ParentIdentity = s2.CategoryIdentity 
from 	@SubCats s1, @SubCats s2
where 	s1.ParentId = s2.CategoryGlobalId

Update 	@SubCats
Set 	nSubCatsCompleted = 1
where 	nSubCats = 0

update 	s
set 	nSubCatsCompleted = 1
from	@SubCats s
where 	s.nSubCats > 0
	and (select count(*) from @SubCats where parentId = s.CategoryGlobalId and nCatCompleted = 1 and nSubCatsCompleted = 1) = s.nSubCats
		
select	*
from	@SubCats
order 	by nOrder

DROP TABLE #GetCats

The code in a new query window...

declare @nId 		int
declare @nUserLang 	int

set @nId = 13067
set @nUserLang = 1

declare @uAgid 	uniqueidentifier

select @uAgid = AuditGlobalId  from  Audit where WebAuditId = @nId

CREATE  TABLE  #GetCats(	 nOrder  		int NOT NULL,
				 AuditGlobalId  		uniqueIdentifier NOT NULL,
				 CategoryGlobalId 	uniqueIdentifier NOT NULL,
				 ParentId  		uniqueIdentifier NULL,
				 CategoryIdentity  	int NOT NULL,
				 CategoryUniqueID  	nvarchar(50) NULL,
				 CategoryReference  	nvarchar(50) NULL,
				 CategorySequence  	int NOT NULL,
				 CategoryView 		int NOT NULL,
				 CategoryAnswer  	bit NULL,
				 ReAuditCategory 	int NULL,
				 Archived 		bit NULL,
				 Deleted 		bit NULL,
				 CategoryWeight 	int null,
				 nLevel  		int NOT NULL,
				 CategoryViewLabel  	nvarchar(100) NULL, 
				 nSubCats  		int NULL,
				 nQuestions  		int NULL,
				 CategoryName  		nvarchar(250) NULL,
				 CatWeightsOk 		int NULL,
				 CategoryDueDates	datetime NULL)


DECLARE @SubCats TABLE  (	nOrder  			int NOT NULL,
				AuditGlobalId 		uniqueIdentifier NOT NULL,
				CategoryGlobalId  	uniqueIdentifier NOT NULL,
				ParentId  		uniqueIdentifier NULL,
				CategoryIdentity  	int NOT NULL,
				CategorySequence  	int NOT NULL,
				CategoryName  		nvarchar(250) NULL,
				CategoryView 		int NOT NULL,
				ParentIdentity 		int NULL,
				CategoryAnswer  	bit NULL,
				ReAuditCategory 	int NULL,
				nLevel  			int NOT NULL,
				nSubCats  		int NULL,
				nQuestions  		int NULL,
				nCatCompleted  		bit NULL,
				nSubCatsCompleted  	bit NULL)


declare @IQ table
(
	QuestionIdentity	 int,
	DynamicInclude bit,
	QuestionResult 	int,
	Triggered bit,
	activated bit,
	AuditGlobalID uniqueidentifier,
	CategoryGlobalID uniqueidentifier
)


insert 	#GetCats 
exec 	ImpGetCategories @uAgid, @nUserLang

insert 	@SubCats
select 	nOrder, AuditGlobalId, CategoryGlobalId, ParentId, CategoryIdentity, CategorySequence, CategoryName, 
	CategoryView, NULL, CategoryAnswer, ReAuditCategory, nLevel, nSubCats, nQuestions, 0, 0
from 	#GetCats


insert into @IQ
select 	q.QuestionIdentity,
	0, -- DynamicInclude
	qrh.DynamicInclude,-- QuestionREsultn
	CASE  when cql.QuestionGlobalID IS NULL then 0 ELSE 1 end as triggered,
	CASE  when Activated.QActivated IS NULL then 0 When Activated.QActivated = 0 then 0 else 1 end as Activated,
	q.AuditGlobalID,
	q.CategoryGlobalID
from question q
inner join  Audit ma	on 
	ma.AuditGlobalID = q.AuditGlobalID
	AND ma.WebAuditID = @nId -- Restrict to only the questions that are in the webAudit
left join  QuestionResultHistory 	qrh on qrh.QuestionIdentity = q.QuestionIdentity
	and qrh.WebAuditID = @nID
	and qrh.Timestamp = (select Max(timeSTamp) as timestamp 
						from  QuestionREsultHistory 
						where WebAuditID = @nID 
							and QuestionIdentity = qrh.questionIdentity)						
inner join category c on 
	c.CategoryGlobalId = q.CategoryGlobalId
left join 
(
	(select	aq.questionGlobalID, count(*) as QActivated
	from 	question aq
		inner join ChoiceQuestionList cql
			on cql.QuestionGlobalId = aq.QuestionGlobalId
		inner join Choice tc
			on tc.choiceglobalid = cql.choiceglobalid
		inner join question tq
			on tq.questionglobalid = tc.questionglobalid
		inner join  QuestionResultHistory tqrh
			on tqrh.QuestionIdentity = tq.QuestionIdentity
			and tqrh.webauditid = @nId
			and tqrh.DynamicInclude = 1
			and tqrh.choiceidentity = tc.choiceidentity
		inner join
		(
			select Max(timeSTamp) as timestamp, QuestionIdentity from  QuestionREsultHistory where WebAuditID = @nId group by QuestionIdentity
		) mts on mts.TimeStamp = tqrh.Timestamp
		  and mts.QuestionIdentity = tqrh.QuestionIdentity
	Group by aq.QuestionGlobalID)
) Activated on Activated.QuestionGlobalID = q.QuestionGlobalID									
left join ChoiceQuestionList cql on 
	cql.QuestionGlobalID = q.QuestionGlobalID

update 	@IQ
set 	dynamicinclude = 1
where	(questionresult = 1 and triggered = 0)
	or (triggered = 0)
	or (triggered = 1 and activated = 1)

update sc
set nCatCompleted = 1
from @SubCats SC
left join 
(
		select 	q.CategoryGlobalID, count(*) as catCount
		from 	Question q
		join @iq iq 
			on q.AuditGlobalID = iq.AuditGlobalID
			and q.CategoryGlobalid = iq.CategoryGlobalID
			and q.QuestionIdentity = iq.QuestionIdentity
			and dynamicInclude = 1
		where 	q.Archived = 0 and q.Deleted = 0
				and q.ReAuditQuestion <= 2
				and q.required = 1
		group by q.CategoryGlobalID
) total on total.CategoryGlobalID = sc.CategoryGlobalID
left join
(
	select 	CategoryGlobalID, count(mqrh.QuestionIdentity) as catCount 
	from	 QuestionResult mqr,  QuestionResultHistory mqrh,@IQ iq
	where 	mqr.WebAuditId = @nId 
		and (select required from question where questionidentity = mqr.questionidentity) = 1
		--and mqr.CategoryIdentity = sc.CategoryIdentity
		and mqr.WebAuditId = mqrh.WebAuditId
		and mqr.QuestionIdentity = mqrh.QuestionIdentity
		and (ChoiceIdentity is not null or Len(ChoiceText) > 0)
		and IQ.dynamicInclude = 1
		and timestamp = (	select 	max(timestamp)
							from 	 QuestionResultHistory
							where 	WebAuditId = mqrh.WebAuditId
							and QuestionIdentity = mqrh.QuestionIdentity)
		and iq.QuestionIdentity = mqrh.QuestionIdentity
	group by CategoryGlobalID
) TotalComplete on 	total.CategoryGlobalID = totalComplete.CategoryGlobalID				
where isNull(total.CatCount,0) = isNUll(totalComplete.CatCount,0)

update 	s1
set 	ParentIdentity = s2.CategoryIdentity 
from 	@SubCats s1, @SubCats s2
where 	s1.ParentId = s2.CategoryGlobalId

Update 	@SubCats
Set 	nSubCatsCompleted = 1
where 	nSubCats = 0

update 	s
set 	nSubCatsCompleted = 1
from	@SubCats s
where 	s.nSubCats > 0
	and (select count(*) from @SubCats where parentId = s.CategoryGlobalId and nCatCompleted = 1 and nSubCatsCompleted = 1) = s.nSubCats
		
select	*
from	@SubCats
order 	by nOrder

DROP TABLE #GetCats

 

[campbeltr]

No thoughts on this?

 

[SQLDenis]

read my previous reply

show the execution plans of both

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[campbeltr]

SQLDenis,

Thank you for your reply, and I did of course read your previous reply. In response to that first reply, I posted the actual code that I am trying to work with. However, based on your most recent note, that doesn't seem to be what you were looking for. So, I am sorry to say that I do not understand what you are looking for when you asked for "the execution plans". If you would be kind enough to educate me briefly on what you meant by that, I will do my best to provide you with what you have asked for.

Thank you

 

[SQLDenis]

set showplan_text on

--put your code here


set showplan_text of

set showplan_text on

--exec your proc here


set showplan_text of
[code]

first look at the differences in the plans and then paste the results here

Denis The SQL Menace 
--------------------
[url=http://sqlservercode.blogspot.com/]SQL Server Code,Tips and Tricks, Performance Tuning[/url] 
[url=http://forum.lessthandot.com/viewtopic.php?f=17&t=306]SQL Server Programming Hacks [/url]

 

[gmmastros]

At the top of the query analyzer window....

set showplan_text on
go
-- your code here.

Now, when you run the code (F5), you will see the execution plan in a text format that you can copy/paste here. It helps if you set your output to text.

Tools -> Options
Results tab
Default results target -> Results To Text

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[campbeltr]

Okay, I've done that. Unfortunately, this looks like a lot to slog through, but if anyone is willing to skim through it an give your thoughts, I'd really appreciate it.

Actual Stored Procedure...

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 SubCatInfo 13067, 1
  
CREATE PROCEDURE  SubCatInfo

@nId 		int,
@nUserLang 	int

AS

declare @uAgid 	uniqueidentifier

select @uAgid = AuditGlobalId  from  Audit where WebAuditId = @nId

(2 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
            |--Index Seek(OBJECT:([ DB].[dbo].[ Audit].[PK_WebAudit]), SEEK:([ DB].[dbo].[ Audit].[WebAuditID]=[@nId]) ORDERED FORWARD)
            |--RID Lookup(OBJECT:([ DB].[dbo].[ Audit]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
CREATE  TABLE  #GetCats(	 nOrder  		int NOT NULL,
				 AuditGlobalId  		uniqueIdentifier NOT NULL,
				 CategoryGlobalId 	uniqueIdentifier NOT NULL,
				 ParentId  		uniqueIdentifier NULL,
				 CategoryIdentity  	int NOT NULL,
				 CategoryUniqueID
  
DECLARE @SubCats TABLE  (	nOrder  			int NOT NULL,
				AuditGlobalId 		uniqueIdentifier NOT NULL,
				CategoryGlobalId  	uniqueIdentifier NOT NULL,
				ParentId  		uniqueIdentifier NULL,
				CategoryIdentity  	int NOT NULL,
				CategorySequence  	i

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Insert(OBJECT:([tempdb].[dbo].[#GetCats]), SET:([#GetCats].[nOrder] = RaiseIfNull([ParameterTable].[nOrder]),[#GetCats].[AuditGlobalId] = RaiseIfNull([ParameterTable].[AuditGlobalId]),[#GetCats].[CategoryGlobalId] = RaiseIfNull([ParameterTa
            |--Top(ROWCOUNT est 0)
                 |--Parameter Table Scan

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    


CREATE  PROCEDURE ImpGetCategories


@uAgid		uniqueidentifier,
@nUserLang	int			= 1,
@uCgid		uniqueidentifier		= NULL,
@nLevel	int			= 1,
@nIdsOnly	int			= 0

AS

------------------------------------------------------------------------
      


CREATE PROCEDURE SQSGetConfigItem

@nUserLang	int,
@strItemKey	nvarchar(100),
@strItemValue	nvarchar(4000)	Output

AS

select @strItemValue = isnull(ItemValue, StandardValue) from SQSConfig where LanguageKey = @nUserLang and ItemKey = @

(2 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Compute Scalar(DEFINE:([Expr1003]=isnull([ DB].[dbo].[SQSConfig].[ItemValue],[ DB].[dbo].[SQSConfig].[StandardValue])))
                      |--Clustered Index Seek(OBJECT:([ DB].[dbo].[SQSConfig].[PK_SQSConfig]), SEEK:([ DB].[dbo].[SQSConfig].[LanguageKey]=[@nUserLang] AND [ DB].[dbo].[SQSConfig].[ItemKey]=[@strItemKey]) ORDERED FORWARD)

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
exec SQSGetConfigItem @nUserLang,  'labelSingleQuestion', @strSingle output
      


CREATE PROCEDURE SQSGetConfigItem

@nUserLang	int,
@strItemKey	nvarchar(100),
@strItemValue	nvarchar(4000)	Output

AS

select @strItemValue = isnull(ItemValue, StandardValue) from SQSConfig where LanguageKey = @nUserLang and ItemKey = @

(2 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Compute Scalar(DEFINE:([Expr1003]=isnull([ DB].[dbo].[SQSConfig].[ItemValue],[ DB].[dbo].[SQSConfig].[StandardValue])))
                      |--Clustered Index Seek(OBJECT:([ DB].[dbo].[SQSConfig].[PK_SQSConfig]), SEEK:([ DB].[dbo].[SQSConfig].[LanguageKey]=[@nUserLang] AND [ DB].[dbo].[SQSConfig].[ItemKey]=[@strItemKey]) ORDERED FORWARD)

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
set @bCatWeightsOK = 1
    
create table #ImpCats (	Catcgid uniqueidentifier, l1 int NULL, l2 int NULL, l3 int NULL, l4 int NULL, l5 int NULL, l6 int NULL, l7 int NULL, l8 int NULL, 
			l9 int NULL, l10 int NULL,  l11 int NULL,  l12 int NULL,  l13 int NULL,  l14 int NULL,  l15 
    
create table #Out (	nOrder int identity, Outcgid uniqueidentifier, nLevel int, nSubCats int NULL, nQuestions int NULL)


-------------------------------------------------------------------------------------------------------------------------------
    
insert 	into #ImpCats (Catcgid, l1, nLevel) 
select 	CategoryGlobalId, CategorySequence, 1 
from 	Category 
where 	AuditGlobalId = @uAgid 
	and (((@uCgid is NULL) and (ParentId is NULL)) or ((@uCgid is NOT NULL) and (ParentId = @uCgid)))
	and Arc

(4 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Table Insert(OBJECT:([tempdb].[dbo].[#ImpCats]), SET:([#ImpCats].[Catcgid] = [ DB].[dbo].[Category].[CategoryGlobalID],[#ImpCats].[l1] = [ DB].[dbo].[Category].[CategorySequence],[#ImpCats].[nLevel] = [Expr1007],[#ImpCats].[l2] = NULL,[#
                 |--Compute Scalar(DEFINE:([Expr1007]=(1)))
                      |--Top(ROWCOUNT est 0)
                           |--Sort(ORDER BY:([ DB].[dbo].[Category].[CategorySequence] ASC))
                                |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Category].[PK_Category]), SEEK:([ DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([ DB].[dbo].[Category].[Archived]=(0) AND [ DB].[dbo].[Category].[Deleted]=(0) AN

(5 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
Set @n = 2
    
While (@n <= 15)
       Begin
	insert 	into #ImpCats
	select	c.CategoryGlobalId,
		Parent.l1,
		Case When @n = 2 Then c.CategorySequence Else Parent.l2 End,
		Case When @n = 3 Then c.CategorySequence Else Parent.l3 End,
		Case When @n = 4 Then c.CategorySequence Else

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Table Insert(OBJECT:([tempdb].[dbo].[#ImpCats]), SET:([#ImpCats].[Catcgid] = [ DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID],[#ImpCats].[l1] = #ImpCats.[l1] as [Parent].[l1],[#ImpCats].[l2] = [Expr1009],[#ImpCats].
                      |--Table Spool
                           |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [@n] = (2) THEN [ DB].[dbo].[Category].[CategorySequence] as [c].[CategorySequence] ELSE #ImpCats.[l2] as [Parent].[l2] END, [Expr1010]=CASE WHEN [@n] = (3) THEN [ DB].[dbo].[Cat
                                |--Top(ROWCOUNT est 0)
                                     |--Hash Match(Inner Join, HASH:([Parent].[Catcgid])=([c].[ParentID]), RESIDUAL:([ DB].[dbo].[Category].[ParentID] as [c].[ParentID]=#ImpCats.[Catcgid] as [Parent].[Catcgid]))
                                          |--Filter(WHERE:(CASE WHEN [@n] = (2) THEN #ImpCats.[l1] as [Parent].[l1] ELSE CASE WHEN [@n] = (3) THEN #ImpCats.[l2] as [Parent].[l2] ELSE CASE WHEN [@n] = (4) THEN #ImpCats.[l3] as [Parent].[l3] ELSE CASE WHEN [
                                          |    |--Table Scan(OBJECT:([tempdb].[dbo].[#ImpCats] AS [Parent]))
                                          |--Clustered Index Scan(OBJECT:([ DB].[dbo].[Category].[PK_Category] AS [c]), WHERE:([ DB].[dbo].[Category].[Archived] as [c].[Archived]=(0) AND [ DB].[dbo].[Category].[Deleted] as [c].[Deleted]=(0)))

(8 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      	Set @n = @n + 1
    
End

-- Compile
insert 	into #Out (Outcgid, nLevel, nSubCats, nQuestions)
select	Catcgid, nLevel, nSubCats, Sum(Case When q.QuestionGlobalId is NULL Then 0 Else 1 End) as nQuestions
from	(
	select	Catcgid, nLevel, Sum(Case When c.CategoryGlobalI

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Table Insert(OBJECT:([tempdb].[dbo].[#Out]), SET:([#Out].[Outcgid] = [#ImpCats].[Catcgid],[#Out].[nLevel] = [#ImpCats].[nLevel],[#Out].[nSubCats] = [Expr1010],[#Out].[nQuestions] = [Expr1014],[#Out].[nOrder] = [Expr1015]))
                 |--Compute Scalar(DEFINE:([Expr1015]=getidentity((24982910),(2),N'#Out')))
                      |--Top(ROWCOUNT est 0)
                           |--Stream Aggregate(GROUP BY:([#ImpCats].[l1], [#ImpCats].[l2], [#ImpCats].[l3], [#ImpCats].[l4], [#ImpCats].[l5], [#ImpCats].[l6], [#ImpCats].[l7], [#ImpCats].[l8], [#ImpCats].[l9], [#ImpCats].[l10], [#ImpCats].[l11], [#ImpCats]
                                |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [ DB].[dbo].[Question].[QuestionGlobalID] as [q].[QuestionGlobalID] IS NULL THEN (0) ELSE (1) END))
                                     |--Nested Loops(Left Outer Join, OUTER REFERENCES:([#ImpCats].[Catcgid]))
                                          |--Stream Aggregate(GROUP BY:([#ImpCats].[l1], [#ImpCats].[l2], [#ImpCats].[l3], [#ImpCats].[l4], [#ImpCats].[l5], [#ImpCats].[l6], [#ImpCats].[l7], [#ImpCats].[l8], [#ImpCats].[l9], [#ImpCats].[l10], [#ImpCats].[l
                                          |    |--Compute Scalar(DEFINE:([Expr1017]=CASE WHEN [ DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID] IS NULL THEN (0) ELSE (1) END))
                                          |         |--Nested Loops(Left Outer Join, WHERE:([ DB].[dbo].[Category].[ParentID] as [c].[ParentID]=[#ImpCats].[Catcgid]))
                                          |              |--Sort(ORDER BY:([#ImpCats].[l1] ASC, [#ImpCats].[l2] ASC, [#ImpCats].[l3] ASC, [#ImpCats].[l4] ASC, [#ImpCats].[l5] ASC, [#ImpCats].[l6] ASC, [#ImpCats].[l7] ASC, [#ImpCats].[l8] ASC, [#ImpCats].[l
                                          |              |    |--Table Scan(OBJECT:([tempdb].[dbo].[#ImpCats]))
                                          |              |--Clustered Index Scan(OBJECT:([ DB].[dbo].[Category].[PK_Category] AS [c]), WHERE:([ DB].[dbo].[Category].[Archived] as [c].[Archived]=(0) AND [ DB].[dbo].[Category].[Deleted] as [c].[Deleted
                                          |--Filter(WHERE:([ DB].[dbo].[Question].[Archived] as [q].[Archived]=(0) AND [ DB].[dbo].[Question].[Deleted] as [q].[Deleted]=(0)))
                                               |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1011]))
                                                    |--Index Seek(OBJECT:([ DB].[dbo].[Question].[IX_Question_QuestionSequence] AS [q]), SEEK:([q].[CategoryGlobalID]=[#ImpCats].[Catcgid]) ORDERED FORWARD)
                                                    |--RID Lookup(OBJECT:([ DB].[dbo].[Question] AS [q]), SEEK:([Bmk1011]=[Bmk1011]) LOOKUP ORDERED FORWARD)

(16 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------
    
if exists (select * from SQSOption where OptionId = 'weightedcats' and Setting = 1)

(1 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
                 |--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
                      |--Constant Scan
                      |--Filter(WHERE:([ DB].[dbo].[SQSOption].[Setting]=(1)))
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [ DB].[dbo].[SQSOption].[ClientID]))
                                |--Index Seek(OBJECT:([ DB].[dbo].[SQSOption].[PK_SQSOption]), SEEK:([ DB].[dbo].[SQSOption].[OptionID]=N'weightedcats') ORDERED FORWARD)
                                |--Clustered Index Seek(OBJECT:([ DB].[dbo].[SQSOption].[IX_SQSOption_ClientId]), SEEK:([ DB].[dbo].[SQSOption].[ClientID]=[ DB].[dbo].[SQSOption].[ClientID] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

(7 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       begin
	-- Determine if every top level category has a weight and the sum is 100
	select @nTotCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                      |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                           |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Category].[PK_Category]), SEEK:([ DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([ DB].[dbo].[Category].[ParentID] IS NULL AND [ DB].[dbo].[Category].[Archived]=(0) AN

(3 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
      	select @nWeightCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0 and CategoryWeight is NOT NULL

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                      |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                           |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Category].[PK_Category]), SEEK:([ DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([ DB].[dbo].[Category].[ParentID] IS NULL AND [ DB].[dbo].[Category].[Archived]=(0) AN

(3 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      	select @nSum = sum(CategoryWeight) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0 and CategoryWeight is NOT NULL

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END))
                      |--Stream Aggregate(DEFINE:([Expr1004]=Count(*), [Expr1005]=SUM([ DB].[dbo].[Category].[CategoryWeight])))
                           |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Category].[PK_Category]), SEEK:([ DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([ DB].[dbo].[Category].[ParentID] IS NULL AND [ DB].[dbo].[Category].[Archived]=(0) AN

(3 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------
      	select @nSubCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NOT NULL and Archived = 0 and Deleted = 0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                      |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                           |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Category].[PK_Category]), SEEK:([ DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([ DB].[dbo].[Category].[Archived]=(0) AND [ DB].[dbo].[Category].[Deleted]=(0) AND [Ty

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      	if((@nWeightCnt > 0) and (((@nTotCnt - @nWeightCnt) > 0) or (@nSum <> 100) or (@nSubCnt > 0)))
         set @bCatWeightsOK = 0
    
end

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- RETUR
      	select	#Out.nOrder, c.*, #Out.nLevel, CategoryViewLabel = Case When c.CategoryView = 1 Then @strCatList Else @strSingle End, 
		nSubCats, nQuestions, cl.CategoryName, @bCatWeightsOK as CatWeightsOK, cdd.categoryduedates
	from	Category c 
		join C

(4 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Compute Scalar(DEFINE:([Expr1010]=CASE WHEN [ DB].[dbo].[Category].[CategoryView] as [c].[CategoryView]=(1) THEN [@strCatList] ELSE [@strSingle] END))
                      |--Nested Loops(Left Outer Join, OUTER REFERENCES:([c].[CategoryGlobalID]))
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                           |    |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                           |    |    |--Sort(ORDER BY:([#Out].[nOrder] ASC))
                           |    |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#Out]))
                           |    |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Category].[PK_Category] AS [c]), SEEK:([c].[AuditGlobalID]=[@uAgid] AND [c].[CategoryGlobalID]=[#Out].[Outcgid]) ORDERED FORWARD)
                           |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[CategoryLanguage].[PK_CategoryLanguage] AS [cl]), SEEK:([cl].[CategoryGlobalID]=[#Out].[Outcgid] AND [cl].[LanguageKey]=[@nUserLang]) ORDERED FORWARD)
                           |--Clustered Index Seek(OBJECT:([ DB].[dbo].[CategoryDueDates].[PK_CategoryDueDates] AS [cdd]), SEEK:([cdd].[CategoryGlobalID]=[ DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID]) ORDERED FORWARD)

(9 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
Else If (@nIdsOnly = 2)
        	select	#Out.nOrder, c.CategoryGlobalId, c.ParentId, c.CategoryReference, #OUT.nLevel, c.Archived, c.Deleted
	from	Category c, CategoryLanguage cl, #Out
	where	c.AuditGlobalId = @uAgid
		and c.CategoryGlobalId = #Out.Outcgid
		and c.CategoryGlo

(2 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                           |    |--Sort(ORDER BY:([#Out].[nOrder] ASC))
                           |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#Out]))
                           |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Category].[PK_Category] AS [c]), SEEK:([c].[AuditGlobalID]=[@uAgid] AND [c].[CategoryGlobalID]=[#Out].[Outcgid]) ORDERED FORWARD)
                           |--Clustered Index Seek(OBJECT:([ DB].[dbo].[CategoryLanguage].[PK_CategoryLanguage] AS [cl]), SEEK:([cl].[CategoryGlobalID]=[#Out].[Outcgid] AND [cl].[LanguageKey]=[@nUserLang]) ORDERED FORWARD)

(6 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        
Else
	select	#Out.nOrder, c.CategoryGlobalId
	from	Category c, CategoryLanguage cl, #Out
	where	c.AuditGlobalId = @uAgid
		and c.CategoryGlobalId = #Out.Outcgid
		and c.CategoryGlobalId = cl.CategoryGlobalId
		and cl.LanguageKey = @nUserLang

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      |--Sort(ORDER BY:([#Out].[nOrder] ASC))
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                                |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                                |    |--Table Scan(OBJECT:([tempdb].[dbo].[#Out]))
                                |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Category].[PK_Category] AS [c]), SEEK:([c].[AuditGlobalID]=[@uAgid] AND [c].[CategoryGlobalID]=[#Out].[Outcgid]) ORDERED FORWARD)
                                |--Clustered Index Seek(OBJECT:([ DB].[dbo].[CategoryLanguage].[PK_CategoryLanguage] AS [cl]), SEEK:([cl].[CategoryGlobalID]=[#Out].[Outcgid] AND [cl].[LanguageKey]=[@nUserLang]) ORDERED FORWARD)

(6 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
drop table #Out
    
drop table #ImpCats
  
insert 	@SubCats
select 	nOrder, AuditGlobalId, CategoryGlobalId, ParentId, CategoryIdentity, CategorySequence, CategoryName, 
	CategoryView, NULL, CategoryAnswer, ReAuditCategory, nLevel, nSubCats, nQuestions, 0, 0
from 	#GetCats

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Insert(OBJECT:(@SubCats), SET:([nOrder] = [#GetCats].[nOrder],[AuditGlobalId] = [#GetCats].[AuditGlobalId],[CategoryGlobalId] = [#GetCats].[CategoryGlobalId],[ParentId] = [#GetCats].[ParentId],[CategoryIdentity] = [#GetCats].[CategoryIdenti
            |--Compute Scalar(DEFINE:([Expr1008]=NULL, [Expr1009]=(0), [Expr1010]=(0)))
                 |--Top(ROWCOUNT est 0)
                      |--Table Scan(OBJECT:([tempdb].[dbo].[#GetCats]))

(4 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
insert into @IQ
select 	q.QuestionIdentity,
	0, -- DynamicInclude
	qrh.DynamicInclude,-- QuestionREsultn
	CASE  when cql.QuestionGlobalID IS NULL then 0 ELSE 1 end as triggered,
	CASE  when Activated.QActivated IS NULL then 0 When Activated.QActiva

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Insert(OBJECT:(@IQ), SET:([QuestionIdentity] = [ DB].[dbo].[Question].[QuestionIdentity] as [q].[QuestionIdentity],[DynamicInclude] = [Expr1040],[QuestionResult] = [Expr1041],[Triggered] = [Expr1042],[activated] = [Expr1043],[AuditGlobalI
            |--Compute Scalar(DEFINE:([Expr1040]=(0), [Expr1043]=CONVERT_IMPLICIT(bit,CASE WHEN [Expr1035] IS NULL THEN (0) ELSE CASE WHEN [Expr1035]=(0) THEN (0) ELSE (1) END END,0)))
                 |--Top(ROWCOUNT est 0)
                      |--Compute Scalar(DEFINE:([Expr1042]=CONVERT_IMPLICIT(bit,CASE WHEN [ DB].[dbo].[ChoiceQuestionList].[QuestionGlobalID] as [cql].[QuestionGlobalID] IS NULL THEN (0) ELSE (1) END,0)))
                           |--Hash Match(Left Outer Join, HASH:([q].[QuestionGlobalID])=([cql].[QuestionGlobalID]), RESIDUAL:([ DB].[dbo].[ChoiceQuestionList].[QuestionGlobalID] as [cql].[QuestionGlobalID]=[ DB].[dbo].[Question].[QuestionGlobalID] as [
                                |--Hash Match(Right Outer Join, HASH:([aq].[QuestionGlobalID])=([q].[QuestionGlobalID]), RESIDUAL:([ DB].[dbo].[Question].[QuestionGlobalID] as [aq].[QuestionGlobalID]=[ DB].[dbo].[Question].[QuestionGlobalID] as [q].[Qu
                                |    |--Compute Scalar(DEFINE:([Expr1035]=[Expr1035]))
                                |    |    |--Compute Scalar(DEFINE:([Expr1035]=CONVERT_IMPLICIT(int,[Expr1054],0)))
                                |    |         |--Stream Aggregate(GROUP BY:([aq].[QuestionGlobalID]) DEFINE:([Expr1054]=Count(*)))
                                |    |              |--Sort(ORDER BY:([aq].[QuestionGlobalID] ASC))
                                |    |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([cql].[QuestionGlobalID]))
                                |    |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([tc].[ChoiceGlobalID]))
                                |    |                        |    |--Filter(WHERE:([ DB].[dbo].[Question].[QuestionIdentity] as [tq].[QuestionIdentity]=[ DB].[dbo].[ QuestionResultHistory].[QuestionIdentity]))
                                |    |                        |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1026]))
                                |    |                        |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([tc].[QuestionGlobalID]))
                                |    |                        |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tc].[AuditGlobalID], [tc].[ChoiceGlobalID]))
                                |    |                        |    |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tqrh].[ChoiceIdentity]))
                                |    |                        |    |         |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ DB].[dbo].[ QuestionResultHistory].[QuestionIdentity], [Expr1034]))
                                |    |                        |    |         |    |    |    |    |--Stream Aggregate(GROUP BY:([ DB].[dbo].[ QuestionResultHistory].[QuestionIdentity]) DEFINE:([Expr1034]=MAX([ DB].[dbo].[ QuestionResultHistory].[T
                                |    |                        |    |         |    |    |    |    |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory]), SEEK:([ DB].[dbo].[ QuestionResultHistory]
                                |    |                        |    |         |    |    |    |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory] AS [tqrh]), SEEK:([tqrh].[WebAuditID]=[@nId] AND [tqrh
                                |    |                        |    |         |    |    |    |--Index Seek(OBJECT:([ DB].[dbo].[Choice].[IX_Choice] AS [tc]), SEEK:([tc].[ChoiceIdentity]=[ DB].[dbo].[ QuestionResultHistory].[ChoiceIdentity] as [tqrh].
                                |    |                        |    |         |    |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[Choice].[PK_Choice] AS [tc]), SEEK:([tc].[AuditGlobalID]=[ DB].[dbo].[Choice].[AuditGlobalID] as [tc].[AuditGlobalID] AN
                                |    |                        |    |         |    |--Index Seek(OBJECT:([ DB].[dbo].[Question].[IX_QuestionGlobalId] AS [tq]), SEEK:([tq].[QuestionGlobalID]=[ DB].[dbo].[Choice].[QuestionGlobalID] as [tc].[QuestionGlobal
                                |    |                        |    |         |--RID Lookup(OBJECT:([ DB].[dbo].[Question] AS [tq]), SEEK:([Bmk1026]=[Bmk1026]) LOOKUP ORDERED FORWARD)
                                |    |                        |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[ChoiceQuestionList].[PK_ChoiceQuestionList] AS [cql]), SEEK:([cql].[ChoiceGlobalID]=[ DB].[dbo].[Choice].[ChoiceGlobalID] as [tc].[ChoiceGlo
                                |    |                        |--Index Seek(OBJECT:([ DB].[dbo].[Question].[IX_QuestionGlobalId] AS [aq]), SEEK:([aq].[QuestionGlobalID]=[ DB].[dbo].[ChoiceQuestionList].[QuestionGlobalID] as [cql].[QuestionGlobalID]) OR
                                |    |--Compute Scalar(DEFINE:([Expr1041]=CONVERT_IMPLICIT(int,[ DB].[dbo].[ QuestionResultHistory].[DynamicInclude] as [qrh].[DynamicInclude],0)))
                                |         |--Hash Match(Right Outer Join, HASH:([qrh].[QuestionIdentity])=([q].[QuestionIdentity]))
                                |              |--Top(TOP EXPRESSION:((1)))
                                |              |    |--Segment
                                |              |         |--Clustered Index Seek(OBJECT:([ DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory] AS [qrh]), SEEK:([qrh].[WebAuditID]=[@nId]) ORDERED BACKWARD)
                                |              |--Hash Match(Inner Join, HASH:([q].[CategoryGlobalID])=([c].[CategoryGlobalID]), RESIDUAL:([ DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID]=[ DB].[dbo].[Question].[CategoryGlobalID] as 
                                |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004], [Expr1056]) WITH UNORDERED PREFETCH)
                                |                   |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ma].[AuditGlobalID]))
                                |                   |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1007]))
                                |                   |    |    |    |--Index Seek(OBJECT:([ DB].[dbo].[ Audit].[PK_WebAudit] AS [ma]), SEEK:([ma].[WebAuditID]=[@nId]) ORDERED FORWARD)
                                |                   |    |    |    |--RID Lookup(OBJECT:([ DB].[dbo].[ Audit] AS [ma]), SEEK:([Bmk1007]=[Bmk1007]) LOOKUP ORDERED FORWARD)
                                |                   |    |    |--Index Seek(OBJECT:([ DB].[dbo].[Question].[PK_Question] AS [q]), SEEK:([q].[AuditGlobalID]=[ DB].[dbo].[ Audit].[AuditGlobalID] as [ma].[AuditGlobalID]) ORDERED FORWARD)
                                |                   |    |--RID Lookup(OBJECT:([ DB].[dbo].[Question] AS [q]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)
                                |                   |--Index Scan(OBJECT:([ DB].[dbo].[Category].[IX_CategoryGlobalId] AS [c]))
                                |--Clustered Index Scan(OBJECT:([ DB].[dbo].[ChoiceQuestionList].[PK_ChoiceQuestionList] AS [cql]))

(42 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------
  
update 	@IQ
set 	dynamicinclude = 1
where	(questionresult = 1 and triggered = 0)
	or (triggered = 0)
	or (triggered = 1 and activated = 1)

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------
       |--Table Update(OBJECT:(@IQ), SET:([DynamicInclude] = [Expr1004]))
            |--Compute Scalar(DEFINE:([Expr1004]=(1)))
                 |--Top(ROWCOUNT est 0)
                      |--Table Scan(OBJECT:(@IQ), WHERE:([Triggered]=(0) OR [Triggered]=(1) AND [activated]=(1)) ORDERED)

(4 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
update sc
set nCatCompleted = 1
from @SubCats SC
left join 
(
		select 	q.CategoryGlobalID, count(*) as catCount
		from 	Question q
		join @iq iq 
			on q.AuditGlobalID = iq.AuditGlobalID
			and q.CategoryGlobalid = iq.CategoryGlobalID
			and 

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Update(OBJECT:(@SubCats), SET:([nCatCompleted] = [Expr1032]))
            |--Compute Scalar(DEFINE:([Expr1032]=(1)))
                 |--Top(ROWCOUNT est 0)
                      |--Filter(WHERE:(isnull([Expr1009],(0))=isnull([Expr1029],(0))))
                           |--Nested Loops(Left Outer Join, OUTER REFERENCES:([q].[CategoryGlobalID]))
                                |--Nested Loops(Left Outer Join, OUTER REFERENCES:([SC].[CategoryGlobalId]))
                                |    |--Table Scan(OBJECT:(@SubCats AS [SC]))
                                |    |--Compute Scalar(DEFINE:([q].[CategoryGlobalID]=[ DB].[dbo].[Question].[CategoryGlobalID] as [q].[CategoryGlobalID], [Expr1009]=[Expr1009]))
                                |         |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1050],0)))
                                |              |--Stream Aggregate(DEFINE:([Expr1050]=Count(*), [q].[CategoryGlobalID]=ANY([ DB].[dbo].[Question].[CategoryGlobalID] as [q].[CategoryGlobalID])))
                                |                   |--Filter(WHERE:([ DB].[dbo].[Question].[AuditGlobalID] as [q].[AuditGlobalID]=@iq.[AuditGlobalID] as [iq].[AuditGlobalID] AND [ DB].[dbo].[Question].[CategoryGlobalID] as [q].[CategoryGlobalID]=@SubC
                                |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003]))
                                |                             |--Nested Loops(Inner Join, OUTER REFERENCES:([iq].[QuestionIdentity]))
                                |                             |    |--Table Scan(OBJECT:(@iq AS [iq]), WHERE:(@iq.[CategoryGlobalID] as [iq].[CategoryGlobalID]=@SubCats.[CategoryGlobalId] as [SC].[CategoryGlobalId] AND @iq.[DynamicInclude] as [iq].[Dynamic
                                |                             |    |--Index Seek(OBJECT:([ DB].[dbo].[Question].[IX_Question_QuestionIdentity] AS [q]), SEEK:([q].[QuestionIdentity]=@iq.[QuestionIdentity] as [iq].[QuestionIdentity]) ORDERED FORWARD)
                                |                             |--RID Lookup(OBJECT:([ DB].[dbo].[Question] AS [q]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD)
                                |--Compute Scalar(DEFINE:([Expr1029]=[Expr1029]))
                                     |--Compute Scalar(DEFINE:([Expr1029]=CONVERT_IMPLICIT(int,[Expr1051],0)))
                                          |--Stream Aggregate(DEFINE:([Expr1051]=Count(*)))
                                               |--Merge Join(Inner Join, MERGE:([ DB].[dbo].[ QuestionResultHistory].[QuestionIdentity], [Expr1027])=([iq].[QuestionIdentity], [mqrh].[TimeStamp]), RESIDUAL:([ DB].[dbo].[ QuestionResultHistory].[Qu
                                                    |--Stream Aggregate(GROUP BY:([ DB].[dbo].[ QuestionResultHistory].[QuestionIdentity]) DEFINE:([Expr1027]=MAX([ DB].[dbo].[ QuestionResultHistory].[TimeStamp])))
                                                    |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory]), SEEK:([ DB].[dbo].[ QuestionResultHistory].[WebAuditID]=[@nId]) ORDERED FORWARD)
                                                    |--Nested Loops(Inner Join, WHERE:(@IQ.[QuestionIdentity] as [iq].[QuestionIdentity]=[ DB].[dbo].[ QuestionResultHistory].[QuestionIdentity] as [mqrh].[QuestionIdentity]))
                                                         |--Filter(WHERE:([Expr1038]=(1)))
                                                         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mqr].[QuestionIdentity]))
                                                         |         |--Nested Loops(Inner Join, OUTER REFERENCES:([mqrh].[QuestionIdentity]))
                                                         |         |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory] AS [mqrh]), SEEK:([mqrh].[WebAuditID]=[@nId]),  WHERE:([ DB].[dbo].[ Quest
                                                         |         |    |--Clustered Index Seek(OBJECT:([ DB].[dbo].[ QuestionResult].[PK_ QuestionResult] AS [mqr]), SEEK:([mqr].[WebAuditID]=[@nId] AND [mqr].[QuestionIdentity]=[ DB].[dbo].[ Qu
                                                         |         |--Assert(WHERE:(CASE WHEN [Expr1037]>(1) THEN (0) ELSE NULL END))
                                                         |              |--Stream Aggregate(DEFINE:([Expr1037]=Count(*), [Expr1038]=ANY([ DB].[dbo].[Question].[Required])))
                                                         |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1019]))
                                                         |                        |--Index Seek(OBJECT:([ DB].[dbo].[Question].[IX_Question_QuestionIdentity]), SEEK:([ DB].[dbo].[Question].[QuestionIdentity]=[ DB].[dbo].[ QuestionResult].[Question
                                                         |                        |--RID Lookup(OBJECT:([ DB].[dbo].[Question]), SEEK:([Bmk1019]=[Bmk1019]) LOOKUP ORDERED FORWARD)
                                                         |--Table Scan(OBJECT:(@IQ AS [iq]), WHERE:([ DB].[dbo].[Question].[CategoryGlobalID] as [q].[CategoryGlobalID]=@IQ.[CategoryGlobalID] as [iq].[CategoryGlobalID] AND @IQ.[DynamicInclude] as [iq].[Dy

(34 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------
  
update 	s1
set 	ParentIdentity = s2.CategoryIdentity 
from 	@SubCats s1, @SubCats s2
where 	s1.ParentId = s2.CategoryGlobalId

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Update(OBJECT:(@SubCats), SET:([ParentIdentity] = @SubCats.[CategoryIdentity] as [s2].[CategoryIdentity]))
            |--Table Spool
                 |--Top(ROWCOUNT est 0)
                      |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([s2].[CategoryIdentity]=ANY(@SubCats.[CategoryIdentity] as [s2].[CategoryIdentity])))
                           |--Nested Loops(Inner Join, WHERE:(@SubCats.[CategoryGlobalId] as [s2].[CategoryGlobalId]=@SubCats.[ParentId] as [s1].[ParentId]))
                                |--Table Scan(OBJECT:(@SubCats AS [s1]))
                                |--Table Scan(OBJECT:(@SubCats AS [s2]))

(7 row(s) affected)

StmtText
------------------------------------------------------------------------
  
Update 	@SubCats
Set 	nSubCatsCompleted = 1
where 	nSubCats = 0

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------
       |--Table Update(OBJECT:(@SubCats), SET:([nSubCatsCompleted] = [Expr1004]))
            |--Compute Scalar(DEFINE:([Expr1004]=(1)))
                 |--Top(ROWCOUNT est 0)
                      |--Table Scan(OBJECT:(@SubCats), WHERE:([nSubCats]=(0)) ORDERED)

(4 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
update 	s
set 	nSubCatsCompleted = 1
from	@SubCats s
where 	s.nSubCats > 0
	and (select count(*) from @SubCats where parentId = s.CategoryGlobalId and nCatCompleted = 1 and nSubCatsCompleted = 1) = s.nSubCats

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Update(OBJECT:(@SubCats), SET:([nSubCatsCompleted] = [Expr1009]))
            |--Compute Scalar(DEFINE:([Expr1009]=(1)))
                 |--Table Spool
                      |--Top(ROWCOUNT est 0)
                           |--Filter(WHERE:(CASE WHEN [Expr1007] IS NULL THEN (0) ELSE [Expr1007] END=@SubCats.[nSubCats] as [s].[nSubCats]))
                                |--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[CategoryGlobalId]))
                                     |--Table Scan(OBJECT:(@SubCats AS [s]), WHERE:(@SubCats.[nSubCats] as [s].[nSubCats]>(0)) ORDERED)
                                     |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1020],0)))
                                          |--Stream Aggregate(DEFINE:([Expr1020]=Count(*)))
                                               |--Table Scan(OBJECT:(@SubCats), WHERE:([ParentId]=@SubCats.[CategoryGlobalId] as [s].[CategoryGlobalId] AND [nCatCompleted]=(1) AND [nSubCatsCompleted]=(1)))

(10 row(s) affected)

StmtText
------------------------------------------------
  
select	*
from	@SubCats
order 	by nOrder

(1 row(s) affected)

StmtText
---------------------------------------------
       |--Sort(ORDER BY:([nOrder] ASC))
            |--Table Scan(OBJECT:(@SubCats))

(2 row(s) affected)

StmtText
-------------------------
  
DROP TABLE #GetCats

(1 row(s) affected)

New Query Window...

StmtText
--------------------------------------------------------------------------------------------------------------
declare @nId 		int--,
declare @nUserLang 	int

set @nId = 13067

set @nUserLang = 1

declare @uAgid 	uniqueidentifier

select @uAgid = AuditGlobalId  from  Audit where WebAuditId = @nId

(3 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |--Index Seek(OBJECT:([DB].[dbo].[ Audit].[PK_WebAudit]), SEEK:([DB].[dbo].[ Audit].[WebAuditID]=[@nId]) ORDERED FORWARD)
       |--RID Lookup(OBJECT:([DB].[dbo].[ Audit]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE  TABLE  #GetCats(	 nOrder  		int NOT NULL,
				 AuditGlobalId  		uniqueIdentifier NOT NULL,
				 CategoryGlobalId 	uniqueIdentifier NOT NULL,
				 ParentId  		uniqueIdentifier NULL,
				 CategoryIdentity  	int NOT NULL,
				 CategoryUniqueID  

DECLARE @SubCats TABLE  (	nOrder  			int NOT NULL,
				AuditGlobalId 		uniqueIdentifier NOT NULL,
				CategoryGlobalId  	uniqueIdentifier NOT NULL,
				ParentId  		uniqueIdentifier NULL,
				CategoryIdentity  	int NOT NULL,
				CategorySequence  	int

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Insert(OBJECT:([tempdb].[dbo].[#GetCats]), SET:([tempdb].[dbo].[#GetCats].[nOrder] = RaiseIfNull([ParameterTable].[nOrder]),[tempdb].[dbo].[#GetCats].[AuditGlobalId] = RaiseIfNull([ParameterTable].[AuditGlobalId]),[tempdb].[dbo].[#GetCats].[Cate
       |--Top(ROWCOUNT est 0)
            |--Parameter Table Scan

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  


CREATE  PROCEDURE ImpGetCategories


@uAgid		uniqueidentifier,
@nUserLang	int			= 1,
@uCgid		uniqueidentifier		= NULL,
@nLevel	int			= 1,
@nIdsOnly	int			= 0

AS

--------------------------------------------------------------------------
    


CREATE PROCEDURE SQSGetConfigItem

@nUserLang	int,
@strItemKey	nvarchar(100),
@strItemValue	nvarchar(4000)	Output

AS

select @strItemValue = isnull(ItemValue, StandardValue) from SQSConfig where LanguageKey = @nUserLang and ItemKey = @st

(2 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=isnull([DB].[dbo].[SQSConfig].[ItemValue],[DB].[dbo].[SQSConfig].[StandardValue])))
                 |--Clustered Index Seek(OBJECT:([DB].[dbo].[SQSConfig].[PK_SQSConfig]), SEEK:([DB].[dbo].[SQSConfig].[LanguageKey]=[@nUserLang] AND [DB].[dbo].[SQSConfig].[ItemKey]=[@strItemKey]) ORDERED FORWARD)

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
exec SQSGetConfigItem @nUserLang,  'labelSingleQuestion', @strSingle output
    


CREATE PROCEDURE SQSGetConfigItem

@nUserLang	int,
@strItemKey	nvarchar(100),
@strItemValue	nvarchar(4000)	Output

AS

select @strItemValue = isnull(ItemValue, StandardValue) from SQSConfig where LanguageKey = @nUserLang and ItemKey = @st

(2 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=isnull([DB].[dbo].[SQSConfig].[ItemValue],[DB].[dbo].[SQSConfig].[StandardValue])))
                 |--Clustered Index Seek(OBJECT:([DB].[dbo].[SQSConfig].[PK_SQSConfig]), SEEK:([DB].[dbo].[SQSConfig].[LanguageKey]=[@nUserLang] AND [DB].[dbo].[SQSConfig].[ItemKey]=[@strItemKey]) ORDERED FORWARD)

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
set @bCatWeightsOK = 1
  
create table #ImpCats (	Catcgid uniqueidentifier, l1 int NULL, l2 int NULL, l3 int NULL, l4 int NULL, l5 int NULL, l6 int NULL, l7 int NULL, l8 int NULL, 
			l9 int NULL, l10 int NULL,  l11 int NULL,  l12 int NULL,  l13 int NULL,  l14 int NULL,  l15 in
  
create table #Out (	nOrder int identity, Outcgid uniqueidentifier, nLevel int, nSubCats int NULL, nQuestions int NULL)


---------------------------------------------------------------------------------------------------------------------------------
  
insert 	into #ImpCats (Catcgid, l1, nLevel) 
select 	CategoryGlobalId, CategorySequence, 1 
from 	Category 
where 	AuditGlobalId = @uAgid 
	and (((@uCgid is NULL) and (ParentId is NULL)) or ((@uCgid is NOT NULL) and (ParentId = @uCgid)))
	and Archi

(4 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Insert(OBJECT:([tempdb].[dbo].[#ImpCats]), SET:([#ImpCats].[Catcgid] = [DB].[dbo].[Category].[CategoryGlobalID],[#ImpCats].[l1] = [DB].[dbo].[Category].[CategorySequence],[#ImpCats].[nLevel] = [Expr1007],[#ImpCats].[l2] = NULL,[#ImpCa
            |--Compute Scalar(DEFINE:([Expr1007]=(1)))
                 |--Top(ROWCOUNT est 0)
                      |--Sort(ORDER BY:([DB].[dbo].[Category].[CategorySequence] ASC))
                           |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[Archived]=(0) AND [DB].[dbo].[Category].[Deleted]=(0) AND ([@

(5 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
Set @n = 2
  
While (@n <= 15)
     Begin
	insert 	into #ImpCats
	select	c.CategoryGlobalId,
		Parent.l1,
		Case When @n = 2 Then c.CategorySequence Else Parent.l2 End,
		Case When @n = 3 Then c.CategorySequence Else Parent.l3 End,
		Case When @n = 4 Then c.CategorySequence Else P

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Table Insert(OBJECT:([tempdb].[dbo].[#ImpCats]), SET:([#ImpCats].[Catcgid] = [DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID],[#ImpCats].[l1] = #ImpCats.[l1] as [Parent].[l1],[#ImpCats].[l2] = [Expr1009],[#ImpCats].[l3] 
                 |--Table Spool
                      |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [@n] = (2) THEN [DB].[dbo].[Category].[CategorySequence] as [c].[CategorySequence] ELSE #ImpCats.[l2] as [Parent].[l2] END, [Expr1010]=CASE WHEN [@n] = (3) THEN [DB].[dbo].[Category
                           |--Top(ROWCOUNT est 0)
                                |--Hash Match(Inner Join, HASH:([Parent].[Catcgid])=([c].[ParentID]), RESIDUAL:([DB].[dbo].[Category].[ParentID] as [c].[ParentID]=#ImpCats.[Catcgid] as [Parent].[Catcgid]))
                                     |--Filter(WHERE:(CASE WHEN [@n] = (2) THEN #ImpCats.[l1] as [Parent].[l1] ELSE CASE WHEN [@n] = (3) THEN #ImpCats.[l2] as [Parent].[l2] ELSE CASE WHEN [@n] = (4) THEN #ImpCats.[l3] as [Parent].[l3] ELSE CASE WHEN [@n] =
                                     |    |--Table Scan(OBJECT:([tempdb].[dbo].[#ImpCats] AS [Parent]))
                                     |--Clustered Index Scan(OBJECT:([DB].[dbo].[Category].[PK_Category] AS [c]), WHERE:([DB].[dbo].[Category].[Archived] as [c].[Archived]=(0) AND [DB].[dbo].[Category].[Deleted] as [c].[Deleted]=(0)))

(8 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	Set @n = @n + 1
  
End

-- Compile
insert 	into #Out (Outcgid, nLevel, nSubCats, nQuestions)
select	Catcgid, nLevel, nSubCats, Sum(Case When q.QuestionGlobalId is NULL Then 0 Else 1 End) as nQuestions
from	(
	select	Catcgid, nLevel, Sum(Case When c.CategoryGlobalId 

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Insert(OBJECT:([tempdb].[dbo].[#Out]), SET:([#Out].[Outcgid] = [#ImpCats].[Catcgid],[#Out].[nLevel] = [#ImpCats].[nLevel],[#Out].[nSubCats] = [Expr1010],[#Out].[nQuestions] = [Expr1014],[#Out].[nOrder] = [Expr1015]))
            |--Compute Scalar(DEFINE:([Expr1015]=getidentity((24982910),(2),N'#Out')))
                 |--Top(ROWCOUNT est 0)
                      |--Stream Aggregate(GROUP BY:([#ImpCats].[l1], [#ImpCats].[l2], [#ImpCats].[l3], [#ImpCats].[l4], [#ImpCats].[l5], [#ImpCats].[l6], [#ImpCats].[l7], [#ImpCats].[l8], [#ImpCats].[l9], [#ImpCats].[l10], [#ImpCats].[l11], [#ImpCats].[l12
                           |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [DB].[dbo].[Question].[QuestionGlobalID] as [q].[QuestionGlobalID] IS NULL THEN (0) ELSE (1) END))
                                |--Nested Loops(Left Outer Join, OUTER REFERENCES:([#ImpCats].[Catcgid]))
                                     |--Stream Aggregate(GROUP BY:([#ImpCats].[l1], [#ImpCats].[l2], [#ImpCats].[l3], [#ImpCats].[l4], [#ImpCats].[l5], [#ImpCats].[l6], [#ImpCats].[l7], [#ImpCats].[l8], [#ImpCats].[l9], [#ImpCats].[l10], [#ImpCats].[l11], 
                                     |    |--Compute Scalar(DEFINE:([Expr1017]=CASE WHEN [DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID] IS NULL THEN (0) ELSE (1) END))
                                     |         |--Nested Loops(Left Outer Join, WHERE:([DB].[dbo].[Category].[ParentID] as [c].[ParentID]=[#ImpCats].[Catcgid]))
                                     |              |--Sort(ORDER BY:([#ImpCats].[l1] ASC, [#ImpCats].[l2] ASC, [#ImpCats].[l3] ASC, [#ImpCats].[l4] ASC, [#ImpCats].[l5] ASC, [#ImpCats].[l6] ASC, [#ImpCats].[l7] ASC, [#ImpCats].[l8] ASC, [#ImpCats].[l9] AS
                                     |              |    |--Table Scan(OBJECT:([tempdb].[dbo].[#ImpCats]))
                                     |              |--Clustered Index Scan(OBJECT:([DB].[dbo].[Category].[PK_Category] AS [c]), WHERE:([DB].[dbo].[Category].[Archived] as [c].[Archived]=(0) AND [DB].[dbo].[Category].[Deleted] as [c].[Deleted]=(0)
                                     |--Filter(WHERE:([DB].[dbo].[Question].[Archived] as [q].[Archived]=(0) AND [DB].[dbo].[Question].[Deleted] as [q].[Deleted]=(0)))
                                          |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1011]))
                                               |--Index Seek(OBJECT:([DB].[dbo].[Question].[IX_Question_QuestionSequence] AS [q]), SEEK:([q].[CategoryGlobalID]=[#ImpCats].[Catcgid]) ORDERED FORWARD)
                                               |--RID Lookup(OBJECT:([DB].[dbo].[Question] AS [q]), SEEK:([Bmk1011]=[Bmk1011]) LOOKUP ORDERED FORWARD)

(16 row(s) affected)

StmtText
---------------------------------------------------------------------------------------
  
if exists (select * from SQSOption where OptionId = 'weightedcats' and Setting = 1)

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
            |--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
                 |--Constant Scan
                 |--Filter(WHERE:([DB].[dbo].[SQSOption].[Setting]=(1)))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [DB].[dbo].[SQSOption].[ClientID]))
                           |--Index Seek(OBJECT:([DB].[dbo].[SQSOption].[PK_SQSOption]), SEEK:([DB].[dbo].[SQSOption].[OptionID]=N'weightedcats') ORDERED FORWARD)
                           |--Clustered Index Seek(OBJECT:([DB].[dbo].[SQSOption].[IX_SQSOption_ClientId]), SEEK:([DB].[dbo].[SQSOption].[ClientID]=[DB].[dbo].[SQSOption].[ClientID] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

(7 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     begin
	-- Determine if every top level category has a weight and the sum is 100
	select @nTotCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[ParentID] IS NULL AND [DB].[dbo].[Category].[Archived]=(0) AND [Ty

(3 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	select @nWeightCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0 and CategoryWeight is NOT NULL

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[ParentID] IS NULL AND [DB].[dbo].[Category].[Archived]=(0) AND [Ty

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	select @nSum = sum(CategoryWeight) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0 and CategoryWeight is NOT NULL

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END))
                 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*), [Expr1005]=SUM([DB].[dbo].[Category].[CategoryWeight])))
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[ParentID] IS NULL AND [DB].[dbo].[Category].[Archived]=(0) AND [Ty

(3 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------
    	select @nSubCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NOT NULL and Archived = 0 and Deleted = 0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[Archived]=(0) AND [DB].[dbo].[Category].[Deleted]=(0) AND [DB].

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	if((@nWeightCnt > 0) and (((@nTotCnt - @nWeightCnt) > 0) or (@nSum <> 100) or (@nSubCnt > 0)))
       set @bCatWeightsOK = 0
  
end

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- RETURN    	select	#Out.nOrder, c.*, #Out.nLevel, CategoryViewLabel = Case When c.CategoryView = 1 Then @strCatList Else @strSingle End, 
		nSubCats, nQuestions, cl.CategoryName, @bCatWeightsOK as CatWeightsOK, cdd.categoryduedates
	from	Category c 
		join Cat

(4 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1010]=CASE WHEN [DB].[dbo].[Category].[CategoryView] as [c].[CategoryView]=(1) THEN [@strCatList] ELSE [@strSingle] END))
                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([c].[CategoryGlobalID]))
                      |--Nested Loops(Inner Join,

 

[campbeltr]

Query in New Window...

StmtText
--------------------------------------------------------------------------------------------------------------
declare @nId 		int--,
declare @nUserLang 	int

set @nId = 13067

set @nUserLang = 1

declare @uAgid 	uniqueidentifier

select @uAgid = AuditGlobalId  from  Audit where WebAuditId = @nId

(3 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |--Index Seek(OBJECT:([DB].[dbo].[ Audit].[PK_WebAudit]), SEEK:([DB].[dbo].[ Audit].[WebAuditID]=[@nId]) ORDERED FORWARD)
       |--RID Lookup(OBJECT:([DB].[dbo].[ Audit]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE  TABLE  #GetCats(	 nOrder  		int NOT NULL,
				 AuditGlobalId  		uniqueIdentifier NOT NULL,
				 CategoryGlobalId 	uniqueIdentifier NOT NULL,
				 ParentId  		uniqueIdentifier NULL,
				 CategoryIdentity  	int NOT NULL,
				 CategoryUniqueID  

DECLARE @SubCats TABLE  (	nOrder  			int NOT NULL,
				AuditGlobalId 		uniqueIdentifier NOT NULL,
				CategoryGlobalId  	uniqueIdentifier NOT NULL,
				ParentId  		uniqueIdentifier NULL,
				CategoryIdentity  	int NOT NULL,
				CategorySequence  	int

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Insert(OBJECT:([tempdb].[dbo].[#GetCats]), SET:([tempdb].[dbo].[#GetCats].[nOrder] = RaiseIfNull([ParameterTable].[nOrder]),[tempdb].[dbo].[#GetCats].[AuditGlobalId] = RaiseIfNull([ParameterTable].[AuditGlobalId]),[tempdb].[dbo].[#GetCats].[Cate
       |--Top(ROWCOUNT est 0)
            |--Parameter Table Scan

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  


CREATE  PROCEDURE ImpGetCategories


@uAgid		uniqueidentifier,
@nUserLang	int			= 1,
@uCgid		uniqueidentifier		= NULL,
@nLevel	int			= 1,
@nIdsOnly	int			= 0

AS

--------------------------------------------------------------------------
    


CREATE PROCEDURE SQSGetConfigItem

@nUserLang	int,
@strItemKey	nvarchar(100),
@strItemValue	nvarchar(4000)	Output

AS

select @strItemValue = isnull(ItemValue, StandardValue) from SQSConfig where LanguageKey = @nUserLang and ItemKey = @st

(2 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=isnull([DB].[dbo].[SQSConfig].[ItemValue],[DB].[dbo].[SQSConfig].[StandardValue])))
                 |--Clustered Index Seek(OBJECT:([DB].[dbo].[SQSConfig].[PK_SQSConfig]), SEEK:([DB].[dbo].[SQSConfig].[LanguageKey]=[@nUserLang] AND [DB].[dbo].[SQSConfig].[ItemKey]=[@strItemKey]) ORDERED FORWARD)

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
exec SQSGetConfigItem @nUserLang,  'labelSingleQuestion', @strSingle output
    


CREATE PROCEDURE SQSGetConfigItem

@nUserLang	int,
@strItemKey	nvarchar(100),
@strItemValue	nvarchar(4000)	Output

AS

select @strItemValue = isnull(ItemValue, StandardValue) from SQSConfig where LanguageKey = @nUserLang and ItemKey = @st

(2 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=isnull([DB].[dbo].[SQSConfig].[ItemValue],[DB].[dbo].[SQSConfig].[StandardValue])))
                 |--Clustered Index Seek(OBJECT:([DB].[dbo].[SQSConfig].[PK_SQSConfig]), SEEK:([DB].[dbo].[SQSConfig].[LanguageKey]=[@nUserLang] AND [DB].[dbo].[SQSConfig].[ItemKey]=[@strItemKey]) ORDERED FORWARD)

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
set @bCatWeightsOK = 1
  
create table #ImpCats (	Catcgid uniqueidentifier, l1 int NULL, l2 int NULL, l3 int NULL, l4 int NULL, l5 int NULL, l6 int NULL, l7 int NULL, l8 int NULL, 
			l9 int NULL, l10 int NULL,  l11 int NULL,  l12 int NULL,  l13 int NULL,  l14 int NULL,  l15 in
  
create table #Out (	nOrder int identity, Outcgid uniqueidentifier, nLevel int, nSubCats int NULL, nQuestions int NULL)


---------------------------------------------------------------------------------------------------------------------------------
  
insert 	into #ImpCats (Catcgid, l1, nLevel) 
select 	CategoryGlobalId, CategorySequence, 1 
from 	Category 
where 	AuditGlobalId = @uAgid 
	and (((@uCgid is NULL) and (ParentId is NULL)) or ((@uCgid is NOT NULL) and (ParentId = @uCgid)))
	and Archi

(4 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Insert(OBJECT:([tempdb].[dbo].[#ImpCats]), SET:([#ImpCats].[Catcgid] = [DB].[dbo].[Category].[CategoryGlobalID],[#ImpCats].[l1] = [DB].[dbo].[Category].[CategorySequence],[#ImpCats].[nLevel] = [Expr1007],[#ImpCats].[l2] = NULL,[#ImpCa
            |--Compute Scalar(DEFINE:([Expr1007]=(1)))
                 |--Top(ROWCOUNT est 0)
                      |--Sort(ORDER BY:([DB].[dbo].[Category].[CategorySequence] ASC))
                           |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[Archived]=(0) AND [DB].[dbo].[Category].[Deleted]=(0) AND ([@

(5 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
Set @n = 2
  
While (@n <= 15)
     Begin
	insert 	into #ImpCats
	select	c.CategoryGlobalId,
		Parent.l1,
		Case When @n = 2 Then c.CategorySequence Else Parent.l2 End,
		Case When @n = 3 Then c.CategorySequence Else Parent.l3 End,
		Case When @n = 4 Then c.CategorySequence Else P

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Table Insert(OBJECT:([tempdb].[dbo].[#ImpCats]), SET:([#ImpCats].[Catcgid] = [DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID],[#ImpCats].[l1] = #ImpCats.[l1] as [Parent].[l1],[#ImpCats].[l2] = [Expr1009],[#ImpCats].[l3] 
                 |--Table Spool
                      |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [@n] = (2) THEN [DB].[dbo].[Category].[CategorySequence] as [c].[CategorySequence] ELSE #ImpCats.[l2] as [Parent].[l2] END, [Expr1010]=CASE WHEN [@n] = (3) THEN [DB].[dbo].[Category
                           |--Top(ROWCOUNT est 0)
                                |--Hash Match(Inner Join, HASH:([Parent].[Catcgid])=([c].[ParentID]), RESIDUAL:([DB].[dbo].[Category].[ParentID] as [c].[ParentID]=#ImpCats.[Catcgid] as [Parent].[Catcgid]))
                                     |--Filter(WHERE:(CASE WHEN [@n] = (2) THEN #ImpCats.[l1] as [Parent].[l1] ELSE CASE WHEN [@n] = (3) THEN #ImpCats.[l2] as [Parent].[l2] ELSE CASE WHEN [@n] = (4) THEN #ImpCats.[l3] as [Parent].[l3] ELSE CASE WHEN [@n] =
                                     |    |--Table Scan(OBJECT:([tempdb].[dbo].[#ImpCats] AS [Parent]))
                                     |--Clustered Index Scan(OBJECT:([DB].[dbo].[Category].[PK_Category] AS [c]), WHERE:([DB].[dbo].[Category].[Archived] as [c].[Archived]=(0) AND [DB].[dbo].[Category].[Deleted] as [c].[Deleted]=(0)))

(8 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	Set @n = @n + 1
  
End

-- Compile
insert 	into #Out (Outcgid, nLevel, nSubCats, nQuestions)
select	Catcgid, nLevel, nSubCats, Sum(Case When q.QuestionGlobalId is NULL Then 0 Else 1 End) as nQuestions
from	(
	select	Catcgid, nLevel, Sum(Case When c.CategoryGlobalId 

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Table Insert(OBJECT:([tempdb].[dbo].[#Out]), SET:([#Out].[Outcgid] = [#ImpCats].[Catcgid],[#Out].[nLevel] = [#ImpCats].[nLevel],[#Out].[nSubCats] = [Expr1010],[#Out].[nQuestions] = [Expr1014],[#Out].[nOrder] = [Expr1015]))
            |--Compute Scalar(DEFINE:([Expr1015]=getidentity((24982910),(2),N'#Out')))
                 |--Top(ROWCOUNT est 0)
                      |--Stream Aggregate(GROUP BY:([#ImpCats].[l1], [#ImpCats].[l2], [#ImpCats].[l3], [#ImpCats].[l4], [#ImpCats].[l5], [#ImpCats].[l6], [#ImpCats].[l7], [#ImpCats].[l8], [#ImpCats].[l9], [#ImpCats].[l10], [#ImpCats].[l11], [#ImpCats].[l12
                           |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [DB].[dbo].[Question].[QuestionGlobalID] as [q].[QuestionGlobalID] IS NULL THEN (0) ELSE (1) END))
                                |--Nested Loops(Left Outer Join, OUTER REFERENCES:([#ImpCats].[Catcgid]))
                                     |--Stream Aggregate(GROUP BY:([#ImpCats].[l1], [#ImpCats].[l2], [#ImpCats].[l3], [#ImpCats].[l4], [#ImpCats].[l5], [#ImpCats].[l6], [#ImpCats].[l7], [#ImpCats].[l8], [#ImpCats].[l9], [#ImpCats].[l10], [#ImpCats].[l11], 
                                     |    |--Compute Scalar(DEFINE:([Expr1017]=CASE WHEN [DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID] IS NULL THEN (0) ELSE (1) END))
                                     |         |--Nested Loops(Left Outer Join, WHERE:([DB].[dbo].[Category].[ParentID] as [c].[ParentID]=[#ImpCats].[Catcgid]))
                                     |              |--Sort(ORDER BY:([#ImpCats].[l1] ASC, [#ImpCats].[l2] ASC, [#ImpCats].[l3] ASC, [#ImpCats].[l4] ASC, [#ImpCats].[l5] ASC, [#ImpCats].[l6] ASC, [#ImpCats].[l7] ASC, [#ImpCats].[l8] ASC, [#ImpCats].[l9] AS
                                     |              |    |--Table Scan(OBJECT:([tempdb].[dbo].[#ImpCats]))
                                     |              |--Clustered Index Scan(OBJECT:([DB].[dbo].[Category].[PK_Category] AS [c]), WHERE:([DB].[dbo].[Category].[Archived] as [c].[Archived]=(0) AND [DB].[dbo].[Category].[Deleted] as [c].[Deleted]=(0)
                                     |--Filter(WHERE:([DB].[dbo].[Question].[Archived] as [q].[Archived]=(0) AND [DB].[dbo].[Question].[Deleted] as [q].[Deleted]=(0)))
                                          |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1011]))
                                               |--Index Seek(OBJECT:([DB].[dbo].[Question].[IX_Question_QuestionSequence] AS [q]), SEEK:([q].[CategoryGlobalID]=[#ImpCats].[Catcgid]) ORDERED FORWARD)
                                               |--RID Lookup(OBJECT:([DB].[dbo].[Question] AS [q]), SEEK:([Bmk1011]=[Bmk1011]) LOOKUP ORDERED FORWARD)

(16 row(s) affected)

StmtText
---------------------------------------------------------------------------------------
  
if exists (select * from SQSOption where OptionId = 'weightedcats' and Setting = 1)

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
            |--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
                 |--Constant Scan
                 |--Filter(WHERE:([DB].[dbo].[SQSOption].[Setting]=(1)))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [DB].[dbo].[SQSOption].[ClientID]))
                           |--Index Seek(OBJECT:([DB].[dbo].[SQSOption].[PK_SQSOption]), SEEK:([DB].[dbo].[SQSOption].[OptionID]=N'weightedcats') ORDERED FORWARD)
                           |--Clustered Index Seek(OBJECT:([DB].[dbo].[SQSOption].[IX_SQSOption_ClientId]), SEEK:([DB].[dbo].[SQSOption].[ClientID]=[DB].[dbo].[SQSOption].[ClientID] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

(7 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     begin
	-- Determine if every top level category has a weight and the sum is 100
	select @nTotCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[ParentID] IS NULL AND [DB].[dbo].[Category].[Archived]=(0) AND [Ty

(3 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	select @nWeightCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0 and CategoryWeight is NOT NULL

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[ParentID] IS NULL AND [DB].[dbo].[Category].[Archived]=(0) AND [Ty

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	select @nSum = sum(CategoryWeight) from Category where AuditGlobalId = @uAgid and ParentId is NULL and Archived = 0 and Deleted = 0 and CategoryWeight is NOT NULL

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END))
                 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*), [Expr1005]=SUM([DB].[dbo].[Category].[CategoryWeight])))
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[ParentID] IS NULL AND [DB].[dbo].[Category].[Archived]=(0) AND [Ty

(3 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------
    	select @nSubCnt = count(*) from Category where AuditGlobalId = @uAgid and ParentId is NOT NULL and Archived = 0 and Deleted = 0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
                 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category]), SEEK:([DB].[dbo].[Category].[AuditGlobalID]=[@uAgid]),  WHERE:([DB].[dbo].[Category].[Archived]=(0) AND [DB].[dbo].[Category].[Deleted]=(0) AND [DB].

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	if((@nWeightCnt > 0) and (((@nTotCnt - @nWeightCnt) > 0) or (@nSum <> 100) or (@nSubCnt > 0)))
       set @bCatWeightsOK = 0
  
end

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- RETURN

    	select	#Out.nOrder, c.*, #Out.nLevel, CategoryViewLabel = Case When c.CategoryView = 1 Then @strCatList Else @strSingle End, 
		nSubCats, nQuestions, cl.CategoryName, @bCatWeightsOK as CatWeightsOK, cdd.categoryduedates
	from	Category c 
		join Cat

(4 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |--Compute Scalar(DEFINE:([Expr1010]=CASE WHEN [DB].[dbo].[Category].[CategoryView] as [c].[CategoryView]=(1) THEN [@strCatList] ELSE [@strSingle] END))
                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([c].[CategoryGlobalID]))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                      |    |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                      |    |    |--Sort(ORDER BY:([#Out].[nOrder] ASC))
                      |    |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#Out]))
                      |    |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category] AS [c]), SEEK:([c].[AuditGlobalID]=[@uAgid] AND [c].[CategoryGlobalID]=[#Out].[Outcgid]) ORDERED FORWARD)
                      |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[CategoryLanguage].[PK_CategoryLanguage] AS [cl]), SEEK:([cl].[CategoryGlobalID]=[#Out].[Outcgid] AND [cl].[LanguageKey]=[@nUserLang]) ORDERED FORWARD)
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[CategoryDueDates].[PK_CategoryDueDates] AS [cdd]), SEEK:([cdd].[CategoryGlobalID]=[DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID]) ORDERED FORWARD)

(9 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
Else If (@nIdsOnly = 2)
      	select	#Out.nOrder, c.CategoryGlobalId, c.ParentId, c.CategoryReference, #OUT.nLevel, c.Archived, c.Deleted
	from	Category c, CategoryLanguage cl, #Out
	where	c.AuditGlobalId = @uAgid
		and c.CategoryGlobalId = #Out.Outcgid
		and c.CategoryGloba

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                      |    |--Sort(ORDER BY:([#Out].[nOrder] ASC))
                      |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#Out]))
                      |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category] AS [c]), SEEK:([c].[AuditGlobalID]=[@uAgid] AND [c].[CategoryGlobalID]=[#Out].[Outcgid]) ORDERED FORWARD)
                      |--Clustered Index Seek(OBJECT:([DB].[dbo].[CategoryLanguage].[PK_CategoryLanguage] AS [cl]), SEEK:([cl].[CategoryGlobalID]=[#Out].[Outcgid] AND [cl].[LanguageKey]=[@nUserLang]) ORDERED FORWARD)

(6 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      
Else
	select	#Out.nOrder, c.CategoryGlobalId
	from	Category c, CategoryLanguage cl, #Out
	where	c.AuditGlobalId = @uAgid
		and c.CategoryGlobalId = #Out.Outcgid
		and c.CategoryGlobalId = cl.CategoryGlobalId
		and cl.LanguageKey = @nUserLang

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                 |--Sort(ORDER BY:([#Out].[nOrder] ASC))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([#Out].[Outcgid]))
                           |    |--Table Scan(OBJECT:([tempdb].[dbo].[#Out]))
                           |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[Category].[PK_Category] AS [c]), SEEK:([c].[AuditGlobalID]=[@uAgid] AND [c].[CategoryGlobalID]=[#Out].[Outcgid]) ORDERED FORWARD)
                           |--Clustered Index Seek(OBJECT:([DB].[dbo].[CategoryLanguage].[PK_CategoryLanguage] AS [cl]), SEEK:([cl].[CategoryGlobalID]=[#Out].[Outcgid] AND [cl].[LanguageKey]=[@nUserLang]) ORDERED FORWARD)

(6 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
drop table #Out
  
drop table #ImpCats

insert 	@SubCats
select 	nOrder, AuditGlobalId, CategoryGlobalId, ParentId, CategoryIdentity, CategorySequence, CategoryName, 
	CategoryView, NULL, CategoryAnswer, ReAuditCategory, nLevel, nSubCats, nQuestions, 0, 0
from 	#GetCats

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Insert(OBJECT:(@SubCats), SET:([nOrder] = [tempdb].[dbo].[#GetCats].[nOrder],[AuditGlobalId] = [tempdb].[dbo].[#GetCats].[AuditGlobalId],[CategoryGlobalId] = [tempdb].[dbo].[#GetCats].[CategoryGlobalId],[ParentId] = [tempdb].[dbo].[#GetCats].[Pa
       |--Compute Scalar(DEFINE:([Expr1008]=NULL, [Expr1009]=(0), [Expr1010]=(0)))
            |--Top(ROWCOUNT est 0)
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#GetCats]))

(4 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

insert into @IQ
select 	q.QuestionIdentity,
	0, -- DynamicInclude
	qrh.DynamicInclude,-- QuestionREsultn
	CASE  when cql.QuestionGlobalID IS NULL then 0 ELSE 1 end as triggered,
	CASE  when Activated.QActivated IS NULL then 0 When Activated.QActivate

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Insert(OBJECT:(@IQ), SET:([QuestionIdentity] = [DB].[dbo].[Question].[QuestionIdentity] as [q].[QuestionIdentity],[DynamicInclude] = [Expr1040],[QuestionResult] = [Expr1041],[Triggered] = [Expr1042],[activated] = [Expr1043],[AuditGlobalID] = 
       |--Compute Scalar(DEFINE:([Expr1040]=(0), [Expr1043]=CONVERT_IMPLICIT(bit,CASE WHEN [Expr1035] IS NULL THEN (0) ELSE CASE WHEN [Expr1035]=(0) THEN (0) ELSE (1) END END,0)))
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1042]=CONVERT_IMPLICIT(bit,CASE WHEN [DB].[dbo].[ChoiceQuestionList].[QuestionGlobalID] as [cql].[QuestionGlobalID] IS NULL THEN (0) ELSE (1) END,0)))
                      |--Hash Match(Left Outer Join, HASH:([q].[QuestionGlobalID])=([cql].[QuestionGlobalID]), RESIDUAL:([DB].[dbo].[ChoiceQuestionList].[QuestionGlobalID] as [cql].[QuestionGlobalID]=[DB].[dbo].[Question].[QuestionGlobalID] as [q].[Q
                           |--Hash Match(Left Outer Join, HASH:([q].[QuestionGlobalID])=([aq].[QuestionGlobalID]), RESIDUAL:([DB].[dbo].[Question].[QuestionGlobalID] as [aq].[QuestionGlobalID]=[DB].[dbo].[Question].[QuestionGlobalID] as [q].[Question
                           |    |--Compute Scalar(DEFINE:([Expr1041]=CONVERT_IMPLICIT(int,[DB].[dbo].[ QuestionResultHistory].[DynamicInclude] as [qrh].[DynamicInclude],0)))
                           |    |    |--Hash Match(Right Outer Join, HASH:([qrh].[QuestionIdentity])=([q].[QuestionIdentity]))
                           |    |         |--Top(TOP EXPRESSION:((1)))
                           |    |         |    |--Segment
                           |    |         |         |--Clustered Index Seek(OBJECT:([DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory] AS [qrh]), SEEK:([qrh].[WebAuditID]=[@nId]) ORDERED BACKWARD)
                           |    |         |--Hash Match(Inner Join, HASH:([q].[CategoryGlobalID])=([c].[CategoryGlobalID]), RESIDUAL:([DB].[dbo].[Category].[CategoryGlobalID] as [c].[CategoryGlobalID]=[DB].[dbo].[Question].[CategoryGlobalID] as [q].[
                           |    |              |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004], [Expr1058]) WITH UNORDERED PREFETCH)
                           |    |              |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ma].[AuditGlobalID]))
                           |    |              |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1007]))
                           |    |              |    |    |    |--Index Seek(OBJECT:([DB].[dbo].[ Audit].[PK_WebAudit] AS [ma]), SEEK:([ma].[WebAuditID]=[@nId]) ORDERED FORWARD)
                           |    |              |    |    |    |--RID Lookup(OBJECT:([DB].[dbo].[ Audit] AS [ma]), SEEK:([Bmk1007]=[Bmk1007]) LOOKUP ORDERED FORWARD)
                           |    |              |    |    |--Index Seek(OBJECT:([DB].[dbo].[Question].[PK_Question] AS [q]), SEEK:([q].[AuditGlobalID]=[DB].[dbo].[ Audit].[AuditGlobalID] as [ma].[AuditGlobalID]) ORDERED FORWARD)
                           |    |              |    |--RID Lookup(OBJECT:([DB].[dbo].[Question] AS [q]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)
                           |    |              |--Index Scan(OBJECT:([DB].[dbo].[Category].[IX_CategoryGlobalId] AS [c]))
                           |    |--Compute Scalar(DEFINE:([Expr1035]=[Expr1035]))
                           |         |--Compute Scalar(DEFINE:([Expr1035]=CONVERT_IMPLICIT(int,[Expr1063],0)))
                           |              |--Stream Aggregate(GROUP BY:([aq].[QuestionGlobalID]) DEFINE:([Expr1063]=Count(*)))
                           |                   |--Sort(ORDER BY:([aq].[QuestionGlobalID] ASC))
                           |                        |--Hash Match(Inner Join, HASH:([cql].[QuestionGlobalID])=([aq].[QuestionGlobalID]), RESIDUAL:([DB].[dbo].[Question].[QuestionGlobalID] as [aq].[QuestionGlobalID]=[DB].[dbo].[ChoiceQuestionList].[Qu
                           |                             |--Hash Match(Inner Join, HASH:([tc].[ChoiceGlobalID])=([cql].[ChoiceGlobalID]), RESIDUAL:([DB].[dbo].[ChoiceQuestionList].[ChoiceGlobalID] as [cql].[ChoiceGlobalID]=[DB].[dbo].[Choice].[Choice
                           |                             |    |--Filter(WHERE:([DB].[dbo].[Question].[QuestionIdentity] as [tq].[QuestionIdentity]=[DB].[dbo].[ QuestionResultHistory].[QuestionIdentity]))
                           |                             |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1026], [Expr1062]) WITH UNORDERED PREFETCH)
                           |                             |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([tc].[QuestionGlobalID], [Expr1061]) WITH UNORDERED PREFETCH)
                           |                             |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tc].[AuditGlobalID], [tc].[ChoiceGlobalID], [Expr1060]) WITH UNORDERED PREFETCH)
                           |                             |    |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tqrh].[ChoiceIdentity], [Expr1059]) WITH UNORDERED PREFETCH)
                           |                             |    |         |    |    |    |--Hash Match(Inner Join, HASH:([DB].[dbo].[ QuestionResultHistory].[QuestionIdentity], [Expr1034])=([tqrh].[QuestionIdentity], [tqrh].[TimeStamp]), RESIDUAL:([Tys
                           |                             |    |         |    |    |    |    |--Stream Aggregate(GROUP BY:([DB].[dbo].[ QuestionResultHistory].[QuestionIdentity]) DEFINE:([Expr1034]=MAX([DB].[dbo].[ QuestionResultHistory].[TimeSt
                           |                             |    |         |    |    |    |    |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory]), SEEK:([DB].[dbo].[ QuestionResultHistory].[Web
                           |                             |    |         |    |    |    |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory] AS [tqrh]), SEEK:([tqrh].[WebAuditID]=[@nId]),  WHERE:([Tys
                           |                             |    |         |    |    |    |--Index Seek(OBJECT:([DB].[dbo].[Choice].[IX_Choice] AS [tc]), SEEK:([tc].[ChoiceIdentity]=[DB].[dbo].[ QuestionResultHistory].[ChoiceIdentity] as [tqrh].[Choi
                           |                             |    |         |    |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[Choice].[PK_Choice] AS [tc]), SEEK:([tc].[AuditGlobalID]=[DB].[dbo].[Choice].[AuditGlobalID] as [tc].[AuditGlobalID] AND [tc
                           |                             |    |         |    |--Index Seek(OBJECT:([DB].[dbo].[Question].[IX_QuestionGlobalId] AS [tq]), SEEK:([tq].[QuestionGlobalID]=[DB].[dbo].[Choice].[QuestionGlobalID] as [tc].[QuestionGlobalID]) 
                           |                             |    |         |--RID Lookup(OBJECT:([DB].[dbo].[Question] AS [tq]), SEEK:([Bmk1026]=[Bmk1026]) LOOKUP ORDERED FORWARD)
                           |                             |    |--Clustered Index Scan(OBJECT:([DB].[dbo].[ChoiceQuestionList].[PK_ChoiceQuestionList] AS [cql]))
                           |                             |--Index Scan(OBJECT:([DB].[dbo].[Question].[IX_QuestionGlobalId] AS [aq]))
                           |--Clustered Index Scan(OBJECT:([DB].[dbo].[ChoiceQuestionList].[PK_ChoiceQuestionList] AS [cql]))

(42 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------

update 	@IQ
set 	dynamicinclude = 1
where	(questionresult = 1 and triggered = 0)
	or (triggered = 0)
	or (triggered = 1 and activated = 1)

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------
  |--Table Update(OBJECT:(@IQ), SET:([DynamicInclude] = [Expr1004]))
       |--Compute Scalar(DEFINE:([Expr1004]=(1)))
            |--Top(ROWCOUNT est 0)
                 |--Table Scan(OBJECT:(@IQ), WHERE:([Triggered]=(0) OR [Triggered]=(1) AND [activated]=(1)) ORDERED)

(4 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

update sc
set nCatCompleted = 1
from @SubCats SC
left join 
(
		select 	q.CategoryGlobalID, count(*) as catCount
		from 	Question q
		join @iq iq 
			on q.AuditGlobalID = iq.AuditGlobalID
			and q.CategoryGlobalid = iq.CategoryGlobalID
			and q.

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Update(OBJECT:(@SubCats), SET:([nCatCompleted] = [Expr1032]))
       |--Compute Scalar(DEFINE:([Expr1032]=(1)))
            |--Top(ROWCOUNT est 0)
                 |--Filter(WHERE:(isnull([Expr1009],(0))=isnull([Expr1029],(0))))
                      |--Nested Loops(Left Outer Join, OUTER REFERENCES:([q].[CategoryGlobalID]))
                           |--Nested Loops(Left Outer Join, OUTER REFERENCES:([SC].[CategoryGlobalId]))
                           |    |--Table Scan(OBJECT:(@SubCats AS [SC]))
                           |    |--Compute Scalar(DEFINE:([q].[CategoryGlobalID]=[DB].[dbo].[Question].[CategoryGlobalID] as [q].[CategoryGlobalID], [Expr1009]=[Expr1009]))
                           |         |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1050],0)))
                           |              |--Stream Aggregate(DEFINE:([Expr1050]=Count(*), [q].[CategoryGlobalID]=ANY([DB].[dbo].[Question].[CategoryGlobalID] as [q].[CategoryGlobalID])))
                           |                   |--Filter(WHERE:([DB].[dbo].[Question].[AuditGlobalID] as [q].[AuditGlobalID]=@iq.[AuditGlobalID] as [iq].[AuditGlobalID] AND [DB].[dbo].[Question].[CategoryGlobalID] as [q].[CategoryGlobalID]=@SubCats.[
                           |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003]))
                           |                             |--Nested Loops(Inner Join, OUTER REFERENCES:([iq].[QuestionIdentity]))
                           |                             |    |--Table Scan(OBJECT:(@iq AS [iq]), WHERE:(@iq.[CategoryGlobalID] as [iq].[CategoryGlobalID]=@SubCats.[CategoryGlobalId] as [SC].[CategoryGlobalId] AND @iq.[DynamicInclude] as [iq].[DynamicInclu
                           |                             |    |--Index Seek(OBJECT:([DB].[dbo].[Question].[IX_Question_QuestionIdentity] AS [q]), SEEK:([q].[QuestionIdentity]=@iq.[QuestionIdentity] as [iq].[QuestionIdentity]) ORDERED FORWARD)
                           |                             |--RID Lookup(OBJECT:([DB].[dbo].[Question] AS [q]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD)
                           |--Compute Scalar(DEFINE:([Expr1029]=[Expr1029]))
                                |--Compute Scalar(DEFINE:([Expr1029]=CONVERT_IMPLICIT(int,[Expr1051],0)))
                                     |--Stream Aggregate(DEFINE:([Expr1051]=Count(*)))
                                          |--Filter(WHERE:([DB].[dbo].[ QuestionResultHistory].[TimeStamp] as [mqrh].[TimeStamp]=[Expr1027]))
                                               |--Stream Aggregate(GROUP BY:([Bmk1016], [mqrh].[TimeStamp]) DEFINE:([Expr1027]=MAX([DB].[dbo].[ QuestionResultHistory].[TimeStamp])))
                                                    |--Nested Loops(Inner Join, OUTER REFERENCES:([iq].[QuestionIdentity]))
                                                         |--Nested Loops(Inner Join, OUTER REFERENCES:([iq].[QuestionIdentity]))
                                                         |    |--Sort(ORDER BY:([Bmk1016] ASC))
                                                         |    |    |--Filter(WHERE:([Expr1038]=(1)))
                                                         |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([mqr].[QuestionIdentity]))
                                                         |    |              |--Nested Loops(Inner Join, OUTER REFERENCES:([iq].[QuestionIdentity]))
                                                         |    |              |    |--Table Scan(OBJECT:(@IQ AS [iq]), WHERE:([DB].[dbo].[Question].[CategoryGlobalID] as [q].[CategoryGlobalID]=@IQ.[CategoryGlobalID] as [iq].[CategoryGlobalID] AND @IQ.[Dy
                                                         |    |              |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[ QuestionResult].[PK_ QuestionResult] AS [mqr]), SEEK:([mqr].[WebAuditID]=[@nId] AND [mqr].[QuestionIdentity]=@IQ.[Questi
                                                         |    |              |--Assert(WHERE:(CASE WHEN [Expr1037]>(1) THEN (0) ELSE NULL END))
                                                         |    |                   |--Stream Aggregate(DEFINE:([Expr1037]=Count(*), [Expr1038]=ANY([DB].[dbo].[Question].[Required])))
                                                         |    |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1019]))
                                                         |    |                             |--Index Seek(OBJECT:([DB].[dbo].[Question].[IX_Question_QuestionIdentity]), SEEK:([DB].[dbo].[Question].[QuestionIdentity]=[DB].[dbo].[ QuestionResult]
                                                         |    |                             |--RID Lookup(OBJECT:([DB].[dbo].[Question]), SEEK:([Bmk1019]=[Bmk1019]) LOOKUP ORDERED FORWARD)
                                                         |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[ QuestionResultHistory].[PK_ QuestionResultHistory] AS [mqrh]), SEEK:([mqrh].[WebAuditID]=[@nId] AND [mqrh].[QuestionIdentity]=@IQ.[QuestionI
                                                         |--Index Seek(OBJECT:([DB].[dbo].[ QuestionResultHistory].[IX_ QuestionResultHistoryIdentity]), SEEK:([DB].[dbo].[ QuestionResultHistory].[QuestionIdentity]=@IQ.[QuestionIdentity] as [

(36 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------

update 	s1
set 	ParentIdentity = s2.CategoryIdentity 
from 	@SubCats s1, @SubCats s2
where 	s1.ParentId = s2.CategoryGlobalId

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Update(OBJECT:(@SubCats), SET:([ParentIdentity] = @SubCats.[CategoryIdentity] as [s2].[CategoryIdentity]))
       |--Table Spool
            |--Top(ROWCOUNT est 0)
                 |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([s2].[CategoryIdentity]=ANY(@SubCats.[CategoryIdentity] as [s2].[CategoryIdentity])))
                      |--Nested Loops(Inner Join, WHERE:(@SubCats.[CategoryGlobalId] as [s2].[CategoryGlobalId]=@SubCats.[ParentId] as [s1].[ParentId]))
                           |--Table Scan(OBJECT:(@SubCats AS [s1]))
                           |--Table Scan(OBJECT:(@SubCats AS [s2]))

(7 row(s) affected)

StmtText
----------------------------------------------------------------------

Update 	@SubCats
Set 	nSubCatsCompleted = 1
where 	nSubCats = 0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------
  |--Table Update(OBJECT:(@SubCats), SET:([nSubCatsCompleted] = [Expr1004]))
       |--Compute Scalar(DEFINE:([Expr1004]=(1)))
            |--Top(ROWCOUNT est 0)
                 |--Table Scan(OBJECT:(@SubCats), WHERE:([nSubCats]=(0)) ORDERED)

(4 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

update 	s
set 	nSubCatsCompleted = 1
from	@SubCats s
where 	s.nSubCats > 0
	and (select count(*) from @SubCats where parentId = s.CategoryGlobalId and nCatCompleted = 1 and nSubCatsCompleted = 1) = s.nSubCats

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Update(OBJECT:(@SubCats), SET:([nSubCatsCompleted] = [Expr1009]))
       |--Compute Scalar(DEFINE:([Expr1009]=(1)))
            |--Table Spool
                 |--Top(ROWCOUNT est 0)
                      |--Filter(WHERE:(CASE WHEN [Expr1007] IS NULL THEN (0) ELSE [Expr1007] END=@SubCats.[nSubCats] as [s].[nSubCats]))
                           |--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[CategoryGlobalId]))
                                |--Table Scan(OBJECT:(@SubCats AS [s]), WHERE:(@SubCats.[nSubCats] as [s].[nSubCats]>(0)) ORDERED)
                                |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1020],0)))
                                     |--Stream Aggregate(DEFINE:([Expr1020]=Count(*)))
                                          |--Table Scan(OBJECT:(@SubCats), WHERE:([ParentId]=@SubCats.[CategoryGlobalId] as [s].[CategoryGlobalId] AND [nCatCompleted]=(1) AND [nSubCatsCompleted]=(1)))

(10 row(s) affected)

StmtText
----------------------------------------------

select	*
from	@SubCats
order 	by nOrder

(1 row(s) affected)

StmtText
----------------------------------------
  |--Sort(ORDER BY:([nOrder] ASC))
       |--Table Scan(OBJECT:(@SubCats))

(2 row(s) affected)

StmtText
-----------------------

DROP TABLE #GetCats

(1 row(s) affected)

 

[campbeltr]

Were these the execution plans not useful, too large, or unreadable? I am still stumped by this, so any thoughts would be appreciated.

 

[SQLDenis]

I am sorry...but it jst too big


here is what you can do


have the proc and the code in 1 window, hit CTRL + K (in query analyzer) then F5

this will execute the code and show you the execution plan graphically

now look where the percentages are the most different focus on that code and post that code here


btw what makes you think it is not parameter sniffing?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[campbeltr]

have the proc and the code in 1 window, hit CTRL + K (in query analyzer) then F5"

I know doesn't sound dificult, but somehow it is stumping me. I notice you mention the use of "query analyzer", which I am familiar with in SQL Server 2000, though I am currently using SQL Server 2005. Is there a way to accomplish this in 2005?

 

[gmmastros]

In SQL Server Management Studio, press CTRL+M.

Load your query
Press CTRL+M (it won't appear to have done anything)
Run your query
There will now be a new tab named "Execution Plan"



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom