September 9, 2011 at 12:07 pm
I wrote a table valued function for SS2005 and it executes in under 1 second but on another server with 2008, it takes almost 1:30 or more to complete. Why the huge slowdown? I found this article but it seems to be only indirectly related to my plight. I'm recommending that we switch the compatibility level to 2005 and hope for the best.
Any help would be greatly appreciated.
[url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7e2695fe-d879-4af7-97cf-e6fed62368e8">
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7e2695fe-d879-4af7-97cf-e6fed62368e8
September 9, 2011 at 12:15 pm
Well there could be lots of reasons. Perhaps jsut change the table variable to a temp table well help?
September 9, 2011 at 12:18 pm
Temp tables do speed things up when I run the individual queries that make up the function. However, table valued functions can't use temp tables.
September 9, 2011 at 12:25 pm
Sorry I mis-read that as a table variable issue not a tabled valued function.
Can post the code? We might be able to help with some suggestions. If it's not already, can you convert it to an inline table valued funtion or a view?
September 9, 2011 at 12:28 pm
Sorry, I should clarify: it is a table valued function that uses table variables.
September 9, 2011 at 12:35 pm
Lamprey13 (9/9/2011)
Sorry I mis-read that as a table variable issue not a tabled valued function.Can post the code? We might be able to help with some suggestions. If it's not already, can you convert it to an inline table valued funtion or a view?
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ITX_GETALLSUBS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [ITX_GETALLSUBS]
GO
CREATE FUNCTION [dbo].[ITX_GETALLSUBS](@NODE VARCHAR(32))
RETURNS @Team TABLE (Prov_OID Varchar(32), Prov_Level FLOAT, Provider Varchar(32), Sup_OID Varchar(32), HeirarchyOrder VarBinary(MAX), Level INT)
AS
BEGIN
DECLARE @P TABLE (Prov_OID VARCHAR(32), Prov_Level FLOAT, Sup_OID Varchar(32))
INSERT @P
Select
Provider.OID Prov_OID,
MAX(CAST(SUBSTRING(L.Cad4,6,5) AS FLOAT)) Prov_Level,
MAX(CAST(USER_DEFINED_DATA.CAD4 AS Varchar(32))) Sup_OID
--CAST(Cad11 AS Varchar(32)) Agency_Cost_Center_Helper_OID,
From
PROVIDER
INNER JOIN USER_DEFINED_DATA ON PROVIDER.OID = USER_DEFINED_DATA.ATTACHED_TO_OID
LEFT JOIN dbo.USER_DEFINED_DATA L ON (CAST(USER_DEFINED_DATA.ATTACHED_TO_OID AS VARCHAR(32))) = L.ATTACHED_TO_OID
AND L.CAD1='vITX_DS_LEVEL 2009.12.10.1_1.2'
AND ( L.ExpDate IS NULL
OR L.ExpDate > GETDATE()
)
Where
--ASSESSMENT.FULLNAME = 'ITX_COSTCENTER'
USER_DEFINED_DATA.CAD1 = 'vITX_DS_TEAM_HIERARCHY_1.2'
AND (USER_DEFINED_DATA.ExpDate IS NULL OR USER_DEFINED_DATA.ExpDate>GETDATE())
GROUP BY Provider.OID
DECLARE @h TABLE (Prov_OID VARCHAR(32), Prov_Level Float, Sup_OID VARCHAR(32))--, ACCH_OID VARCHAR(32))
INSERT @h
Select Prov_OID, MIN(Prov_Level) , Sup_OID From @P Group By Prov_OID, Sup_OID
;WITH
cteTree
as
(
select --top 1
H.Prov_OID,
H.Prov_Level,
P.LName+', '+P.FName Provider,
H.Sup_OID,
cast(cast(ROW_NUMBER() over (order by H.Prov_OID) AS INTEGER) as varbinary(max)) as HeirarchyOrder,
1 as Level
from @h H INNER JOIN Provider P ON H.Prov_OID=P.OID
--where #H.Sup_OID IS NULL OR #H.Sup_OID='-2'
where H.prov_OID = @NODE
union all
select
H.Prov_OID,
H.Prov_Level,
P.LName+', '+P.FName Provider,
H.Sup_OID,
cast(cteTree.HeirarchyOrder + cast (cast(ROW_NUMBER() over (order by H.Prov_OID) AS INTEGER) as varbinary(8)) as varbinary(max) )AS HeirarchyOrder,
Level +1 as Level
--#H.Agency_Cost_Center_Helper_OID,
--#H.CCHrs
from @h H INNER JOIN Provider P ON H.Prov_OID=P.OID
join cteTree
on cteTree.Prov_OID = H.Sup_OID
)
INSERT INTO @Team
SELECT Prov_OID ,
Prov_Level,
Provider ,
Sup_OID ,
HeirarchyOrder ,
Level
FROM cteTree
--*/
RETURN
END
GO
September 9, 2011 at 1:08 pm
Are the row counts on the base tables and the temp table similar? Rowcounts can adversely affect the execution plan especially with large temptables. I have the suggestion to use the option recompile query hint when dealing with large 100k+ temp tables to help the optimizer create an execution plan that works well the size of the temp table. I have not had much practice with really large temp tables so I have not tried this approach myself. Can you post the execution plans from each of your servers? With those in hand it will be a lot easier to determine what may help your situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2011 at 1:09 pm
Tried changing the table variable to just adding another CTE to the stack. It runs but MUCH slower than my specs allow. Not sure why a CTE would be so much slower than an table variable where only around 250-300 rows are concerned.
This is fast
SET STATISTICS PROFILE ON;
DECLARE @NODE VARCHAR(32)
SET @NODE=(SELECT OID FROM PROVIDER WHERE USERNAME='test')
DECLARE @h TABLE (Prov_OID VARCHAR(32), Prov_Level FLOAT, Sup_OID Varchar(32))
INSERT @h
Select
Provider.OID Prov_OID,
MAX(CAST(SUBSTRING(L.Cad4,6,5) AS FLOAT)) Prov_Level,
MAX(CAST(USER_DEFINED_DATA.CAD4 AS Varchar(32))) Sup_OID
--CAST(Cad11 AS Varchar(32)) Agency_Cost_Center_Helper_OID,
From
PROVIDER
INNER JOIN USER_DEFINED_DATA ON PROVIDER.OID = USER_DEFINED_DATA.ATTACHED_TO_OID
LEFT JOIN dbo.USER_DEFINED_DATA L ON (CAST(USER_DEFINED_DATA.ATTACHED_TO_OID AS VARCHAR(32))) = L.ATTACHED_TO_OID
AND L.CAD1='vITX_DS_LEVEL 2009.12.10.1_1.2'
AND ( L.ExpDate IS NULL
OR L.ExpDate > GETDATE()
)
Where
--ASSESSMENT.FULLNAME = 'ITX_COSTCENTER'
USER_DEFINED_DATA.CAD1 = 'vITX_DS_TEAM_HIERARCHY_1.2'
AND (USER_DEFINED_DATA.ExpDate IS NULL OR USER_DEFINED_DATA.ExpDate>GETDATE())
GROUP BY Provider.OID
;WITH
cteTree
as
(
select --top 1
H.Prov_OID,
H.Prov_Level,
P.LName+', '+P.FName Provider,
H.Sup_OID,
cast(cast(ROW_NUMBER() over (order by H.Prov_OID) AS INTEGER) as varbinary(max)) as HeirarchyOrder,
1 as Level
from @h H INNER JOIN Provider P ON H.Prov_OID=P.OID
--where #H.Sup_OID IS NULL OR #H.Sup_OID='-2'
where H.prov_OID = @NODE
union all
select
H.Prov_OID,
H.Prov_Level,
P.LName+', '+P.FName Provider,
H.Sup_OID,
cast(cteTree.HeirarchyOrder + cast (cast(ROW_NUMBER() over (order by H.Prov_OID) AS INTEGER) as varbinary(8)) as varbinary(max) )AS HeirarchyOrder,
Level +1 as Level
--#H.Agency_Cost_Center_Helper_OID,
--#H.CCHrs
from @h H INNER JOIN Provider P ON H.Prov_OID=P.OID
join cteTree
on cteTree.Prov_OID = H.Sup_OID
)
SELECT * FROM cteTree
SQL Server parse and compile time:
CPU time = 31 ms, elapsed time = 43 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Provider'. Scan count 1, logical reads 156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(7 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
Table '#55E07C01'. Scan count 0, logical reads 253, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'USER_DEFINED_DATA'. Scan count 2222, logical reads 8282, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Provider'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(251 row(s) affected)
(18 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 102 ms.
(120 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 721, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Provider'. Scan count 0, logical reads 360, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#55E07C01'. Scan count 121, logical reads 363, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(23 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 191 ms.
While this is slow
SET STATISTICS PROFILE ON;
DECLARE @NODE VARCHAR(32)
SET @NODE=(SELECT OID FROM PROVIDER WHERE USERNAME='test')
;WITH
H
AS
(
Select
Provider.OID Prov_OID,
MAX(CAST(SUBSTRING(L.Cad4,6,5) AS FLOAT)) Prov_Level,
MAX(CAST(USER_DEFINED_DATA.CAD4 AS Varchar(32))) Sup_OID
--CAST(Cad11 AS Varchar(32)) Agency_Cost_Center_Helper_OID,
From
PROVIDER
INNER JOIN USER_DEFINED_DATA ON PROVIDER.OID = USER_DEFINED_DATA.ATTACHED_TO_OID
LEFT JOIN dbo.USER_DEFINED_DATA L ON (CAST(USER_DEFINED_DATA.ATTACHED_TO_OID AS VARCHAR(32))) = L.ATTACHED_TO_OID
AND L.CAD1='vITX_DS_LEVEL 2009.12.10.1_1.2'
AND ( L.ExpDate IS NULL
OR L.ExpDate > GETDATE()
)
Where
--ASSESSMENT.FULLNAME = 'ITX_COSTCENTER'
USER_DEFINED_DATA.CAD1 = 'vITX_DS_TEAM_HIERARCHY_1.2'
AND (USER_DEFINED_DATA.ExpDate IS NULL OR USER_DEFINED_DATA.ExpDate>GETDATE())
GROUP BY Provider.OID
)
,
cteTree
as
(
select --top 1
H.Prov_OID,
H.Prov_Level,
P.LName+', '+P.FName Provider,
H.Sup_OID,
cast(cast(ROW_NUMBER() over (order by H.Prov_OID) AS INTEGER) as varbinary(max)) as HeirarchyOrder,
1 as Level
from H INNER JOIN Provider P ON H.Prov_OID=P.OID
--where #H.Sup_OID IS NULL OR #H.Sup_OID='-2'
where H.prov_OID = @NODE
union all
select
H.Prov_OID,
H.Prov_Level,
P.LName+', '+P.FName Provider,
H.Sup_OID,
cast(cteTree.HeirarchyOrder + cast (cast(ROW_NUMBER() over (order by H.Prov_OID) AS INTEGER) as varbinary(8)) as varbinary(max) )AS HeirarchyOrder,
Level +1 as Level
--#H.Agency_Cost_Center_Helper_OID,
--#H.CCHrs
from H H INNER JOIN Provider P ON H.Prov_OID=P.OID
join cteTree
on cteTree.Prov_OID = H.Sup_OID
)
SELECT * FROM cteTree
SQL Server parse and compile time:
CPU time = 94 ms, elapsed time = 107 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Provider'. Scan count 1, logical reads 156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(7 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 4 ms.
(120 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 721, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'USER_DEFINED_DATA'. Scan count 30242, logical reads 6338805, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Provider'. Scan count 240, logical reads 21981, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(47 row(s) affected)
SQL Server Execution Times:
CPU time = 23821 ms, elapsed time = 24323 ms.
September 9, 2011 at 1:13 pm
Sean Lange (9/9/2011)
Are the row counts on the base tables and the temp table similar? Rowcounts can adversely affect the execution plan especially with large temptables. I have the suggestion to use the option recompile query hint when dealing with large 100k+ temp tables to help the optimizer create an execution plan that works well the size of the temp table. I have not had much practice with really large temp tables so I have not tried this approach myself. Can you post the execution plans from each of your servers? With those in hand it will be a lot easier to determine what may help your situation.
Tables are only 250-300 rows. Temp tables would solve the problem if they were usable inside of a table valued function, alas....
September 9, 2011 at 1:31 pm
Tried changing "H" to a subquery inside the recursive CTE but aggregates and even left outers are not allowed. Its ugly, but it buys me time to find a real solution.
September 9, 2011 at 2:08 pm
The query still isn't returning on the 2008 box. It still has to do an insert into the result table so it has the same problem.
I'm stuck.
September 9, 2011 at 2:20 pm
Meaning it is taking a LONG time?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2011 at 2:26 pm
Try this on a TEST server. The code below has the recompile hints. It might help the optimizer with a better execution plan. I make no promise as to the effectiveness of this approach as I have not myself had to use this. Again do this on a test server first.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ITX_GETALLSUBS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [ITX_GETALLSUBS]
GO
CREATE FUNCTION [dbo].[ITX_GETALLSUBS](@NODE VARCHAR(32))
RETURNS @Team TABLE (Prov_OID Varchar(32), Prov_Level FLOAT, Provider Varchar(32), Sup_OID Varchar(32), HeirarchyOrder VarBinary(MAX), Level INT)
AS
BEGIN
DECLARE @P TABLE (Prov_OID VARCHAR(32), Prov_Level FLOAT, Sup_OID Varchar(32))
INSERT @P
Select
Provider.OID Prov_OID,
MAX(CAST(SUBSTRING(L.Cad4,6,5) AS FLOAT)) Prov_Level,
MAX(CAST(USER_DEFINED_DATA.CAD4 AS Varchar(32))) Sup_OID
--CAST(Cad11 AS Varchar(32)) Agency_Cost_Center_Helper_OID,
From
PROVIDER
INNER JOIN USER_DEFINED_DATA ON PROVIDER.OID = USER_DEFINED_DATA.ATTACHED_TO_OID
LEFT JOIN dbo.USER_DEFINED_DATA L ON (CAST(USER_DEFINED_DATA.ATTACHED_TO_OID AS VARCHAR(32))) = L.ATTACHED_TO_OID
AND L.CAD1='vITX_DS_LEVEL 2009.12.10.1_1.2'
AND ( L.ExpDate IS NULL
OR L.ExpDate > GETDATE()
)
Where
--ASSESSMENT.FULLNAME = 'ITX_COSTCENTER'
USER_DEFINED_DATA.CAD1 = 'vITX_DS_TEAM_HIERARCHY_1.2'
AND (USER_DEFINED_DATA.ExpDate IS NULL OR USER_DEFINED_DATA.ExpDate>GETDATE())
GROUP BY Provider.OID
OPTION (RECOMPILE)
DECLARE @h TABLE (Prov_OID VARCHAR(32), Prov_Level Float, Sup_OID VARCHAR(32))--, ACCH_OID VARCHAR(32))
INSERT @h
Select Prov_OID, MIN(Prov_Level) , Sup_OID From @P Group By Prov_OID, Sup_OID
;WITH
cteTree
as
(
select --top 1
H.Prov_OID,
H.Prov_Level,
P.LName+', '+P.FName Provider,
H.Sup_OID,
cast(cast(ROW_NUMBER() over (order by H.Prov_OID) AS INTEGER) as varbinary(max)) as HeirarchyOrder,
1 as Level
from @h H INNER JOIN Provider P ON H.Prov_OID=P.OID
--where #H.Sup_OID IS NULL OR #H.Sup_OID='-2'
where H.prov_OID = @NODE
union all
select
H.Prov_OID,
H.Prov_Level,
P.LName+', '+P.FName Provider,
H.Sup_OID,
cast(cteTree.HeirarchyOrder + cast (cast(ROW_NUMBER() over (order by H.Prov_OID) AS INTEGER) as varbinary(8)) as varbinary(max) )AS HeirarchyOrder,
Level +1 as Level
--#H.Agency_Cost_Center_Helper_OID,
--#H.CCHrs
from @h H INNER JOIN Provider P ON H.Prov_OID=P.OID
join cteTree
on cteTree.Prov_OID = H.Sup_OID
)
INSERT INTO @Team
SELECT Prov_OID ,
Prov_Level,
Provider ,
Sup_OID ,
HeirarchyOrder ,
Level
FROM cteTree
OPTION (RECOMPILE)
--*/
RETURN
END
GO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2011 at 4:02 pm
Thanks, still no dice.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply