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:
>