Performance Q

Hi,
I have a table which stores a parent-child relationship and I want to update
a column in the table to hold the ultimate parent, i.e. the top level parent
for each parent. I have written a scalar UDF which creates a creates a table
var to hold all the relationships, I then select the top level from this.
To update 6793 rows it takes just over 2mins, my question is does this seem
like acceptable performance? I'm trying to improve the performance and
looking at the execution plan I can see 63% of the cost is a Table
Spool/Eager spool, another 29% is a table scan.
I have tried creating a unique non-clustered and also a clustered index on
ParentId,ChildId and re-running the query, each time the execution plan
changes to use the index but the overall time is not reduced.
My next question is are there are performance issues or other concerns with
using a datetime field in an index? I have a table which stores historical
data for several days and often want to pull out one days info also I want t
o
enforce that the data cannot be duplicated for the same day so I want to
create a composite key on Date_Stamp plus another col.That sounds horrific, no that cant be ok to update ~7000 rows within 2 min,
since i dont guess that these are really big and complex function, (are
they ?). Just post you DDL in here to see where the bottleneck is located
at.
In common, indexes on datetime columns work fine.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Neil" <Neil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:538D1605-FF40-4B0B-81FA-88B6D22577D4@.microsoft.com...
> Hi,
> I have a table which stores a parent-child relationship and I want to
> update
> a column in the table to hold the ultimate parent, i.e. the top level
> parent
> for each parent. I have written a scalar UDF which creates a creates a
> table
> var to hold all the relationships, I then select the top level from this.
> To update 6793 rows it takes just over 2mins, my question is does this
> seem
> like acceptable performance? I'm trying to improve the performance and
> looking at the execution plan I can see 63% of the cost is a Table
> Spool/Eager spool, another 29% is a table scan.
> I have tried creating a unique non-clustered and also a clustered index on
> ParentId,ChildId and re-running the query, each time the execution plan
> changes to use the index but the overall time is not reduced.
>
> My next question is are there are performance issues or other concerns
> with
> using a datetime field in an index? I have a table which stores historical
> data for several days and often want to pull out one days info also I want
> to
> enforce that the data cannot be duplicated for the same day so I want to
> create a composite key on Date_Stamp plus another col.|||We really need to see your DDL to figure that out. Also, you can try to use
System Monitor to look at Avg. Disk Queue Length, Buffer Cache Hit Ratio and
Page Life Expectancy. You may be memory starved.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Neil" <Neil@.discussions.microsoft.com> wrote in message
news:538D1605-FF40-4B0B-81FA-88B6D22577D4@.microsoft.com...
Hi,
I have a table which stores a parent-child relationship and I want to update
a column in the table to hold the ultimate parent, i.e. the top level parent
for each parent. I have written a scalar UDF which creates a creates a table
var to hold all the relationships, I then select the top level from this.
To update 6793 rows it takes just over 2mins, my question is does this seem
like acceptable performance? I'm trying to improve the performance and
looking at the execution plan I can see 63% of the cost is a Table
Spool/Eager spool, another 29% is a table scan.
I have tried creating a unique non-clustered and also a clustered index on
ParentId,ChildId and re-running the query, each time the execution plan
changes to use the index but the overall time is not reduced.
My next question is are there are performance issues or other concerns with
using a datetime field in an index? I have a table which stores historical
data for several days and often want to pull out one days info also I want
to
enforce that the data cannot be duplicated for the same day so I want to
create a composite key on Date_Stamp plus another col.|||We really need to see your DDL to figure that out. Also, you can try to use
System Monitor to look at Avg. Disk Queue Length, Buffer Cache Hit Ratio and
Page Life Expectancy. You may be memory starved.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Neil" <Neil@.discussions.microsoft.com> wrote in message
news:538D1605-FF40-4B0B-81FA-88B6D22577D4@.microsoft.com...
Hi,
I have a table which stores a parent-child relationship and I want to update
a column in the table to hold the ultimate parent, i.e. the top level parent
for each parent. I have written a scalar UDF which creates a creates a table
var to hold all the relationships, I then select the top level from this.
To update 6793 rows it takes just over 2mins, my question is does this seem
like acceptable performance? I'm trying to improve the performance and
looking at the execution plan I can see 63% of the cost is a Table
Spool/Eager spool, another 29% is a table scan.
I have tried creating a unique non-clustered and also a clustered index on
ParentId,ChildId and re-running the query, each time the execution plan
changes to use the index but the overall time is not reduced.
My next question is are there are performance issues or other concerns with
using a datetime field in an index? I have a table which stores historical
data for several days and often want to pull out one days info also I want
to
enforce that the data cannot be duplicated for the same day so I want to
create a composite key on Date_Stamp plus another col.|||The DDL for the table is -
CREATE TABLE [dbo].[CDRCustomerRelationship] (
DATE_STAMP datetime DEFAULT getdate(),
[ParentId] [int] NOT NULL ,
[ChildId] [int] NOT NULL ,
[DeletionFlag] [char] (1) NOT NULL ,
[UltimateParent] [int] NULL ,
[TopLevelFlag] AS (CASE WHEN ([ParentID] = [UltimateParent]) THEN 1 ELSE 0
END)
) ON [PRIMARY]
GO
The DDL for the UDF is -
/* Returns the Ultimate Parent */
CREATE FUNCTION [dbo].[ufGetUltimateParent] (@.iStartingPoint int)
RETURNS int
AS
BEGIN
DECLARE @.EntityInfo TABLE
(
EParentId int, -- Parent EntityID
EChildId int, -- EntityID
Depth int -- depth
)
DECLARE @.UltimateEParentId int
DECLARE @.Level int
SELECT @.Level = 0 -- initializes the level to value 0
DECLARE @.InsertCount int
SELECT @.InsertCount = 1
-- Try to find the first Parent Record by selecting the starting parent as a
child
INSERT INTO @.EntityInfo
SELECT
ParentId, -- parent id which will be @.startPoint
ChildId, -- child id
@.Level -- depth
FROM
DBO.CDRCustomerRelationship
WHERE
ChildId = @.iStartingPoint
AND DeletionFlag = 'N'
-- Check To see if ultimate parent
SELECT @.InsertCount = @.@.ROWCOUNT
IF ( @.InsertCount > 0 )
BEGIN
SELECT @.Level = @.Level + 1
-- There are more parents so start finding them
WHILE @.InsertCount > 0
BEGIN
INSERT @.EntityInfo
SELECT
ParentId, -- relateditem
ChildId, -- item
@.Level -- depth
FROM
(SELECT DISTINCT EParentId FROM @.EntityInfo WHERE Depth = @.Level-1) AS tmp
INNER JOIN
dbo.CDRCustomerRelationship
ON tmp.EParentId = ChildId
WHERE DeletionFlag = 'N'
SELECT @.InsertCount = @.@.ROWCOUNT
-- Check To see if ultimate parent
IF ( @.InsertCount > 0 )
SELECT @.Level = @.Level + 1
END -- end Loop, no more parents
END
ELSE
BEGIN --No parents in hierachy so insert record for itself
INSERT INTO @.EntityInfo
SELECT
@.iStartingPoint,
@.iStartingPoint,
0 AS Depth
END
SELECT @.UltimateEParentId = EParentId FROM @.EntityInfo Where Depth =
(SELECT MAX(Depth) FROM @.EntityInfo)
RETURN @.UltimateEParentId
END

"Jens Sü?meyer" wrote:

> That sounds horrific, no that can′t be ok to update ~7000 rows within 2 m
in,
> since i don′t guess that these are really big and complex function, (are
> they ?). Just post you DDL in here to see where the bottleneck is located
> at.
> In common, indexes on datetime columns work fine.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Neil" <Neil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:538D1605-FF40-4B0B-81FA-88B6D22577D4@.microsoft.com...
>
>|||Out of interest what kind of performance would you expect time wise?
The DDL for the table is -
CREATE TABLE [dbo].[CDRCustomerRelationship] (
DATE_STAMP datetime DEFAULT getdate(),
[ParentId] [int] NOT NULL ,
[ChildId] [int] NOT NULL ,
[DeletionFlag] [char] (1) NOT NULL ,
[UltimateParent] [int] NULL ,
[TopLevelFlag] AS (CASE WHEN ([ParentID] = [UltimateParent]) THEN 1 ELSE 0
END)
) ON [PRIMARY]
GO
The DDL for the UDF is -
/* Returns the Ultimate Parent */
CREATE FUNCTION [dbo].[ufGetUltimateParent] (@.iStartingPoint int)
RETURNS int
AS
BEGIN
DECLARE @.EntityInfo TABLE
(
EParentId int, -- Parent EntityID
EChildId int, -- EntityID
Depth int -- depth
)
DECLARE @.UltimateEParentId int
DECLARE @.Level int
SELECT @.Level = 0 -- initializes the level to value 0
DECLARE @.InsertCount int
SELECT @.InsertCount = 1
-- Try to find the first Parent Record by selecting the starting parent as a
child
INSERT INTO @.EntityInfo
SELECT
ParentId, -- parent id which will be @.startPoint
ChildId, -- child id
@.Level -- depth
FROM
DBO.CDRCustomerRelationship
WHERE
ChildId = @.iStartingPoint
AND DeletionFlag = 'N'
-- Check To see if ultimate parent
SELECT @.InsertCount = @.@.ROWCOUNT
IF ( @.InsertCount > 0 )
BEGIN
SELECT @.Level = @.Level + 1
-- There are more parents so start finding them
WHILE @.InsertCount > 0
BEGIN
INSERT @.EntityInfo
SELECT
ParentId, -- relateditem
ChildId, -- item
@.Level -- depth
FROM
(SELECT DISTINCT EParentId FROM @.EntityInfo WHERE Depth = @.Level-1) AS tmp
INNER JOIN
dbo.CDRCustomerRelationship
ON tmp.EParentId = ChildId
WHERE DeletionFlag = 'N'
SELECT @.InsertCount = @.@.ROWCOUNT
-- Check To see if ultimate parent
IF ( @.InsertCount > 0 )
SELECT @.Level = @.Level + 1
END -- end Loop, no more parents
END
ELSE
BEGIN --No parents in hierachy so insert record for itself
INSERT INTO @.EntityInfo
SELECT
@.iStartingPoint,
@.iStartingPoint,
0 AS Depth
END
SELECT @.UltimateEParentId = EParentId FROM @.EntityInfo Where Depth =
(SELECT MAX(Depth) FROM @.EntityInfo)
RETURN @.UltimateEParentId
END
"Tom Moreau" wrote:

> We really need to see your DDL to figure that out. Also, you can try to u
se
> System Monitor to look at Avg. Disk Queue Length, Buffer Cache Hit Ratio a
nd
> Page Life Expectancy. You may be memory starved.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Neil" <Neil@.discussions.microsoft.com> wrote in message
> news:538D1605-FF40-4B0B-81FA-88B6D22577D4@.microsoft.com...
> Hi,
> I have a table which stores a parent-child relationship and I want to upda
te
> a column in the table to hold the ultimate parent, i.e. the top level pare
nt
> for each parent. I have written a scalar UDF which creates a creates a tab
le
> var to hold all the relationships, I then select the top level from this.
> To update 6793 rows it takes just over 2mins, my question is does this see
m
> like acceptable performance? I'm trying to improve the performance and
> looking at the execution plan I can see 63% of the cost is a Table
> Spool/Eager spool, another 29% is a table scan.
> I have tried creating a unique non-clustered and also a clustered index on
> ParentId,ChildId and re-running the query, each time the execution plan
> changes to use the index but the overall time is not reduced.
>
> My next question is are there are performance issues or other concerns wit
h
> using a datetime field in an index? I have a table which stores historical
> data for several days and often want to pull out one days info also I want
> to
> enforce that the data cannot be duplicated for the same day so I want to
> create a composite key on Date_Stamp plus another col.
>|||DIDmread the whole of your DDL code, taht moght keep you an imression of
doing it:
CREATE FUNCTION UlitmateParent
(
@.ChildValue INT
)
RETURNS INT
AS
BEGIN
DECLARE @.UltimateParent INT
DECLARE @.ParentID INT
SET @.ParentId = -1
WHILE @.UltimateParent IS NOT NULL -->Condition depends on what is entered
if there is not Parent at all
BEGIN
Select @.UltimateParent = ParentID from CDRCustomerRelationship Where
ChildID = @.ChildValue
IF @.UltimateParent IS NOT NULL
BEGIN
SET @.ChildValue = @.UltimateParent
END
END
RETURN @.ChildValue
END
Self-relational tables and queries can be found here:
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Neil" <Neil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7CB6B6F9-559F-4EDF-8602-48AF7423312D@.microsoft.com...
> The DDL for the table is -
>
> CREATE TABLE [dbo].[CDRCustomerRelationship] (
> DATE_STAMP datetime DEFAULT getdate(),
> [ParentId] [int] NOT NULL ,
> [ChildId] [int] NOT NULL ,
> [DeletionFlag] [char] (1) NOT NULL ,
> [UltimateParent] [int] NULL ,
> [TopLevelFlag] AS (CASE WHEN ([ParentID] = [UltimateParent]) THEN 1 ELSE 0
> END)
> ) ON [PRIMARY]
> GO
> The DDL for the UDF is -
> /* Returns the Ultimate Parent */
> CREATE FUNCTION [dbo].[ufGetUltimateParent] (@.iStartingPoint int)
> RETURNS int
> AS
> BEGIN
> DECLARE @.EntityInfo TABLE
> (
> EParentId int, -- Parent EntityID
> EChildId int, -- EntityID
> Depth int -- depth
> )
> DECLARE @.UltimateEParentId int
>
> DECLARE @.Level int
> SELECT @.Level = 0 -- initializes the level to value 0
> DECLARE @.InsertCount int
> SELECT @.InsertCount = 1
> -- Try to find the first Parent Record by selecting the starting parent as
> a
> child
> INSERT INTO @.EntityInfo
> SELECT
> ParentId, -- parent id which will be @.startPoint
> ChildId, -- child id
> @.Level -- depth
> FROM
> DBO.CDRCustomerRelationship
> WHERE
> ChildId = @.iStartingPoint
> AND DeletionFlag = 'N'
>
> -- Check To see if ultimate parent
> SELECT @.InsertCount = @.@.ROWCOUNT
> IF ( @.InsertCount > 0 )
> BEGIN
> SELECT @.Level = @.Level + 1
> -- There are more parents so start finding them
> WHILE @.InsertCount > 0
> BEGIN
> INSERT @.EntityInfo
> SELECT
> ParentId, -- relateditem
> ChildId, -- item
> @.Level -- depth
> FROM
> (SELECT DISTINCT EParentId FROM @.EntityInfo WHERE Depth = @.Level-1) AS tmp
> INNER JOIN
> dbo.CDRCustomerRelationship
> ON tmp.EParentId = ChildId
> WHERE DeletionFlag = 'N'
>
> SELECT @.InsertCount = @.@.ROWCOUNT
> -- Check To see if ultimate parent
> IF ( @.InsertCount > 0 )
> SELECT @.Level = @.Level + 1
> END -- end Loop, no more parents
> END
> ELSE
> BEGIN --No parents in hierachy so insert record for itself
> INSERT INTO @.EntityInfo
> SELECT
> @.iStartingPoint,
> @.iStartingPoint,
> 0 AS Depth
> END
> SELECT @.UltimateEParentId = EParentId FROM @.EntityInfo Where Depth =
> (SELECT MAX(Depth) FROM @.EntityInfo)
> RETURN @.UltimateEParentId
> END
>
>
>
>
>
>
>
>
>
>
>
>
> "Jens Smeyer" wrote:
>|||I have it down to 34 secs now by adding clustered index to table, this seems
more reasonable
"Jens Sü?meyer" wrote:

> DIDm′read the whole of your DDL code, taht moght keep you an imression of
> doing it:
>
> CREATE FUNCTION UlitmateParent
> (
> @.ChildValue INT
> )
> RETURNS INT
> AS
> BEGIN
> DECLARE @.UltimateParent INT
> DECLARE @.ParentID INT
> SET @.ParentId = -1
> WHILE @.UltimateParent IS NOT NULL -->Condition depends on what is enter
ed
> if there is not Parent at all
> BEGIN
> Select @.UltimateParent = ParentID from CDRCustomerRelationship Where
> ChildID = @.ChildValue
> IF @.UltimateParent IS NOT NULL
> BEGIN
> SET @.ChildValue = @.UltimateParent
> END
> END
> RETURN @.ChildValue
> END
>
> Self-relational tables and queries can be found here:
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Neil" <Neil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7CB6B6F9-559F-4EDF-8602-48AF7423312D@.microsoft.com...
>
>|||MS article here, sorry ;-)
http://msdn.microsoft.com/library/d...r />
_5yk3.asp
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:%23O36HAjVFHA.3636@.TK2MSFTNGP14.phx.gbl...
> DIDmread the whole of your DDL code, taht moght keep you an imression of
> doing it:
>
> CREATE FUNCTION UlitmateParent
> (
> @.ChildValue INT
> )
> RETURNS INT
> AS
> BEGIN
> DECLARE @.UltimateParent INT
> DECLARE @.ParentID INT
> SET @.ParentId = -1
> WHILE @.UltimateParent IS NOT NULL -->Condition depends on what is
> entered if there is not Parent at all
> BEGIN
> Select @.UltimateParent = ParentID from CDRCustomerRelationship Where
> ChildID = @.ChildValue
> IF @.UltimateParent IS NOT NULL
> BEGIN
> SET @.ChildValue = @.UltimateParent
> END
> END
> RETURN @.ChildValue
> END
>
> Self-relational tables and queries can be found here:
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Neil" <Neil@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7CB6B6F9-559F-4EDF-8602-48AF7423312D@.microsoft.com...
>|||Try to use the Function (perhaps with some corrections based on your
system), perhaps thatll also help you.
"Neil" <Neil@.discussions.microsoft.com> schrieb im Newsbeitrag
news:780B3895-1C43-4BB2-B360-444932B9AB2C@.microsoft.com...
>I have it down to 34 secs now by adding clustered index to table, this
>seems
> more reasonable
> "Jens Smeyer" wrote:
>

Performance Problems On Recursive Table

I have a table that has a parent-child relation to itself. (see SQL at end)
Basically, records of type 0 are related to type 1, and type 1 to type 2.
This table has about 2 million records right now. When I do this:
INNER JOIN
ExpSrvLog I ON E.ExpSrvID = I.ExpSrvID AND I.ItemType = 2
LEFT OUTER JOIN
ExpSrvLog B ON I.LogID = B.ParentID AND B.ItemType = 1
It seems to consume an inordinate amount of time. Any way to optimize this
behavior? Thanks.
CREATE TABLE [ExpSrvLog] (
[LogID] [bigint] IDENTITY (1, 1) NOT NULL ,
[ExpSrvID] [int] NOT NULL ,
[CalendarDate] [smalldatetime] NOT NULL ,
[ItemType] [tinyint] NOT NULL ,
[VendorInvoiceID] [int] NULL ,
[ClientInvoiceID] [int] NULL ,
[PaymentID] [int] NULL ,
[BillingAmount] [money] NULL ,
[ActualQty] [decimal](8, 2) NULL ,
[ActualRate] [money] NULL ,
[ParentID] [bigint] NULL ,
CONSTRAINT [PK_ExpSrvLog] PRIMARY KEY CLUSTERED
(
[LogID] DESC
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_ExpSrvLog_ClientInvoice] FOREIGN KEY
(
[ClientInvoiceID]
) REFERENCES [ClientInvoice] (
[InvoiceID]
),
CONSTRAINT [FK_ExpSrvLog_ExpSrvLog] FOREIGN KEY
(
[ParentID]
) REFERENCES [ExpSrvLog] (
[LogID]
),
CONSTRAINT [FK_ExpSrvLog_VendorInvoice] FOREIGN KEY
(
[VendorInvoiceID]
) REFERENCES [VendorInvoice] (
[InvoiceID]
)
) ON [PRIMARY]
ENDApart from the primary key, are there any other indexes on that table?
Foreign key columns should at least be indexed.
ML|||"ML" wrote:
> Apart from the primary key, are there any other indexes on that table?
Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FKs.
)
I've also tried an index over ParentID and LogID, to no help.
- alphadog|||Paul, While recursive joins are elegant, they are notoriously slow on
recursive tables with > 1 million rows. You might want try to use mutiples
queries and(or) tables to accomplish your task instead of the recursive join
s.
"Paul Tiseo" wrote:

> "ML" wrote:
> Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
> VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FK
s.)
> I've also tried an index over ParentID and LogID, to no help.
> - alphadog|||Really? Damn. It works so well that way. Oh well, thanks for the info, Frank
.
So, what are all my options? Physically splitting the table is something I
have planned, but at this point in developement is not an option. I guess
it'll have to be temp tables.
"frank chang" wrote:
> Paul, While recursive joins are elegant, they are notoriously slow on
> recursive tables with > 1 million rows. You might want try to use mutiples
> queries and(or) tables to accomplish your task instead of the recursive jo
ins.
> "Paul Tiseo" wrote:
>|||Maybe this example can help you find a way to navigate the
ancestor/descendant axes in your hierarchy:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||>> I have a table that has a parent-child relation to itself. (see SQL at end) Basical
ly, records [sic] of type 0 are related to type 1, and type 1 to type 2. This table h
as about 2 million records [sic] right now. When I do this: <<
No relational key, IDENTITY, too many NULL-able columns, money and
bigint proprierary datatypes and you don't know that rows and records
are not the same. .And there are not specs.
behavior? <<
Change the DDL. Does this hierarchy only go down three levels? Can
level 0 have more than one level 1 subordinate? Can a level 1 have
more than one level 2 subordinate? If I assume not, then:
CREATE TABLE ExpsrvLog
(expsrv_id INTEGER NOT NULL,
hierarchy_level INTEGER DEFAULT 0 NOT NULL
CHECK (hierarchy_level IN (0, 1, 2)),
payment_date DATETIME NOT NULL,
vendor_invoice_nbr INTEGER NOT NULL,
REFERENCES VendorInvoices (invoice_nbr)
ON UPDATE CASCADE,
client_invoice_nbr INTEGER NOT NULL
REFERENCES ClientInvoices (invoice_nbr)
ON UPDATE CASCADE,
payment_nbr INTEGER NOT NULL,
billing_amount DECIMAL(8,2) NOT NULL,
actual_qty DECIMAL(8,2) NOT NULL,
actual_rate DECIMAL(8,2) NULL,
PRIMARY KEY (expsrv_id, hierarchy_level));
If the asumption was wrong, we can move onto the nested sets model.
You might want to get a copy of TREES & HIERARCHIES IN SQL along with a
basic data modeling book.|||"--CELKO--" wrote:
> No relational key,
Of what type? I have a primary key (see first post), an alternate key (not
in the original DDL, but basically CREATE UNIQUE INDEX AK_ExpSrvLog ON
dbo.ExpSrvLog([ExpSrvID], [CalendarDate] DESC , [ItemType]) ) and some
primary-foreign key relationships. What exactly is missing? Do you mean a
natural key? If so, it's the AK that I did not include.

> IDENTITY
Why? How does IDENTITY affect my performance question?

> too many NULL-able columns,
The Real World intrudes into the World of Relational Model Puritans
sometimes. <shrug> Again, how do the NULLable columns affect my performance
problem?

> money and bigint proprierary datatypes
Not a problem for this project.

> and you don't know that rows and records are not the same.
Do you know what they say about "ASS-U-ME"? I use common idioms. Forgive me
for not abiding by your stricter one.

> And there are not specs.
I'm sorry, but I don't understand this comment. What specs do you need?

> Change the DDL. Does this hierarchy only go down three levels? Can
> level 0 have more than one level 1 subordinate? Can a level 1 have
> more than one level 2 subordinate? If I assume not, then:
Yes (three and only three levels), yes and yes.

> CREATE TABLE ExpsrvLog
> (expsrv_id INTEGER NOT NULL,
> hierarchy_level INTEGER DEFAULT 0 NOT NULL
> CHECK (hierarchy_level IN (0, 1, 2)),
> payment_date DATETIME NOT NULL,
> vendor_invoice_nbr INTEGER NOT NULL,
> REFERENCES VendorInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> client_invoice_nbr INTEGER NOT NULL
> REFERENCES ClientInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> payment_nbr INTEGER NOT NULL,
> billing_amount DECIMAL(8,2) NOT NULL,
> actual_qty DECIMAL(8,2) NOT NULL,
> actual_rate DECIMAL(8,2) NULL,
> PRIMARY KEY (expsrv_id, hierarchy_level));
> If the asumption was wrong, we can move onto the nested sets model.
So, you changed the structure of the table in a way that doesn't address my
original problem (in fact you seem to have removed the recursive
relationship) but makes it more purist-friendly in terms of datatypes and
nullability. Your "SQL standardization" and "Relational Model" efforts are
appreciated (although your zeal blinded you to my actual question) but it's
not a solution to my immediate problem.
I don't need to know *how* to model or query the hierarchy. I need to know
*why* it isn't performing properly when I use the self-join.
Thanks.sql