Query runs forever.

I have the following stored procedure

CREATE PROCEDURE spPP_New_GetUserCanWrite
@CategoryId int,
@PersonId int
AS
SET NOCOUNT ON

Declare @CanWrite bit
Declare @RetVal bit

Set @RetVal=0
WHILE (@CategoryId>0)
BEGIN
SELECT @CanWrite=CanWrite
FROM tblPP_New_CategorySecurity
WHERE CategoryId=@CategoryId
AND PersonId=@PersonId

IF (@CanWrite =1) SET @RetVal=1
SELECT @CategoryId=[ParentId]
FROM tblPP_New_Categories
WHERE [ID]=@CategoryId

END
SELECT @RetVal as CanWrite

GO

It sometimes runs forever. I have a feeling it's something to do with the
second SELECT which should recurse through the table from the given
categoryId up to the root category(which has a ParentId of 0)

I passed in a CategoryId of 18 whose ParentId is 17 whose ParentId is 0 so
it should have only looped twice.

The given Personid would have returned no records for the first SELECT.
The expected result therefore would be 0.


The relevant tables are....

tblPP_New_CategorySecurity
PersonId int
CategoryId int
CanWrite bit

tblPP_New_Categories
CategoryId int
ParentId int
Title varchar(50)

Any idea what I'm missing?

Thanks

Re: Query runs forever.

Tom B wrote:
> I have the following stored procedure
>
> CREATE PROCEDURE spPP_New_GetUserCanWrite
> @CategoryId int,
> @PersonId int
> AS
> SET NOCOUNT ON
>
> Declare @CanWrite bit
> Declare @RetVal bit
>
> Set @RetVal=0
> WHILE (@CategoryId>0)
> BEGIN
> SELECT @CanWrite=CanWrite
> FROM tblPP_New_CategorySecurity
> WHERE CategoryId=@CategoryId
> AND PersonId=@PersonId
>
> IF (@CanWrite =1) SET @RetVal=1
> SELECT @CategoryId=[ParentId]
> FROM tblPP_New_Categories
> WHERE [ID]=@CategoryId
>
> END
> SELECT @RetVal as CanWrite
>
> GO
>
> It sometimes runs forever.

Can you reliably reproduce the situation where it hangs? If so, you should
be able to use SQL Profiler to see what's going on. If you are not familiar
with that tool, you should immediately fire it up right now (I'll wait),
connect to your server and start a trace. Then edit the properties of your
trace to add the stored procedures events: sp_statementstarting, etc. Start
the trace and then execute the procedure so you can see what is happening.


> I have a feeling it's something to do
> with the second SELECT which should recurse through the table from
> the given categoryId up to the root category(which has a ParentId of
> 0)
SELECT statements do not recurse. WHILE loops do...

> I passed in a CategoryId of 18 whose ParentId is 17 whose ParentId is
> 0 so it should have only looped twice.
>
> The given Personid would have returned no records for the first
> SELECT. The expected result therefore would be 0.
>

Given that your tables are correctly indexed, there may be a deadlocking
issue, which you can resolve by using the WITH (nolock) hint in your FROM
clause.
There may also be a "parameter sniffing" situation, which you can relieve by
modifying your procedure to assign the parameter values to local variables
which you then use in your SELECTs instead of the parameters.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Query runs forever.

THanks Bob,

Sorry, I was out for a bit.

I did use Profiler -- but since it's a stored procedure I don't see anything
happening other than the stored procedure executing.

I'll take a look at BOL to see if I can figure out what you mean by the WITH
hint and the parameter sniffing.

Thanks for the suggestions.

Oh, and yes, it's easily reproduced by putting in certain values.

THanks again.

Tom B


"Bob Barrows [MVP]" wrote in message
news:epmSoDHvFHA.3472@TK2MSFTNGP10.phx.gbl...
> Tom B wrote:
>> I have the following stored procedure
>>
>> CREATE PROCEDURE spPP_New_GetUserCanWrite
>> @CategoryId int,
>> @PersonId int
>> AS
>> SET NOCOUNT ON
>>
>> Declare @CanWrite bit
>> Declare @RetVal bit
>>
>> Set @RetVal=0
>> WHILE (@CategoryId>0)
>> BEGIN
>> SELECT @CanWrite=CanWrite
>> FROM tblPP_New_CategorySecurity
>> WHERE CategoryId=@CategoryId
>> AND PersonId=@PersonId
>>
>> IF (@CanWrite =1) SET @RetVal=1
>> SELECT @CategoryId=[ParentId]
>> FROM tblPP_New_Categories
>> WHERE [ID]=@CategoryId
>>
>> END
>> SELECT @RetVal as CanWrite
>>
>> GO
>>
>> It sometimes runs forever.
>
> Can you reliably reproduce the situation where it hangs? If so, you should
> be able to use SQL Profiler to see what's going on. If you are not
> familiar with that tool, you should immediately fire it up right now (I'll
> wait), connect to your server and start a trace. Then edit the properties
> of your trace to add the stored procedures events: sp_statementstarting,
> etc. Start the trace and then execute the procedure so you can see what is
> happening.
>
>
>> I have a feeling it's something to do
>> with the second SELECT which should recurse through the table from
>> the given categoryId up to the root category(which has a ParentId of
>> 0)
> SELECT statements do not recurse. WHILE loops do...
>
>> I passed in a CategoryId of 18 whose ParentId is 17 whose ParentId is
>> 0 so it should have only looped twice.
>>
>> The given Personid would have returned no records for the first
>> SELECT. The expected result therefore would be 0.
>>
>
> Given that your tables are correctly indexed, there may be a deadlocking
> issue, which you can resolve by using the WITH (nolock) hint in your FROM
> clause.
> There may also be a "parameter sniffing" situation, which you can relieve
> by modifying your procedure to assign the parameter values to local
> variables which you then use in your SELECTs instead of the parameters.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Query runs forever.

Sorry - I just reread your response and didn't see the part about adding the
stored procedure events. I'll try that now.

Thanks again


"Bob Barrows [MVP]" wrote in message
news:epmSoDHvFHA.3472@TK2MSFTNGP10.phx.gbl...
> Tom B wrote:
>> I have the following stored procedure
>>
>> CREATE PROCEDURE spPP_New_GetUserCanWrite
>> @CategoryId int,
>> @PersonId int
>> AS
>> SET NOCOUNT ON
>>
>> Declare @CanWrite bit
>> Declare @RetVal bit
>>
>> Set @RetVal=0
>> WHILE (@CategoryId>0)
>> BEGIN
>> SELECT @CanWrite=CanWrite
>> FROM tblPP_New_CategorySecurity
>> WHERE CategoryId=@CategoryId
>> AND PersonId=@PersonId
>>
>> IF (@CanWrite =1) SET @RetVal=1
>> SELECT @CategoryId=[ParentId]
>> FROM tblPP_New_Categories
>> WHERE [ID]=@CategoryId
>>
>> END
>> SELECT @RetVal as CanWrite
>>
>> GO
>>
>> It sometimes runs forever.
>
> Can you reliably reproduce the situation where it hangs? If so, you should
> be able to use SQL Profiler to see what's going on. If you are not
> familiar with that tool, you should immediately fire it up right now (I'll
> wait), connect to your server and start a trace. Then edit the properties
> of your trace to add the stored procedures events: sp_statementstarting,
> etc. Start the trace and then execute the procedure so you can see what is
> happening.
>
>
>> I have a feeling it's something to do
>> with the second SELECT which should recurse through the table from
>> the given categoryId up to the root category(which has a ParentId of
>> 0)
> SELECT statements do not recurse. WHILE loops do...
>
>> I passed in a CategoryId of 18 whose ParentId is 17 whose ParentId is
>> 0 so it should have only looped twice.
>>
>> The given Personid would have returned no records for the first
>> SELECT. The expected result therefore would be 0.
>>
>
> Given that your tables are correctly indexed, there may be a deadlocking
> issue, which you can resolve by using the WITH (nolock) hint in your FROM
> clause.
> There may also be a "parameter sniffing" situation, which you can relieve
> by modifying your procedure to assign the parameter values to local
> variables which you then use in your SELECTs instead of the parameters.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Query runs forever.

I added all the Stored Procedure Events -- Wow, you get a ton of info.

The problem was that I had my parameters switched in my asp code.

So it couldn't find a category that matched and therefore just kept looping.
I'll have to put a check before my loop to ensure it's a valid category.

Thanks for the help.

Tom B
"Bob Barrows [MVP]" wrote in message
news:epmSoDHvFHA.3472@TK2MSFTNGP10.phx.gbl...
> Tom B wrote:
>> I have the following stored procedure
>>
>> CREATE PROCEDURE spPP_New_GetUserCanWrite
>> @CategoryId int,
>> @PersonId int
>> AS
>> SET NOCOUNT ON
>>
>> Declare @CanWrite bit
>> Declare @RetVal bit
>>
>> Set @RetVal=0
>> WHILE (@CategoryId>0)
>> BEGIN
>> SELECT @CanWrite=CanWrite
>> FROM tblPP_New_CategorySecurity
>> WHERE CategoryId=@CategoryId
>> AND PersonId=@PersonId
>>
>> IF (@CanWrite =1) SET @RetVal=1
>> SELECT @CategoryId=[ParentId]
>> FROM tblPP_New_Categories
>> WHERE [ID]=@CategoryId
>>
>> END
>> SELECT @RetVal as CanWrite
>>
>> GO
>>
>> It sometimes runs forever.
>
> Can you reliably reproduce the situation where it hangs? If so, you should
> be able to use SQL Profiler to see what's going on. If you are not
> familiar with that tool, you should immediately fire it up right now (I'll
> wait), connect to your server and start a trace. Then edit the properties
> of your trace to add the stored procedures events: sp_statementstarting,
> etc. Start the trace and then execute the procedure so you can see what is
> happening.
>
>
>> I have a feeling it's something to do
>> with the second SELECT which should recurse through the table from
>> the given categoryId up to the root category(which has a ParentId of
>> 0)
> SELECT statements do not recurse. WHILE loops do...
>
>> I passed in a CategoryId of 18 whose ParentId is 17 whose ParentId is
>> 0 so it should have only looped twice.
>>
>> The given Personid would have returned no records for the first
>> SELECT. The expected result therefore would be 0.
>>
>
> Given that your tables are correctly indexed, there may be a deadlocking
> issue, which you can resolve by using the WITH (nolock) hint in your FROM
> clause.
> There may also be a "parameter sniffing" situation, which you can relieve
> by modifying your procedure to assign the parameter values to local
> variables which you then use in your SELECTs instead of the parameters.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>