Noob need help with recursion

I created a table called MyTable with the following fields :

Id int
Name char(200)
Parent int

I also created the following stored procedure named Test, which is supposed to list all records and sub-records.

CREATE PROCEDURE Test
@.Id int
AS

DECLARE @.Name char(200)
DECLARE @.Parent int

DECLARE curLevel CURSOR LOCAL FOR
SELECT * FROM MyTable WHERE Parent = @.Id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Id AS Id, @.Name AS Name, @.Parent AS Parent
EXEC Test @.Id
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
END

CLOSE curLevel
DEALLOCATE curLevel
GO

I added a MxDataGrid and DataSourceControl.
SelectCommand property of the DataSourceControl = EXEC Test 0

When I run the aspx page, it only shows 1 record. I tried to change the parameter to 1, 2, 3 but it always shows only 1 record (not the same tho).

Is there something wrong with the stored procedure ?Ok, I made some modifications to make it work properly but there is some limitations. I have to store the results in a temp table. Can I do something similar but without the temp table ?

(Note : I changed some field/table names)

-- ----------------------
-- Fill and select the temp table where the nodes and sub nodes id are stored
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodes
@.NodeId INT
AS
DELETE FROM TmpNodesAndSubNodes
EXEC GetNodesAndSubNodesRecursive @.NodeId
SELECT * FROM TmpNodesAndSubNodes
GO
-- ----------------------
-- Fill the temp table with the id's of the nodes and it's sub nodes
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodesRecursive
@.NodeId INT
AS
-- Store the node id into the temp table
INSERT INTO TmpNodesAndSubNodes VALUES(@.NodeId)
-- Declare a local cursor to seek in the nodes table
-- and some variables to retreive the values
DECLARE @.CurrentNodeId INT
DECLARE @.CurrentParentNodeId INT
DECLARE curLevel CURSOR LOCAL FOR
 SELECT Id FROM Nodes WHERE ParentNodeId=@.NodeId
-- Fetchs the records and call the recursive method for each node id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.CurrentNodeId
WHILE @.@.FETCH_STATUS = 0
BEGIN
 EXEC GetNodesAndSubNodesRecursive @.CurrentNodeId
 FETCH NEXT FROM curLevel INTO @.CurrentNodeId
END
-- Clean up
CLOSE curLevel
DEALLOCATE curLevel
GO

|||Aaarg :)

I decided to work on a non recursive solution.

NOLOCK and READPAST on same table?

Is it possible to use With (NOLOCK) and With (READPAST) in the same
SELECT query and what whould be the syntax?

@.param int

SELECT
myRow
FROM
dbo.myTable WITH (NOLOCK)
WHERE
myRow = @.param

Thanks,
lqlaurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Is it possible to use With (NOLOCK) and With (READPAST) in the same
> SELECT query and what whould be the syntax?

The syntax would be

SELECT ... FROM tbl WITH (NOLOCK, READPAST)

But I got the error message:

Server: Msg 650, Level 16, State 1, Line 1
You can only specify the READPAST lock in the READ COMMITTED or
REPEATABLE READ isolation levels.

Which makes sense. READPAST means that you skip rows that you would be
blocked on, and you will not be blocked with NOLOCK.

What are you trying to achieve?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 17 Sep 2005 14:04:40 -0700, laurenq uantrell wrote:

>Is it possible to use With (NOLOCK) and With (READPAST) in the same
>SELECT query and what whould be the syntax?

Hi Lauren,

The NOLOCK hint specifies that all locks should be disregarded. The
READPAST hint specifies that locked rows should be skipped. This means
that these lock hints are mutually exclusive.

The syntax for combining hints is
WITH (NOLOCK, READPAST)
which will result in an error for this combination. The use of several
hints might be useful for other combinations, though:
WITH (TABLOCK, XLOCK)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have a table dbo.myTableName to which hundreds of users are
UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
against that table 24/7.
I am tring to remove the slowdown caused by rows that might be in use
when users run a query that looks like: SELECT myID, myRow1 etc. FROM
dbo.myTableName WITH (READPAST) WHERE StartDate = @.DateParam|||So which should result in a faster scan of the table with fewer
possibilty of locking in a situation where:

I have a table dbo.myTableName to which hundreds of users are
UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
against that table 24/7.
I am tring to remove the slowdown caused by rows that might be being
written to when users run a query that looks like: SELECT myID, myRow1
FROM dbo.myTableName WITH (READPAST) <OR> WITH (NOLOCK) WHERE StartDate
= @.DateParam

?|||On 18 Sep 2005 08:28:14 -0700, laurenq uantrell wrote:

>So which should result in a faster scan of the table with fewer
>possibilty of locking in a situation where:
>I have a table dbo.myTableName to which hundreds of users are
>UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
>against that table 24/7.
>I am tring to remove the slowdown caused by rows that might be being
>written to when users run a query that looks like: SELECT myID, myRow1
>FROM dbo.myTableName WITH (READPAST) <OR> WITH (NOLOCK) WHERE StartDate
>= @.DateParam
>?

Hi Lauren,

Here's an answer you probably don't want to hear :-)

Try to use neither. In both cases, you'll return information that is
besides the truth. In the case of (READPAST), rows will be missing in
your result set that should be included. In the case of (NOLOCK), you'll
return data that is currently being changed, but might still be rolled
back (e.g. because it violates a business rule).

You should not be trying to get "a faster scan of the table" - you
should be trying to eliminate table scans at all. Especially on a table
that is under heavy use by hundreds of users. Making sure that all
inserts, updates and selects can use appropriate indexes will go a long
way toward preventing table scans. This will also mean that you'll spend
far less time waiting for a lock to be released on a row you didn;t want
to see after all!!

If you're still facing blocking issues after this, you might want to
consider duplicating the table: one "live" table for all the inserts and
updates, and a "reporting" copy for all the selects. Set up a routine
that will periodically (e.g. every 5 minutes, or whatever time delay is
acceptable in your situation) copy over all changes from the "live"
table to the "reporting" copy.

Now to your original question:
>So which should result in a faster scan of the table with fewer
>possibilty of locking in a situation where:
(snip)
>WITH (READPAST) <OR> WITH (NOLOCK)

The only way to find out is to test them both. If I were forced to
guess, I'd say that NOLCOK might be faster as it doesn't check for
existing locks, nor take any locks, whereas NOLOCK still checks for
locks and takes a lock if the row is not currently locked.

But as I said - that's just a guess.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I have a table dbo.myTableName to which hundreds of users are
> UPDATE-ing or INSERT-ing 24/7. They are also running SELECT queries
> against that table 24/7.
> I am tring to remove the slowdown caused by rows that might be in use
> when users run a query that looks like: SELECT myID, myRow1 etc. FROM
> dbo.myTableName WITH (READPAST) WHERE StartDate = @.DateParam

I echo what Hugo said: try to avoid NOLOCK and READPAST as long as you
can. Rather investigate if indexes can help. A query like the one
above, might excute faster with an index on StartDate.

As for whether you should use NOLOCK or READPAST, there are two things
consider: a) what result do you want and b) and what is your blocking
problem?

a) Both NOLOCK and READPAST can result in the queries giving incorrect
result.
NOLOCK means that you read uncommitted data, which could violate business
rules, and that will be rolled back the next second (or is in fact in the
process of being rolled back). In more devilish cases an updating process
may first delete some data to re-inserted it in some new version, leading
to that you get no data at all.

This last thing is also very typical for READPAST. "SELECT SUM(amt) FROM tbl
WITH (READPAST) WHERE date = @.somdate". Oops, a bunch of rows were locked,
and you get back a value which os 40% of the right one.

If the queries that run are reports that is mainly interested in general
trends, and not used for reconcilliation etc, then it may be OK to run
with NOLOCK, but you should really investigate the consequences.

b) READPAST will not help if SELECTs that performs table scans block
UPDATE statements, the SELECT gets a lock on table level, and the updaters
will have to wait. READPAST makes sense if selects are fast, but your
UPDATE/INSERT operations are complex and long-running.

Generally, first try to see if better indexing can help. But if you have
queries that comes from search functions where the user can select
conditions wildly can be difficult to have an index for everything.
Investing in a second server for reports, may be worth the effort.

In SQL 2005 there is a new isolation level, SNAPSHOT. With this isolation
level, SELECT statements can run on a snapshot of the state of the database
in a given moment. This can help a lot to prevnent this sort of problems.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp