Help me kill this cursor - 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 2008 Forums

Transact-SQL (2008)

Help me kill this cursor

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-12-12 : 12:18:39

It runs fast for every client except one and it seems to be running forever for that client. I suspect a locking issue but it makes me think that the cursor needs to go. Possible?


-- =============================================
-- Create date: March 2 - 2011
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[PopulateDepartmentChildren]
	@ClientID INT
AS
BEGIN
	SET NOCOUNT ON;
    

	BEGIN TRANSACTION
    
    DELETE FROM DepartmentChildren WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE ClientID = @ClientID)
    
    IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END
	
    DECLARE @DepartmentID INT
    DECLARE @ParentID INT
	DECLARE @DepartmentCursor CURSOR
	
	SET @DepartmentCursor = CURSOR FAST_FORWARD FOR
		SELECT DepartmentID FROM Departments WHERE ClientID = @ClientID

	OPEN @DepartmentCursor
		FETCH NEXT FROM @DepartmentCursor
		INTO @DepartmentID
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @ParentID = (SELECT ParentDeptID FROM Departments d WHERE d.DepartmentID = @DepartmentID AND EXISTS (SELECT NULL FROM Departments d2 WHERE d2.DepartmentID = d.ParentDeptID))
		
		WHILE @ParentID > 0 AND @ParentID IS NOT NULL
		BEGIN
			INSERT INTO DepartmentChildren (DepartmentID, ChildDepartmentID)
				SELECT @ParentID, @DepartmentID
				WHERE NOT EXISTS (SELECT NULL FROM DepartmentChildren WHERE DepartmentID = @ParentID AND ChildDepartmentID = @DepartmentID)
			SET @ParentID = (SELECT ParentDeptID FROM Departments d WHERE d.DepartmentID = @ParentID AND EXISTS (SELECT NULL FROM Departments d2 WHERE d2.DepartmentID = d.ParentDeptID))
		END
    
		FETCH NEXT FROM @DepartmentCursor INTO @DepartmentID
	END
    
    IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END
    COMMIT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 00:51:53

something like below


CREATE PROCEDURE [dbo].[PopulateDepartmentChildren]
	@ClientID INT
AS
BEGIN
	SET NOCOUNT ON;
    

	BEGIN TRANSACTION
        
        DELETE dc FROM DepartmentChildren dc 
        INNER JOIN Departments d
        ON d.DepartmentID  =dc.DepartmentID 
        WHERE d.ClientID = @ClientID        

        ;With Department_Hierarchy 
         AS
         (
           SELECT d.ParentDeptID,d.DepartmentID 
           FROM Departments d
           LEFT JOIN Departments d2 
           ON d2.DepartmentID = d.ParentDeptID
           WHERE d2.DepartmentID IS NULL
           UNION ALL
           SELECT
           FROM Department_Hierarchy dh
           INNER JOIN Departments d
           ON d.DepartmentID = dh.ParentDeptID
         )
         INSERT INTO DepartmentChildren (DepartmentID, ChildDepartmentID)
         SELECT DISTINCT ParentDeptID,DepartmentID 
         FROM Department_Hierarchy 
IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END
    COMMIT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ravindra.tsg
Starting Member

2 Posts

Posted - 2011-12-13 : 08:40:02

CREATE PROCEDURE [dbo].[PopulateDepartmentChildren]
@ClientID INT
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRANSACTION

--DELETE FROM DepartmentChildren
--WHERE DepartmentID IN
-- ( SELECT DepartmentID
-- FROM Departments
-- WHERE ClientID = @ClientID
-- )

DELETE deptchild
FROM DepartmentChildren deptchild
INNER JOIN
Departments dept
ON deptchild.DepartmentID=dept.DepartmentID
WHERE dept.ClientID = @ClientID

-- IF @@ERROR <> 0
--BEGIN
-- ROLLBACK
-- RETURN
--END

DECLARE @DepartmentID INT
DECLARE @ParentID INT
DECLARE @DepartmentCursor CURSOR

SET @DepartmentCursor = CURSOR FAST_FORWARD FOR
SELECT DepartmentID FROM Departments WHERE ClientID = @ClientID

OPEN @DepartmentCursor
FETCH NEXT FROM @DepartmentCursor
INTO @DepartmentID

WHILE @@FETCH_STATUS = 0
BEGIN
--SET @ParentID = (

-- SELECT ParentDeptID
-- FROM Departments d
-- WHERE d.DepartmentID = @DepartmentID
-- AND EXISTS (
-- SELECT NULL
-- FROM Departments d2
-- WHERE d2.DepartmentID = d.ParentDeptID))
SET @ParentID = (

SELECT ParentDeptID
FROM Departments d INNER JOIN
Departments d2
ON d2.DepartmentID = d.ParentDeptID AND d.DepartmentID = @DepartmentID

)

WHILE @ParentID > 0 AND @ParentID IS NOT NULL
--BEGIN
-- INSERT INTO DepartmentChildren (DepartmentID, ChildDepartmentID)
-- SELECT @ParentID, @DepartmentID
-- WHERE NOT EXISTS (SELECT NULL FROM DepartmentChildren WHERE DepartmentID = @ParentID AND ChildDepartmentID = @DepartmentID)
-- SET @ParentID = (SELECT ParentDeptID FROM Departments d WHERE d.DepartmentID = @ParentID AND EXISTS (SELECT NULL FROM Departments d2 WHERE d2.DepartmentID = d.ParentDeptID))
--END

BEGIN
IF NOT EXISTS (
SELECT NULL
FROM DepartmentChildren
WHERE DepartmentID = @ParentID AND ChildDepartmentID = @DepartmentID
)
INSERT INTO DepartmentChildren (DepartmentID, ChildDepartmentID)
SELECT @ParentID, @DepartmentID

--SET @ParentID = (SELECT ParentDeptID FROM Departments d WHERE d.DepartmentID = @ParentID AND EXISTS (SELECT NULL FROM Departments d2 WHERE d2.DepartmentID = d.ParentDeptID))
SET @ParentID = (

SELECT ParentDeptID
FROM Departments d INNER JOIN
Departments d2
ON d2.DepartmentID = d.ParentDeptID AND d.DepartmentID = @DepartmentID

)

END

FETCH NEXT FROM @DepartmentCursor INTO @DepartmentID
END

-- IF @@ERROR <> 0
--BEGIN
-- ROLLBACK
-- RETURN
--END
COMMIT
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER(),ERROR_LINE(),ERROR_MESSAGE()
ROLLBACK
END CATCH

END

Sorry i am not able to reach what exactly you are expecting. but I have done some kind of optimization(i.e proper error handling and ANSI JOIN replace with sub query) for this query.
i will see later what you are expecting.

yes this is really good thing to work on this.

~Ravi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 08:44:01

I think OP was looking at way to avoid cursor!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-12-13 : 16:21:46

quote:

Originally posted by visakh16

something like below


CREATE PROCEDURE [dbo].[PopulateDepartmentChildren]
	@ClientID INT
AS
BEGIN
	SET NOCOUNT ON;
    

	BEGIN TRANSACTION
        
        DELETE dc FROM DepartmentChildren dc 
        INNER JOIN Departments d
        ON d.DepartmentID  =dc.DepartmentID 
        WHERE d.ClientID = @ClientID        

        ;With Department_Hierarchy 
         AS
         (
           SELECT d.ParentDeptID,d.DepartmentID 
           FROM Departments d
           LEFT JOIN Departments d2 
           ON d2.DepartmentID = d.ParentDeptID
           WHERE d2.DepartmentID IS NULL
           UNION ALL
           SELECT
           FROM Department_Hierarchy dh
           INNER JOIN Departments d
           ON d.DepartmentID = dh.ParentDeptID
         )
         INSERT INTO DepartmentChildren (DepartmentID, ChildDepartmentID)
         SELECT DISTINCT ParentDeptID,DepartmentID 
         FROM Department_Hierarchy 
IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END
    COMMIT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This is brilliant!

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-12-13 : 16:51:32

Ok - almost brilliant.

I'm getting this error...

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

I tried removing the UNION portion and then it works but unfortunately it only brings back one child per department and many departments have multiple children.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-12-13 : 16:58:01

I tried upping the maxrecursion value to 20000 and it still didn't work :(

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-13 : 18:18:50

Do you have circular references in your "hierarchy?"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:22:08

quote:
Originally posted by ferrethouse

Ok - almost brilliant.

I'm getting this error...

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

I tried removing the UNION portion and then it works but unfortunately it only brings back one child per department and many departments have multiple children.

just make it like


CREATE PROCEDURE [dbo].[PopulateDepartmentChildren]
	@ClientID INT
AS
BEGIN
	SET NOCOUNT ON;
    

	BEGIN TRANSACTION
        
        DELETE dc FROM DepartmentChildren dc 
        INNER JOIN Departments d
        ON d.DepartmentID  =dc.DepartmentID 
        WHERE d.ClientID = @ClientID        

        ;With Department_Hierarchy 
         AS
         (
           SELECT d.ParentDeptID,d.DepartmentID 
           FROM Departments d
           LEFT JOIN Departments d2 
           ON d2.DepartmentID = d.ParentDeptID
           WHERE d2.DepartmentID IS NULL
           UNION ALL
           SELECT
           FROM Department_Hierarchy dh
           INNER JOIN Departments d
           ON d.DepartmentID = dh.ParentDeptID
         )
         INSERT INTO DepartmentChildren (DepartmentID, ChildDepartmentID)
         SELECT DISTINCT ParentDeptID,DepartmentID 
         FROM Department_Hierarchy 
         OPTION (MAXRECURSION 0)
IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END
    COMMIT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources