Query performance

microsoft.public.sqlserver.programming

21 Jul 2006 12:15 AM

Mike

Hello,

I wrote the query below, but I was wondering whether there was a better way of doing so. I am basically trying to build a recursive process.

Any suggestion would be greatly appreciated.

Thanks.
Mike

*********************************************************************************************
*********************************************************************************************

CREATE PROCEDURE dbo.CustomersFiltered
(
@filter   varchar(50)
)
AS

create table #t
(
   dummyId        bigint IDENTITY(1,1) PRIMARY KEY,
   customerId     bigint,
   parentId       bigint,
   levelId        int
)

INSERT #t (customerId, parentId, levelId)
SELECT customerAutoId, parentCustomerAutoId, 0
FROM customers
WHERE [name] like @filter

declare @dummyId   bigint
select @dummyId = 1

declare @customerId    bigint
declare @parentId      bigint
declare @levelId       bigint

WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @dummyId) > 0
BEGIN

   select @customerId = customerId,
          @parentId    = parentId,
          @levelId     = levelId
   FROM #t
   WHERE dummyId = @dummyId

   insert into #t (customerId, parentId, levelId)
   select CustomerAutoId,
          parentCustomerAutoId,
          @levelId - 1
   from customers
   where parentCustomerAutoId = @customerId
   group by CustomerAutoId,
            parentCustomerAutoId

   if (@parentId is not null)
     insert into #t (customerId, parentId, levelId)
     select c.CustomerAutoId,
            c.parentCustomerAutoId,
            @levelId + 1
     from customers c
     where c.customerAutoId = @parentId
       and not exists (select * from #t where c.customerAutoId = customerId)
     group by CustomerAutoId,
              parentCustomerAutoId

   select @dummyId = @dummyId + 1

END

select t.levelId                as LevelId,
       t.customerId             as CustomerAutoId,
       c.[name]                 as [Name],
       c.sic                    as SIC,
       c.ParentCustomerAutoId   as ParentCustomerAutoId
from #t t
inner join customers c on c.customerAutoId = t.customerId
group by t.levelId,
         t.customerId,
         c.[name],
         c.sic,
         c.ParentCustomerAutoId
order by levelId desc,
         c.ParentCustomerAutoId,
         c.[name]

drop table #t
GO