Query performance

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
GOIf you're using SQL Server 2005, you should consider using Common Table
Expressions (CTE) instead. Temporary tables suffer from writing data to
the tempdb database, whereas CTEs are in memory only.
A good walktrough is available at the MSDN site:
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
Hope this helps
"Mike" <none@.none.com> wrote in
news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:

> 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
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||I am using SQL Server 2000 SP4.
Is there anything that I could improve in my query? I checked all indexes,
even in the temp table, but I ran out of ideas.
Thanks.
Mike
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns98075D16535A0olekristianbangaas@.
207.46.248.16...
> If you're using SQL Server 2005, you should consider using Common Table
> Expressions (CTE) instead. Temporary tables suffer from writing data to
> the tempdb database, whereas CTEs are in memory only.
> A good walktrough is available at the MSDN site:
> http://msdn2.microsoft.com/en-us/library/ms186243.aspx
> Hope this helps
>
> "Mike" <none@.none.com> wrote in
> news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
>
> *
> *
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP