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

query on recursive table

Hi,
Suppose I'm working on a database containing 2 tables:
tStudent (student information)
========
|--|
| studentID | studentName | deptID |
|--|
| 504001 | John Doe | 10001 |
| 504005 | JI Jane | 200 |
|--|
tItems (organisational information)
======
|----|
| itemID | KindOfItem | itemName | parentID |
|----|
| 1 | top | top | <NULL> |
| 10 | school | Univ of Geel | 1 |
| 11 | school | Univ of Antwerp | 1 |
| 12 | school | Univ of Brussels | 1 |
| 100 | dept | science dept | 10 |
| 101 | dept | human science dept | 10 |
| 200 | dept | agriculture dept | 11 |
| 1001 | dept | computer science dept | 100 |
| 10001 | dept | Windows comp science dept | 1001 |
| 10002 | dept | Unix comp science dept | 1001 |
|----|
I need to write a query using Reporting Services on a MS SQL server that
gives me for every student the dept he is in (that's the easy part), but
also the top level school the department resides under. Result should be
like:
504001, John Doe , Windows comp science dept, Univ of Geel
504005, JI Jane, Agriculture dept, Univ of Antwerp
Can anybody give me any clues on how to do this (if possible)?
ErikWhy not add the parent_id for say Univ of Geel is 1 to a new column in the
first table and use an update to achieve this. Then you all you need is a
simple equi-join.
"dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:

> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
>|||You didn't specify if the dept of the recursion is variable. Assuming
it is not then:
select x.studentID, x.studentName, y.DeptName, y.School
from tStudent x,
(
select a.deptID, a.itemName as DeptName, b.itemName as School
from tItems a, tItems b
where a.parentID = b.deptID
) y
where x.deptID = y.deptID
Ranny

> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
User submitted from AEWNET (http://www.aewnet.com/)|||This smells more like homework instead of real world scenario, but I'll play
along! how about this:
set nocount on
declare @.organization table (itemid int, kindofitem varchar(20), itemname
varchar(30), parentid int, topparentid int)
declare @.studentinfo table (studentid int, studentname varchar(30), deptid
int)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10, 'school', 'Univ of Geel', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(11, 'school', 'Univ of Antwerp', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(12, 'school', 'Univ of Brussels', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(100, 'dept', 'science dept', 10)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(101, 'dept', 'human science dept', 10)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(200, 'dept', 'agriculture dept', 11)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(1001, 'dept', 'computer science dept', 100)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10001, 'dept', 'Windows comp science dept', 1001)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10002, 'dept', 'Unix comp science dept', 1001)
insert into @.studentinfo (studentid, studentname, deptid) values(504001,
'John Doe', 10001)
insert into @.studentinfo (studentid, studentname, deptid) values(504005, 'JI
Doe', 200)
update @.organization set topparentid = itemid where parentid = 1
while 1 = 1
begin
update @.organization
set topparentid = k.topparentid
from @.organization o
join (select itemid, topparentid from @.organization where topparentid is
not null) k
on o.parentid = k.itemid
where o.topparentid is null
if @.@.rowcount = 0
break
end
select * from @.organization
"dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
>|||@.aew_nospam.com schreef:
> You didn't specify if the dept of the recursion is variable. Assuming
> it is not then:
It IS variable, as you can see from the example:
Student JI Jane resides under dept "agriculture dept", that resides
directly under school "Univ of Antwerp".
Student John Doe resides under dept "Windows comp sc dept", that resides
under dept "computer science dept" that resides under dept "science
dept" that resides under school "Univ of Geel".

> select x.studentID, x.studentName, y.DeptName, y.School
> from tStudent x,
> (
> select a.deptID, a.itemName as DeptName, b.itemName as School
> from tItems a, tItems b
> where a.parentID = b.deptID
> ) y
> where x.deptID = y.deptID
> Ranny
>
>
>
> User submitted from AEWNET (http://www.aewnet.com/)|||
> This smells more like homework instead of real world scenario, but I'll pl
ay
> along! how about this:
Please have your nose checked, because this is no homework but
real-world scenario :) It is part of a student administration system
we're implementing in a group of schools.
My job is to create report(s) from the database. The report mentionned
in OP should have the data on the student, the dept he works in and the
school this dept belongs to. Hope it's clear now :)
> set nocount on
> declare @.organization table (itemid int, kindofitem varchar(20), itemname
> varchar(30), parentid int, topparentid int)
> declare @.studentinfo table (studentid int, studentname varchar(30), deptid
> int)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10, 'school', 'Univ of Geel', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(11, 'school', 'Univ of Antwerp', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(12, 'school', 'Univ of Brussels', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(100, 'dept', 'science dept', 10)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(101, 'dept', 'human science dept', 10)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(200, 'dept', 'agriculture dept', 11)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(1001, 'dept', 'computer science dept', 100)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10001, 'dept', 'Windows comp science dept', 1001)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10002, 'dept', 'Unix comp science dept', 1001)
> insert into @.studentinfo (studentid, studentname, deptid) values(504001,
> 'John Doe', 10001)
> insert into @.studentinfo (studentid, studentname, deptid) values(504005, '
JI
> Doe', 200)
> update @.organization set topparentid = itemid where parentid = 1
> while 1 = 1
> begin
> update @.organization
> set topparentid = k.topparentid
> from @.organization o
> join (select itemid, topparentid from @.organization where topparentid i
s
> not null) k
> on o.parentid = k.itemid
> where o.topparentid is null
> if @.@.rowcount = 0
> break
> end
> select * from @.organization
>
> "dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
>|||marcmc schreef:
> Why not add the parent_id for say Univ of Geel is 1 to a new column in the
> first table and use an update to achieve this. Then you all you need is a
> simple equi-join.
Yes, that is true. But the information I need is available already in
the database, so adding the new column in the first table would make the
information redundant...
>
> "dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Have you thought about designing a normalized schema which does not mix
data and metadata? This is a better approach in the long run than
looking for kludges. Just from this posting and the silly 'T-"
prefixes that you are an OO programmer no training in SQL. OO models do
not work with SQL.