Using WITH to work out parent record paths

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using WITH to work out parent record paths

Author

Topic

john-seedata.co.uk
Starting Member

2 Posts

Posted - 2011-05-26 : 06:19:36

Hi,

I'm trying to create an efficient way of updating a couple of fields on a parent/child style record with strings representing the path to that record. I'm using it for things like chapter references (1.2,1.4.5 etc) and also a way of storing a list of record id's in the parent path.

My query is below, any suggestions would be great....

WITH parents(
[rid],
[parentPageRID],
[parentPagePathRIDList1],
[parentPagePathIndex1],
[pathIndex1],
[pageNumber1]
) AS (
SELECT
[rid],
[parentPageRID],
CONVERT(varchar(255),''),
0 AS [parentPagePathIndex1],
CONVERT(varchar(255),[position]),
0 AS [pageNumber1]
FROM
[page] AS [p]
WHERE
[parentPageRID] IS NULL
UNION ALL
SELECT
[p].[rid],
[p].[parentPageRID],
CONVERT(varchar(255),[parentPagePathRIDList1] + ',' + CAST([p].[parentPageRID] AS varchar)),
[parentPagePathIndex1] + 1,
CONVERT(varchar(255),[pathIndex1] + '.' + CAST([p].[position] AS varchar)),
[pageNumber1] + 1
FROM
[page] AS [p]
INNER JOIN [parents] AS [t] ON [p].[parentPageRID] = [t].[rid]
WHERE [p].[isDeleted] <> 1
)
SELECT [rid], [parentPageRID], [parentPagePathRIDList1], [parentPagePathIndex1], [pathIndex1], [pageNumber1]
FROM [parents]
WHERE [rid] = 100001;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-26 : 07:16:57

The query, from what I can tell, seems fine. Are you running into any issues/performance problems? If you are, can you post table structures, some sample data and expected output?

john-seedata.co.uk
Starting Member

2 Posts

Posted - 2011-05-26 : 08:57:30

Thanks for looking it over. The problem is performance, although I dont think its poor, its just not instant. As you say it does everything its supposed to, but I'm having to run it every time a 'page' is updated and sometimes those updates overlap and this query is getting run by a few processes at once which is obviously sending the whole thing skyward.

I guess I just needed some validation that the query was fine. I think my best option is just ensuring executions of this are queued so they dont overlap.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources