High performance system and table and index structure

Hi Folks,
I'm working on something a little different at present. Given it's a bit out
of the ordinary, and it relates to some of our indexing discussions lately,
I'd appreciate any thoughts/feedback.
It's a SQL Server box that's receiving a stream of real-time data from oil
drilling rigs all over the world. It needs to perform both fast inserts (ie
10,000 per second) and also have good performance on a fixed list of query
types. The essential part of the table data that has me most intrigued looks
like this:
Well (ID of the well involved - varchar(60))
Wellbore (ID of a specific bore at a wellsite - varchar(60))
Curve (ID of a specific set of measures eg: pressures, gas levels, etc.)
Depth (float)
Time (datetime)
DataValue (float)
This data is defined in an external xsd. The hardware/operating system
requirements are basically a non-issue ie: whatever is needed can be
obtained.
The key thing is that about 100 wellbores with up to 100 curves (of sets of
data) can be active each second, so fast insert performance is critical.
Once inserted, the messiest queries involve:
1. All values for a specific curve within a range of depths
2. All values for a specific curve within a range of times
The things I'm pondering are:
1. Whether to have this in one database or to have a database per well.
There are about 1200 wells, but about 100 active concurrently.
2. Whether to store the data in a heap or a clustered index on either depth
or time.
3. Whether to use a non-clustered index for whichever of depth or time
wasn't the clustered one OR whether to store the data twice ie: once by date
and once by time.
4. Whether to find a way to limit the range of depth values (currently
float) to store them instead as a bigint, if the performance of bigint's in
indexes and range queries, etc. is better than floats.
I'd love to hear anyone's thoughts on this. We're about to start
experimenting to see what works.
Regards,
GregGreg
Is it SQL Server 2005?
Well , if it needs perfom fast inserts I think to store the data in a
heap you can get better performance for inserting , however as you said
you need to have good performance on lits of query , so CI will be a good
candidate for range queries
Actually it is really hard to suggest something , you need to test it and
get a decision
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:udZGEsj9GHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hi Folks,
> I'm working on something a little different at present. Given it's a bit
> out of the ordinary, and it relates to some of our indexing discussions
> lately, I'd appreciate any thoughts/feedback.
> It's a SQL Server box that's receiving a stream of real-time data from oil
> drilling rigs all over the world. It needs to perform both fast inserts
> (ie 10,000 per second) and also have good performance on a fixed list of
> query types. The essential part of the table data that has me most
> intrigued looks like this:
> Well (ID of the well involved - varchar(60))
> Wellbore (ID of a specific bore at a wellsite - varchar(60))
> Curve (ID of a specific set of measures eg: pressures, gas levels, etc.)
> Depth (float)
> Time (datetime)
> DataValue (float)
> This data is defined in an external xsd. The hardware/operating system
> requirements are basically a non-issue ie: whatever is needed can be
> obtained.
> The key thing is that about 100 wellbores with up to 100 curves (of sets
> of data) can be active each second, so fast insert performance is
> critical. Once inserted, the messiest queries involve:
> 1. All values for a specific curve within a range of depths
> 2. All values for a specific curve within a range of times
> The things I'm pondering are:
> 1. Whether to have this in one database or to have a database per well.
> There are about 1200 wells, but about 100 active concurrently.
> 2. Whether to store the data in a heap or a clustered index on either
> depth or time.
> 3. Whether to use a non-clustered index for whichever of depth or time
> wasn't the clustered one OR whether to store the data twice ie: once by
> date and once by time.
> 4. Whether to find a way to limit the range of depth values (currently
> float) to store them instead as a bigint, if the performance of bigint's
> in indexes and range queries, etc. is better than floats.
> I'd love to hear anyone's thoughts on this. We're about to start
> experimenting to see what works.
> Regards,
> Greg
>|||Hi...
We had a simmilar problem just alitle bit scaled down.
We implemented like:
- use an autoid for PK -> heap, rows are appended in the pages, no other
indexes, no constrains, all collumns allows null.
- use EE version (sql2005) :)
- use snapshot isolation mode for querying the data. (decrease lockings)
- use for querying a second maschine wich is sync-ing the data each n
sec/min. (you can try a log shipping but we've got no time to checkit)
(decreasing lockings, free cpu).
Have phun,
Radu
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:udZGEsj9GHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hi Folks,
> I'm working on something a little different at present. Given it's a bit
> out of the ordinary, and it relates to some of our indexing discussions
> lately, I'd appreciate any thoughts/feedback.
> It's a SQL Server box that's receiving a stream of real-time data from oil
> drilling rigs all over the world. It needs to perform both fast inserts
> (ie 10,000 per second) and also have good performance on a fixed list of
> query types. The essential part of the table data that has me most
> intrigued looks like this:
> Well (ID of the well involved - varchar(60))
> Wellbore (ID of a specific bore at a wellsite - varchar(60))
> Curve (ID of a specific set of measures eg: pressures, gas levels, etc.)
> Depth (float)
> Time (datetime)
> DataValue (float)
> This data is defined in an external xsd. The hardware/operating system
> requirements are basically a non-issue ie: whatever is needed can be
> obtained.
> The key thing is that about 100 wellbores with up to 100 curves (of sets
> of data) can be active each second, so fast insert performance is
> critical. Once inserted, the messiest queries involve:
> 1. All values for a specific curve within a range of depths
> 2. All values for a specific curve within a range of times
> The things I'm pondering are:
> 1. Whether to have this in one database or to have a database per well.
> There are about 1200 wells, but about 100 active concurrently.
> 2. Whether to store the data in a heap or a clustered index on either
> depth or time.
> 3. Whether to use a non-clustered index for whichever of depth or time
> wasn't the clustered one OR whether to store the data twice ie: once by
> date and once by time.
> 4. Whether to find a way to limit the range of depth values (currently
> float) to store them instead as a bigint, if the performance of bigint's
> in indexes and range queries, etc. is better than floats.
> I'd love to hear anyone's thoughts on this. We're about to start
> experimenting to see what works.
> Regards,
> Greg
>|||On 23.10.2006 02:34, Greg Low [MVP] wrote:
> Hi Folks,
> I'm working on something a little different at present. Given it's a bit out
> of the ordinary, and it relates to some of our indexing discussions lately,
> I'd appreciate any thoughts/feedback.
> It's a SQL Server box that's receiving a stream of real-time data from oil
> drilling rigs all over the world. It needs to perform both fast inserts (ie
> 10,000 per second) and also have good performance on a fixed list of query
> types. The essential part of the table data that has me most intrigued looks
> like this:
> Well (ID of the well involved - varchar(60))
> Wellbore (ID of a specific bore at a wellsite - varchar(60))
> Curve (ID of a specific set of measures eg: pressures, gas levels, etc.)
> Depth (float)
> Time (datetime)
> DataValue (float)
> This data is defined in an external xsd. The hardware/operating system
> requirements are basically a non-issue ie: whatever is needed can be
> obtained.
> The key thing is that about 100 wellbores with up to 100 curves (of sets of
> data) can be active each second, so fast insert performance is critical.
> Once inserted, the messiest queries involve:
> 1. All values for a specific curve within a range of depths
> 2. All values for a specific curve within a range of times
> The things I'm pondering are:
> 1. Whether to have this in one database or to have a database per well.
> There are about 1200 wells, but about 100 active concurrently.
What about partitioning by wellid? You should at least create a setup
with allows for as much concurrent processing as possible (i.e.
separating data physically on different disks or at least file groups
etc.) to be able to stream your data into the DB as independently as
possible.
> 2. Whether to store the data in a heap or a clustered index on either depth
> or time.
> 3. Whether to use a non-clustered index for whichever of depth or time
> wasn't the clustered one OR whether to store the data twice ie: once by date
> and once by time.
If you need purging of old data and query according to time range then a
CI with timestamp as leading column is certainly a good idea.
> 4. Whether to find a way to limit the range of depth values (currently
> float) to store them instead as a bigint, if the performance of bigint's in
> indexes and range queries, etc. is better than floats.
I would start out with floats if the data is actually float. Only go
for this type of optimization if you actually need it and it is proved
that bigint is faster.
> I'd love to hear anyone's thoughts on this. We're about to start
> experimenting to see what works.
On a more general level I am thinking that Oracle might be more suited
for this scenario because it offers several features that give you
better control. For example, you do not have a single CI on a table -
basically all indexes are ordered (but you can mimic SQL Server's CI
with an IOT). Another point is partitioning of tables, Oracle is much
more flexible there (IIRC you can do only hash partitioning on SQL 2005).
Just my 0.02EUR...
Kind regards
robert

hierarchy structure

hello!
I have a hierarchy structure copied from excel to a table in databse SQL.
Here is what i have in SQL table:
ContinentCountryCity
Europe
Norway
Oslo
Bergen
Trondheim
Sweden
Stockholm
Gotaborg
Denmark
Kopenhagen
Aalborg
Germany
Hamburg
Munchen
Hannover
Asia
Japan
Tokyo
Hirroshima
Kina
Shanghai
Africa
South-Africa
Pretoria
AND her is what i want
ContinentCountryCity
Europe
EuropeNorway
EuropeNorwayOslo
EuropeNorwayBergen
EuropeNorwayTrondheim
EuropeSweden
EuropeSwedenStockholm
EuropeSwedenGotaborg
EuropeDenmark
EuropeDenmarkKopenhagen
EuropeDenmarkAalborg
EuropeGermany
EuropeGermanyHamburg
EuropeGermanyMunchen
EuropeGermanyHannover
Asia
AsiaJapan
AsiaJapanTokyo
AsiaJapanHirroshima
AsiaKina
AsiaKinaShanghai
AfricaKina
AfricaSouth-Africa
AfricaSouth-AfricaPretoria
How do i that?
On Wed, 25 May 2005 02:23:02 -0700, CJ wrote:

>hello!
>I have a hierarchy structure copied from excel to a table in databse SQL.
>Here is what i have in SQL table:
(snip)
Hi CJ,
Do you mean that each row has either Continent, or Country, or City
populated, but none of the other columns? I guess you're out of luck
then. Since there is no inherent order in a relational table, there is
no difference between the table you posted and this one:
ContinentCountryCity
Africa
Asia
Europe
Denmark
Germany
Japan
Kina
Norway
South-Africa
Sweden
Aalborg
Bergen
Gotaborg
Hannover
Hamburg
Hirroshima
Kopenhagen
Munchen
Oslo
Pretoria
Stockholm
Shanghai
Tokyo
Trondheim
Besides - since this table has no key, it is officially not a relational
table at all. Of course, terminology is pretty much a question of
definitions, but a table without primary key is usually impossible to do
any serious operations on.

>AND her is what i want
>ContinentCountryCity
>Europe
>EuropeNorway
>EuropeNorwayOslo
>EuropeNorwayBergen
>EuropeNorwayTrondheim
>EuropeSweden
>EuropeSwedenStockholm
>EuropeSwedenGotaborg
>EuropeDenmark
>EuropeDenmarkKopenhagen
>EuropeDenmarkAalborg
>EuropeGermany
>EuropeGermanyHamburg
>EuropeGermanyMunchen
>EuropeGermanyHannover
>Asia
>AsiaJapan
>AsiaJapanTokyo
>AsiaJapanHirroshima
>AsiaKina
>AsiaKinaShanghai
>AfricaKina
>AfricaSouth-Africa
>AfricaSouth-AfricaPretoria
>How do i that?
You don't. The data above has no key either, so it's not a relational
table. Consider moving the data to some properly normalized tables:
CREATE TABLE Countries
(Country varchar(20) NOT NULL,
Continent varchar(10) NOT NULL,
PRIMARY KEY (Country)
)
CREATE TABLE Cities
(City varchar(25) NOT NULL,
Country varchar(20) NOT NULL,
PRIMARY KEY (City),
FOREIGN KEY (Country) REFERENCES Countries (Country)
)
INSERT INTO Countries (Country, Continent)
VALUES ('Norway', 'Europe')
INSERT INTO Countries (Country, Continent)
VALUES ('Sweden', 'Europe')
....
INSERT INTO Countries (Country, Continent)
VALUES ('South-Africa', 'Africa')
INSERT INTO Cities (City, Country)
VALUES ('Oslo', 'Norway')
INSERT INTO Cities (City, Country)
VALUES ('Bergen', 'Norway')
....
INSERT INTO Cities (City, Country)
VALUES ('Pretoria', 'South-Africa')
The input needed to get the listing you describe above would be returned
by
SELECT co.Continent, co.Country, ci.City
FROM Countries AS co
INNER JOIN Cities AS ci
ON ci.Country = co.Country
ORDER BY co.Continent, co.Country, ci.City
The final reformatting to get it displayed exectly as above (including
the repeating of some lines with partial blank data) should be handled
by the presentation tier.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Hierarchy

Hi to All!

Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way:

[Node | ParentId | ParentType | ChildId | ChildType]

But there is this nagging little voice saying it can be better

Cheers!

Nele

? Can you share some additional information? What do the "types" represent? Most of the time when I see the need for typing, it means that a table for that type should be created, and through key relationships this forms a natural hierarchy. In your system can the type hierarchy change? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Eburon@.discussions.microsoft.com> wrote in message news:f4c76476-0e56-4c53-939b-ffb7750ab228@.discussions.microsoft.com... Hi to All! Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way: [Node | ParentId | ParentType | ChildId | ChildType] But there is this nagging little voice saying it can be better Cheers! Nele|||

[same author as Eburon]

It is indeed a natural hierarchy. I assume that the type hierarchy cannot change, but a type can be absent. More in detail there are three types, t1, t2, t3:

t1 is always the root [level 1],|||? I would model this as three tables. T2 and T3 would both have self-referencing keys (i.e., "parent" keys)... CREATE TABLE T1 ( T1Id INT NOT NULL UNIQUE, ... //other attributes, including natural PK ) GO CREATE TABLE T2 ( T1id INT NULL REFERENCES T1 (T1id), T2id INT NOT NULL UNIQUE, parentT2id INT NULL REFERENCES T2 (T2id), ...//other attributes, PK, etc CONSTRAINT CheckIDsNOTNULL CHECK (COALESCE(T1id, T2id) IS NOT NULL) ) GO CREATE TABLE T3 ( T2id INT NULL REFERENCES T3 (T13id), T3id INT NOT NULL UNIQUE, parentT3id INT NULL REFERENCES T2 (T3id), ...//other attributes, PK, etc CONSTRAINT CheckIDsNOTNULL CHECK (COALESCE(T2id, T3id) IS NOT NULL) ) GO -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Nele@.discussions.microsoft..com> wrote in message news:27029b28-63cc-419f-8340-5be221c78682@.discussions.microsoft.com... [same author as Eburon] It is indeed a natural hierarchy. I assume that the type hierarchy cannot change, but a type can be absent. More in detail there are three types, t1, t2, t3: t1 is always the root [level 1], t2 may be added to go lower down the hierarchy [level 1.1 -> level 1 ... 1], and t3 may be added as a child [level 1 ... 1.1]. Is that sufficient additional information?|||I had not thought of that solution. Thank you!

Hierarchy

Hello all,
I have the following table structure (legacy, not my design :-D ):
CREATE TABLE [dbo].[hierarchy]
([idproduct] [int] NOT NULL ,
[name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[idlevel0] [int] NOT NULL ,
[name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel1] [int] NULL ,
[name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel2] [int] NULL ,
[name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
)
ON [PRIMARY]
GO
Some data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
idproduct field: id of a product
name= name o a product
idlevel0 = id of the root hierarchy
name0 = name of the root hierarchy
idlevel1 = id of the second node in the hierarchy
name1 = name of the second node in the hierarchy
idlevel2 = id of the third node in the hierarchy
name2 = name of the third node in the hierarchy
basically, there can be only three levels in the tree but as much brances as
the number of records are.
what I want is to reformat the hierarchy this way:
nodeid / nodename/ parentid
any hints on this?
Kind regards,
TudorSorry, pressed send before i actually finished:
So, further data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1001, 'Product2', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1003, 'Product3', 1, 'root node', 10,
'Intermediate10', 101, 'Intremediate201')
insert into hierarchy values (1004, 'Product4', 1, 'root node', 11,
'Intermediate11', 100, 'Intremediate202')
insert into hierarchy values (1005, 'Product5', 1, 'root node', 12,
'Intermediate12', 102, 'Intremediate202')
I would like the data to be stored as this:
nodeid nodename parentid
1 rootnode
2 intermediate10 1
3 intermediate200 2
4 intermediate200 2
5 intermediate11 1
etc...
Thanks,
Tudor
"Tudor" <tudor@.hopscotch.com> wrote in message
news:eeN5qxbQGHA.1868@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have the following table structure (legacy, not my design :-D ):
> CREATE TABLE [dbo].[hierarchy]
> ([idproduct] [int] NOT NULL ,
> [name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [idlevel0] [int] NOT NULL ,
> [name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel1] [int] NULL ,
> [name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel2] [int] NULL ,
> [name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> )
> ON [PRIMARY]
> GO
> Some data:
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
>
> idproduct field: id of a product
> name= name o a product
> idlevel0 = id of the root hierarchy
> name0 = name of the root hierarchy
> idlevel1 = id of the second node in the hierarchy
> name1 = name of the second node in the hierarchy
> idlevel2 = id of the third node in the hierarchy
> name2 = name of the third node in the hierarchy
> basically, there can be only three levels in the tree but as much brances
> as the number of records are.
> what I want is to reformat the hierarchy this way:
> nodeid / nodename/ parentid
> any hints on this?
> Kind regards,
> Tudor
>
>
>

Hierarchical Query

I have a table representing a tree structure - self referencing table. For
any node, I need to traverse upwards until root and retreives all the
traversed nodes.
With SQL only, how could I do this? In Oracle, there is START WITH, CONNECT
BY clause to help me. What is the equivalence in sql server?Here is an alternative method
http://toponewithties.blogspot.com/
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"as" <none@.asgmeail.com> wrote in message
news:42928048$1_1@.rain.i-cable.com...
>I have a table representing a tree structure - self referencing table. For
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH,
> CONNECT
> BY clause to help me. What is the equivalence in sql server?
>|||See Itzik Ben-Gan's example
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
"as" <none@.asgmeail.com> wrote in message
news:42928048$1_1@.rain.i-cable.com...
> I have a table representing a tree structure - self referencing table.
For
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH,
CONNECT
> BY clause to help me. What is the equivalence in sql server?
>|||Hi as,
Can you try the something like following which i implemented while working
on Hierarchical query (I converted this to a stored proc. This code example
is given in Books online, with little modification it worked fine for me.)
--Begin--
--
Accessing and Changing Relational Data
Expanding Hierarchies
Databases often store hierarchical information. For example, the following
data is a hierarchical representation of regions of the world. This
representation does not clearly show the structure implied by the data.
Parent Child
-- --
World Europe
World North America
Europe France
France Paris
North America United States
North America Canada
United States New York
United States Washington
New York New York City
Washington Redmond
This example is easier to interpret:
World
North America
Canada
United States
Washington
Redmond
New York
New York City
Europe
France
Paris
The following Transact-SQL procedure expands an encoded hierarchy to any
arbitrary depth. Although Transact-SQL supports recursion, it is more
efficient to use a temporary table as a stack to keep track of all of the
items for which processing has begun but is not complete. When processing is
complete for a particular item, it is removed from the stack. New items are
added to the stack as they are identified.
CREATE PROCEDURE expand (@.current char(20)) as
SET NOCOUNT ON
DECLARE @.level int, @.line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@.current, 1)
SELECT @.level = 1
WHILE @.level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
BEGIN
SELECT @.current = item
FROM #stack
WHERE level = @.level
SELECT @.line = space(@.level - 1) + @.current
PRINT @.line
DELETE FROM #stack
WHERE level = @.level
AND item = @.current
INSERT #stack
SELECT child, @.level + 1
FROM hierarchy
WHERE parent = @.current
IF @.@.ROWCOUNT > 0
SELECT @.level = @.level + 1
END
ELSE
SELECT @.level = @.level - 1
END -- WHILE
The input parameter (@.current) indicates the place in the hierarchy to
start. It also keeps track of the current item in the main loop.
The local variables used are @.level, which keeps track of the current level
in the hierarchy, and @.line, which is a work area used to construct the
indented line.
The SET NOCOUNT ON statement avoids cluttering the output with ROWCOUNT
messages from each SELECT.
The temporary table, #stack, is created and primed with the item identifier
of the starting point in the hierarchy, and @.level is set to match. The leve
l
column in #stack allows the same item to appear at multiple levels in the
database. Although this situation does not apply to the geographic data in
the example, it can apply in other examples.
In this example, when @.level is greater than 0, the procedure follows these
steps:
If there are any items in the stack at the current level (@.level), the
procedure chooses one and calls it @.current.
Indents the item @.level spaces, and then prints the item.
Deletes the item from the stack so it will not be processed again, and then
adds all its child items to the stack at the next level (@.level + 1). This i
s
the only place where the hierarchy table (#stack) is used.
With a conventional programming language, you would have to find each child
item and add it to the stack individually. With Transact-SQL, you can find
all child items and add them with a single statement, avoiding another neste
d
loop.
If there are child items (IF @.@.ROWCOUNT > 0), descends one level to process
them (@.level = @.level + 1); otherwise, continues processing at the current
level.
If there are no items on the stack awaiting processing at the current level,
goes back one level to see if there are any awaiting processing at the
previous level (@.level = @.level - 1). When there is no previous level, the
expansion is complete.
--End---
--
Regards,
Siva
"as" wrote:

> I have a table representing a tree structure - self referencing table. Fo
r
> any node, I need to traverse upwards until root and retreives all the
> traversed nodes.
> With SQL only, how could I do this? In Oracle, there is START WITH, CONNE
CT
> BY clause to help me. What is the equivalence in sql server?
>
>|||>> What is the equivalent in sql server? <<
The equivalent is a cursor becasue that what Oracle does under the
covers in their proprietary syntax. Instead:
1) Get a copy of TREES & HIERACHIES IN SQL from Amazon.com. There are
much better ways of modeling a tree.
2) Google for "Nested set model" in this newsgroup. There is no need
for procedural code.l|||Joe,
I would like to know your opinion about the approach discussed in
http://toponewithties.blogspot.com/ (Path Enumeration using Prime number
Products)
Regards
Roji
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116960769.637939.154330@.z14g2000cwz.googlegroups.com...
> The equivalent is a cursor becasue that what Oracle does under the
> covers in their proprietary syntax. Instead:
> 1) Get a copy of TREES & HIERACHIES IN SQL from Amazon.com. There are
> much better ways of modeling a tree.
> 2) Google for "Nested set model" in this newsgroup. There is no need
> for procedural code.l
>|||I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
The only problem I can see is the size of the primes as the trees get
larger, but we are living in a 64-bit world now. Since the math is
simple integer division and multiplication, the speed is probably
pretty good.
Random thought: if we give each node a prime in a general graph, then a
cycle|||Thanks Joe,
I am a happy man.
You can consider this for the next version of TREES & HIERARCHIES IN SQL :)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117037337.540731.73370@.g14g2000cwa.googlegroups.com...
>I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
> The only problem I can see is the size of the primes as the trees get
> larger, but we are living in a 64-bit world now. Since the math is
> simple integer division and multiplication, the speed is probably
> pretty good.
> Random thought: if we give each node a prime in a general graph, then a
> cycle
>|||--CELKO-- wrote:
> I wish I had seen this before finishing TREES & HIERARCHIES IN SQL.
> The only problem I can see is the size of the primes as the trees get
> larger, but we are living in a 64-bit world now. Since the math is
> simple integer division and multiplication, the speed is probably
> pretty good.
Kendall Willets suggested this method on comp.databases.theory some
time ago.

> Random thought: if we give each node a prime in a general graph, then a
> cycle
Prime numbers set with "divided by" binary relation is a partial order.
More specifically it is a lattice. An arbitrary DAG can be embedded
into a lattice. However, the prime numbers encoding for graphs is
volatile. Adding a node into a graph would force to recalculate
encodings for large graph fragment. Plus, how to handle acyclic graphs?|||Mikito Harakiri wrote:
> --CELKO-- wrote:
> Kendall Willets suggested this method on comp.databases.theory some
> time ago.
>
> Prime numbers set with "divided by" binary relation is a partial order.
> More specifically it is a lattice. An arbitrary DAG can be embedded
> into a lattice. However, the prime numbers encoding for graphs is
> volatile. Adding a node into a graph would force to recalculate
> encodings for large graph fragment. Plus, how to handle acyclic graphs?
Could the access path be indexed? Imagine that we don't have this silly
64 bit limit and you are presented with a node encoded with a number
3107418240490043721350750035888567930037
3460228427
2754572016194882320644051808150455634682
9671723286
7824379162728380334154710731085019195485
2900733772
4822783525742386454014691736602477652346
609
(RSA-640) and are asked what are the ancestors. Can you answer that in
a reasonable amount of time.
I'm cheating of course, if the primes are small you can factor the
number reasonably fast. Still, what about the access path? For
comparison, in case of nested sets we are talking about index range
scan (at least one way).