Discussion :

[zoltix]

J'ai écris une requête recursive qui assez simple mais lorsque je commence a avoir trop individu. Les performances chute.

Peutêtre avez vous des idées?


alter proc [dbo].[RecursiveContacts] @node varchar(50) =null, @limitLevel int = 0,@level int = 0
as
Declare @child varchar(50)
declare @ATemp varchar(50)
--SET ANSI_NULLS OFF
--SET NOCOUNT ON
set @child =(select count(*) from dbo.CAM_Contacts where id_contact_parent = @node )
print @child
if @child>0
Begin
print 'Child Exixts'
set @level = @level + 1
while(((select count(*) from dbo.CAM_Contacts where id_contact_parent =@node and id_contact not in (select id_contact from #tmp___Comtacts))>0)
and ( @level <= @limitLevel or @limitLevel =0) )
Begin
select top 1 @ATemp=id_contact from dbo.CAM_Contacts where id_contact_parent =@node and id_contact not in (select id_contact from #tmp___Comtacts)
--print 'level '+ cast( @level as varchar(20))
--print 'limit '+ cast( @limitLevel as varchar(20))
--print 'dbo.[RecursiveContacts]' + cast( @ATemp as varchar) + ' '+ cast(@level as varchar)
exec dbo.[RecursiveContacts] @ATemp, @limitLevel,@level
End
insert into #tmp___Comtacts (id_contact) select id_contact from dbo.CAM_Contacts where id_contact = @node
End
Else
Begin
insert into #tmp___Comtacts (id_contact) select id_contact from dbo.CAM_Contacts where id_contact = @node
End








create proc sp_retrieve_Contact_Child @id_Parents int
as

IF EXISTS (select name from tempdb..sysobjects where name like '#tmp___Comtacts%')
begin
--print 'drop temp'
DROP Table #tmp___Comtacts
end
Begin
Create table #tmp___Comtacts (id_contact int)
create
end
exec dbo.[RecursiveContacts] @id_Parents
select * from #tmp___Comtacts tmp inner join dbo.CAM_Contacts con on con.id_contact =tmp.id_contact




CREATE TABLE [dbo].[CAM_Contacts](
[id_contact] [int] IDENTITY(1,1) NOT NULL,
[id_contact_parent] [int] NOT NULL CONSTRAINT [DF_CAM_Contacts_id_contact_parent] DEFAULT ((-1)),
CONSTRAINT [PK_CAM_Contacts] PRIMARY KEY CLUSTERED
(
[id_contact] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



Pour lancer la procèdure

sp_retrieve_Contact_Child 778
sp_retrieve_Contact_Child 17832

[zoltix]

avec cette transformation je gagne +- 20 seconde sur requête 30 secondes


alter proc [dbo].[RecursiveContacts] @node varchar(50) =null, @limitLevel int = 0,@level int = 0
as
declare @ATemp varchar(50)
declare @bExit bit
--SET ANSI_NULLS OFF
SET NOCOUNT ON
set @bExit = 0;
--print @child
if (select count(*) from dbo.CAM_Contacts where id_contact_parent = @node )>0
Begin
--print 'Child Exixts'
set @level = @level + 1
while(( @level <= @limitLevel or @limitLevel =0) )
Begin
set @ATemp = null
select top 1 @ATemp=id_contact
from dbo.CAM_Contacts con
where id_contact_parent =@node
and NOT EXISTS (select * from #tmp___Comtacts tmp where con.id_contact = tmp.id_contact)
print @ATemp
if (@ATemp is null) begin set @bExit=1; break; end;
exec dbo.[RecursiveContacts] @ATemp, @limitLevel,@level
End
insert into #tmp___Comtacts (id_contact) select id_contact from dbo.CAM_Contacts where id_contact = @node
End
Else
Begin
insert into #tmp___Comtacts (id_contact) select id_contact from dbo.CAM_Contacts where id_contact = @node
End