Slow 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)

Slow recursive query

Author

Topic

waelation
Starting Member

13 Posts

Posted - 2009-05-25 : 04:52:39

I have the following stored procedure that is taking time to run..I got to this procedure through the help of the forum members here..It is actually the recursive query that is taking time..It takes around 30 seconds if its getting me more results when it is getting me more data..Please give some solutions..

This is the schema of the FAMILY_MEMBER_REL table :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAMILY_MEMBER_REL](
[FM_REL_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[M_ID] [varchar](100) NOT NULL,
[P_ID] [varchar](100) NOT NULL,
[REL_ID] [varchar](20) NOT NULL,
[REL_TYPE] [int] NULL,
[REL_STATUS] [int] NULL,
[F_ID] [numeric](18, 0) NULL,
[P_F_ID] [numeric](18, 0) NULL,
[REL_DATE] [datetime] NULL,
[CREATED_BY] [nvarchar](50) NULL,
[USER_ID] [nvarchar](50) NULL,
[IP] [varchar](20) NULL,
[SYSTEM_ID] [varchar](20) NULL,
[SITE_ID] [varchar](20) NULL,
[CHECK_SUM] [smallint] NULL,
[CREATION_DATE] [datetime] NULL CONSTRAINT [DF__FAMILY_ME__CREAT__5E74FADA] DEFAULT (getdate()),
[LAST_UPDATED_BY] [nvarchar](50) NULL,
[LAST_UPDATED_DATE] [datetime] NULL,
CONSTRAINT [PK_FAMILY_MEMBER_REL_1] PRIMARY KEY CLUSTERED
(
[M_ID] ASC,
[P_ID] ASC,
[REL_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Following is the Stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[BFS](
@FRM_ID varchar(100),
@TO_ID varchar(100)
)
AS

declare @FROM int,
@TO int

select @from = F_ID from family_member where M_ID = @FRM_ID
select @to = F_ID from family_member where M_ID = @TO_ID

BEGIN
DECLARE @Nodes TABLE (Generation INT, p int, r int, UNIQUE(p, r))
DECLARE @Generation INT

SELECT @Generation = 0

INSERT @Nodes
(
Generation,
p
)
SELECT @Generation,
@FROM

WHILE @@ROWCOUNT > 0 AND NOT EXISTS (SELECT * FROM @Nodes WHERE p = @TO)
BEGIN
SELECT @Generation = @Generation + 1

INSERT @Nodes
(
Generation,
p,
r
)

SELECT @Generation,
P_F_ID,
F_ID
FROM family_member_rel
WHERE F_ID IN (SELECT p FROM @Nodes WHERE Generation = @Generation - 1)
AND P_F_ID NOT IN (SELECT p FROM @Nodes)
UNION
SELECT @Generation,
F_ID,
P_F_ID
FROM family_member_rel
WHERE P_F_ID IN (SELECT p FROM @Nodes WHERE Generation = @Generation - 1)
AND F_ID NOT IN (SELECT p FROM @Nodes)

END

-- Backtracing method: Traces the route back from target to start

DECLARE @Backtrace TABLE
( p INT )

INSERT @Backtrace VALUES(@TO)

WHILE @Generation > 0
BEGIN
DELETE FROM @Nodes
WHERE Generation = @Generation
AND p NOT IN(SELECT p FROM @Backtrace)

INSERT @Backtrace
( p )

SELECT DISTINCT r
FROM @Nodes
WHERE Generation = @Generation

SELECT @Generation = @Generation - 1
END
END

;WITH Generations AS
(SELECT generation AS id,
p AS node,
r AS parent,
1 AS level
FROM @NODES
WHERE generation = 0
UNION ALL
SELECT S.generation,
S.p,
S.r,
G.level + 1
FROM @NODES AS S
JOIN Generations AS G
ON S.r = G.node),
Pivoted AS
(SELECT id,
CASE WHEN level = 1 THEN node END AS gen1,
CASE WHEN level = 2 THEN node END AS gen2,
CASE WHEN level = 3 THEN node END AS gen3,
CASE WHEN level = 4 THEN node END AS gen4,
CASE WHEN level = 5 THEN node END AS gen5,
CASE WHEN level = 6 THEN node END AS gen6,
CASE WHEN level = 7 THEN node END AS gen7,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY node) AS rk
FROM Generations),
Grouped AS
(SELECT MAX(id) AS id,
MAX(gen1) AS gen1,
MAX(gen2) AS gen2,
MAX(gen3) AS gen3,
MAX(gen4) AS gen4,
MAX(gen5) AS gen5,
MAX(gen6) AS gen6,
MAX(gen7) AS gen7
FROM Pivoted
GROUP BY rk)
SELECT @FRM_ID, @TO_ID, id,
MAX(gen1) OVER() AS gen1,
gen2, gen3, gen4, gen5, gen6, gen7
FROM Grouped;

These are the Indexes on the table:

CREATE NONCLUSTERED INDEX [_dta_index_FAMILY_MEMBER_REL_5_498816839__K2] ON [dbo].[FAMILY_MEMBER_REL]
(
[M_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_dta_index_FAMILY_MEMBER_REL_5_498816839__K3_K4] ON [dbo].[FAMILY_MEMBER_REL]
(
[P_ID] ASC,
[REL_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_dta_index_FAMILY_MEMBER_REL_5_498816839__K4] ON [dbo].[FAMILY_MEMBER_REL]
(
[REL_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_dta_index_FAMILY_MEMBER_REL_5_498816839__K4_K2] ON [dbo].[FAMILY_MEMBER_REL]
(
[REL_ID] ASC,
[M_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_dta_index_FAMILY_MEMBER_REL_5_498816839__K4_K3] ON [dbo].[FAMILY_MEMBER_REL]
(
[REL_ID] ASC,
[P_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_dta_index_FAMILY_MEMBER_REL_5_498816839__K4_K3_K2_1] ON [dbo].[FAMILY_MEMBER_REL]
(
[REL_ID] ASC,
[P_ID] ASC,
[M_ID] ASC
)
INCLUDE ( [FM_REL_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

waelation
Starting Member

13 Posts

Posted - 2009-05-26 : 09:54:57

can anyone help me out ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources