Speed up recursive query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Speed up recursive query

Author

Topic

staplebottom
Starting Member

29 Posts

Posted - 2007-05-22 : 13:25:15

Hi,

I have a set of queries which are working and now Id like to speed them up.

I have two table, one for categories, and one for holding relationships.

So table lut_category would have :

categoryID PK int
categoryName Varchar(500)

and lut_category_parents has

categoryID int
parentID int

allowing categories to have multiple parent and child relationships.

I want to write sql to loop through the tables and retrieve ordered results starting with the first base category, its first child etc :

1
--1.1
----1.1.1
--1.2
----1.2.1
----1.3.1
--1.3
2
--2.1
----2.1.1
----2.1.2
----2.1.3

and so on.

Currently im using a stored procedure to create a table and read all the categories with no parents. I use a cursor to move through these and call a recursive stored procedure which insert the sub categories. It looks like this :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[usp_SC_FLEX]
(
  @CultureID as INT,
  @parentid as INT
)

AS

	DECLARE @cID int, @pID int, @cName nvarchar(250)
	DECLARE @RowNum int

	DECLARE cursName CURSOR LOCAL FOR
          *SELECT STATEMENT HERE*

	OPEN cursName
	FETCH NEXT FROM cursName
	INTO @cID, @pID, @cName
	SET @RowNum = 0 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO lut_category_ordered (CategoryID, ParentID, CategoryName)
		VALUES (@cID, @pID, @cName)

			EXEC usp_SC_FLEX @Cultureid = @CultureID, @parentID = @cID

		FETCH NEXT FROM cursName
		INTO @cID, @pID, @cName
	END 
	CLOSE cursName
	DEALLOCATE cursName

It works, but takes about 15 seconds to fill the table, for about 1600 records. If this is normal I can just update the table every 10 minutes and leave it at that. But should this take so long, and is am I going about this the wrong way?

Thanks.
Conor

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-22 : 13:44:40

(1) Change the cursor type to fast_forward,readonly
(2) What's going on in the call to the usp_SC_FLEX proc?

staplebottom
Starting Member

29 Posts

Posted - 2007-05-22 : 14:05:53

the select statement joins a few tables to get localised details :

Select lut_category.CategoryId, lut_category_parents.ParentId, lut_category_locale.CategoryName
		from   lut_category,lut_category_parents,lut_category_locale
		where  (lut_category.CategoryId = lut_category_locale.CategoryID) AND
			   (lut_category.CategoryId = lut_category_parents.CategoryID) AND
			   (lut_category_parents.ParentId = @parentid) AND (lut_category.enabled = 1) AND
			   (lut_Category_locale.CultureID = @cultureID) AND (lut_category_locale.active = 1)

Would I be better using a stored procedure for that part?

When i change the line creating the cursor to :

DECLARE cursName CURSOR FAST_FORWARD LOCAL FOR

the whole thing take 5 times as long now, since FAST_FORWARD is in. this doesnt make sense?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-22 : 14:19:32

If you are using SQL 2005, there is no reason to use a cursor at all. In fact, even with SQL 2000 there is no reason to use a cursor for this.

see: http://msdn2.microsoft.com/en-us/library/ms186243.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

staplebottom
Starting Member

29 Posts

Posted - 2007-05-22 : 15:42:36

Thanks for that, it looks like it should help me but I cant seem to get it right.

At the moment i have the following :


USE [sample];
GO
WITH ordered_categories (CategoryID, ParentID, CategoryName, Level)
AS
(
-- Anchor member definition
    Select C.CategoryId, P.ParentId, L.CategoryName, 0 AS LEVEL
		from   lut_category AS C
		inner join lut_category_parents AS P ON 
		C.CategoryId = P.CategoryID
		inner join lut_category_locale AS L ON
		C.CategoryId = L.CategoryID
		WHERE P.Parentid = 0

    UNION ALL
-- Recursive member definition
    Select C.CategoryId, P.ParentId, L.CategoryName, Level+1
		from   lut_category AS C
		inner join lut_category_parents AS P ON 
		C.CategoryId = P.CategoryID
		inner join lut_category_locale AS L ON
		C.CategoryId = L.CategoryID
		inner join ordered_categories as O on
		(P.ParentId = O.CategoryID)
		where   (C.enabled = 1) AND
			    (L.CultureID = 1) AND 
				(L.active = 1) 


)
-- Statement that executes the CTE
SELECT CategoryID, parentID, CategoryName, Level
FROM ordered_categories 
GO

this is return results backwards. In the form :

1
2
3
4
5
6
--6.1
--6.2
--6.3
----6.3.1
----6.3.2
----6.2.1

working backwards all the way to the subcategories of category 1. Can anyone see how I can alter the output to suit what i need.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-22 : 15:44:43

What's going on inside the stored proc that gets called through each loop within the cursor?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-22 : 15:51:30

Here's an even more detailed link regarding CTE's and hierarchies:

http://msdn2.microsoft.com/en-us/library/ms345144.aspx

scroll way down the page and you'll see at least two examples where the output is sorted the way you are looking for. (I think!)

- Jeff
http://weblogs.sqlteam.com/JeffS

staplebottom
Starting Member

29 Posts

Posted - 2007-05-22 : 15:53:03

quote:
Originally posted by rudesyle

What's going on inside the stored proc that gets called through each loop within the cursor?

pretty much this is the select thats being called in each call of the recursive procedure :

Select C.CategoryId, P.ParentId, L.CategoryName, Level+1
		from   lut_category AS C
		inner join lut_category_parents AS P ON 
		C.CategoryId = P.CategoryID
		inner join lut_category_locale AS L ON
		C.CategoryId = L.CategoryID
		inner join ordered_categories as O on
		(P.ParentId = O.CategoryID)
		where   (C.enabled = 1) AND
			    (L.CultureID = 1) AND 
				(L.active = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources