December 19, 2008 at 3:30 am
I have a Multi line TVF (Takes 6 parameters ) which executes much slower then if i pull out the the body TSQL and execute it with hardcoded parameters. (10 seconds Vs ober a minute)
I thought it might be parameter sniffing but even hard coding the paramters in the function makes no difference . I cant see the actual execution plan of the TVF as all it shows me is 100% cost of the TVF not the code executing inside.
I am so lost , any insight into this anomoly would be gratefully appreciated
December 19, 2008 at 3:39 am
Can you post the code for the function?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 19, 2008 at 3:44 am
Code without function wrapper (Much faster)
Declare @HierachyReturns TABLE (
[DataSourceId] Int,
[FundId] Int,
[EffectiveDate] DateTime,
[Rank] INT,
[PathKey] VARCHAR(100) ,
[ParentKey] VARCHAR(100) ,
[CategoryId]INT,
[HrchCatName]VARCHAR(100),
[MV]FLOAT,
[MVParent] FLOAT,
[CategoryWeight] FLOAT,
[CategoryWeightReturn] FLOAT,
[CategoryReturn] FLOAT,
[ParentCategoryReturn] FLOAT,
[PortfolioRelReturn] FLOAT,
[IDXReturn]Float
PRIMARY KEY (DataSourceId,FundId,EffectiveDate,PathKey)
)
DECLARE @FundID INT
DECLARE @CNT as INT
DECLARE @MAXCNT as INT
DECLARE @ExtManPathKeyVarchar(1000)
DECLARE @RankFromNewParent Int
DECLARE @CurrentKeyVarchar(100)
DECLARE @CategoryWeightReturn Float
DECLARE @ParentFundId Int
DECLARE @TMV FLOAT
DECLARE @PrevDate DATETIME
DECLARE @CurrDate DateTime
DECLARE @LOOPCNT INT
DECLARE @TOTROWS INT
DECLARE @DataSourceID INT
DECLARE @StartDateDateTime
DECLARE@EndDateDateTime
DECLARE @FundCode Varchar(100)
DECLARE @ReturnValueType Int
DECLARE @SchemaIdInt
SET @ReturnValueType = 1
Set @StartDate = '27 oct 2008'
Set @EndDate = '31 oct 2008'
SET @SchemaId = 31
SET @ParentFundId = 2592
SET @FundCode = 'GEPF COMPOSITE'
SET @DataSourceID =1
/*
DECLARE @DataSourceID INT
Declare @HierachyReturns TABLE (
[FundId] Int,
[EffectiveDate] DateTime,
[Rank] INT,
[PathKey] VARCHAR(100) ,
[ParentKey] VARCHAR(100) ,
[CategoryId]INT,
[HrchCatName]VARCHAR(100),
[MV]FLOAT,
[MVParent] FLOAT,
[CategoryWeight] FLOAT,
[CategoryWeightReturn] FLOAT,
[CategoryReturn] FLOAT,
[ParentCategoryReturn] FLOAT,
[PortfolioRelReturn] FLOAT,
PRIMARY KEY (EffectiveDate,PathKey,ParentKey)
--[PortfolioReturn] FLOAT
)
DECLARE @StartDateDateTime
DECLARE@EndDateDateTime
DECLARE @FundCode Varchar(100)
DECLARE @ReturnValueType Int
DECLARE @SchemaIdInt
SET @ReturnValueType = 1
Set @StartDate = '31 Oct 2008'
Set @EndDate = '31 Oct 2008'
SET @SchemaId = 31
SET @ParentFundId = 2592
*/
Declare @ChildFunds Table
([Indx]int identity(1,1),
[SchemaId] [int] NOT NULL,
[ParentFundId] [int] NOT NULL,
[ChildFundId] [int] NOT NULL,
[HierachyMergeNode] [varchar](50) NOT NULL
PRIMARY KEY (ChildFundId)
)
Declare @DatesForCompound Table
(IDInt Identity(1,1),
EffectiveDate DateTime,
Primary Key(EffectiveDate)
)
--Darron come back to this to , will not work for rischedge
IF @DataSourceId = 1
BEGIN
Select @ParentFundId = FundId from riscsql01.reporting.dbo.fund where FundCode=@FundCode
END
ELSE
BEGIN
Select @ParentFundId = FundId from riscsql01.RiscHedge.dbo.fund where FundCode=@FundCode
END
--Get list of funds to loop through
INSERT INTO @ChildFunds
Select * from dbo.HrchFoFMapping where SchemaId = @SchemaId and ParentFundId = @ParentFundId
-- Loop Through
SET @CNT = 1
SELECT @MAXCNT = MAX(Indx) from @ChildFunds
WHILE @CNT <= @MAXCNT
BEGIN
--SET Current Variables
SELECT @FundCode =Fundcode,
@FundId = ChildFundid,
@ExtManPathKey =HierachyMergeNode,
@RankFromNewParent= dbo.fnGetHierachyDepthByPathKey(HierachyMergeNode)-1
FROM RISCSQL01.REPORTING.DBO.FUND FND
INNER JOIN @ChildFunds CHLD ON
FND.Fundid = CHLD.ChildFundId
WHERE Indx = @CNT
-- GetValues From Function with appended PathKey
Insert Into @HierachyReturns
--CASE manipulation to remove redundant portfolio level before manager level
select
@DataSourceId,
FundId,
EffectiveDate,
@RankFromNewParent + Rank as Rank,
CASEWHEN CategoryId = 1
THEN @ExtManPathKey
ELSE REPLACE(@ExtManPathKey + '|' + pathkey,'|1|','|')
END as PathKey,
CASEWHEN CategoryId = 1
THEN dbo.fnGetPathKeyParent(@ExtManPathKey)
WHEN ParentKey='1'
THEN @ExtManPathKey
ELSE REPLACE(@ExtManPathKey + '|' + ParentKey,'|1|','|')
END as ParentKey,
CategoryId,
CASEWHEN Categoryid =1
THEN @FundCode
ELSE HrchCatName
END as HrchCatName,
MV,
MVParent,
CategoryWeight,
CategoryWeightReturn,
CategoryReturn,
ParentCategoryReturn,
PortfolioRelReturn,
IDXReturn
from HrchFundsStagedData --fnmGetCategoryReturnByFund_NEWDEV_V3(@SchemaId,@DataSourceId,@FundCode,@StartDate,@EndDate,@ReturnValueType)
Where
SchemaId = @SchemaId
And
DataSourceId = @DataSourceId
And
FundId = @FundId
And
EffectiveDate between @StartDate and @EndDate
OPTION(RECOMPILE);
--OPTION(OPTIMIZE FOR (@StartDate = '20081001',@EndDate='20081031',@DataSourceId=1,@SchemaId=1));
--Increment Counter
SET @CNT = @CNT + 1
END
--Get top level hierachy structure --Exploded
INSERT INTO @HierachyReturns
SELECT@DataSourceId,
0, --Fund no longer specific
EffectiveDate ,
dbo.fnGetHierachyDepthByPathKey(PathKey) as Rank,
PathKey,
dbo.fnGetPathKeyParent(PathKey) as ParentKey,
dbo.fnGetCatIdfromPathKey(PathKey) as CategoryId,
dbo.fnGetCatgegoryFromID(dbo.fnGetCatIdfromPathKey(PathKey)) as HrchCatName,
0,
0,
0,
0,
0,
0,
0,
0
From
(SELECT DISTINCT
DelimItem as PathKey,
EffectiveDate
from @HierachyReturns Hrch Cross apply
dbo.fncDelimitedStringtoAccumKeyTableVariable(Pathkey)
where Rank=4) Wrp
Where dbo.fnGetHierachyDepthByPathKey(PathKey)<4 ---Level 4 is manager level , already present
OPTION(RECOMPILE);
--OPTION(OPTIMIZE FOR (@DataSourceId=1));
----Roll Forward and create top Hierachy
----Reset @CNT and @MaxCnt
SET @CNT = 4
WHILE @CNT >= 1 --Step Back through hierachy
BEGIN
Update @HierachyReturns set MV = SM.Total
FROM
@HierachyReturns HRCH INNER JOIN
(SelectEffectiveDate,
ParentKey ,
SUM(MV) As Total
FROM
@HierachyReturns
where
Rank=@CNT
group by effectiveDate,ParentKey
) SM
ON
HRCH.EffectiveDate = SM.EffectiveDate
AND
HRCH.PathKey = SM.ParentKey
--Set MV of Child Records and category Weighting and category weight return
Update @HierachyReturns set MVParent = SM.Total,CategoryWeight=MV/SM.Total
FROM
@HierachyReturns HRCH INNER JOIN
(SelectEffectiveDate,
ParentKey ,
SUM(MV) As Total
FROM
@HierachyReturns
where
Rank=@CNT
group by effectiveDate,ParentKey
) SM
ON
HRCH.EffectiveDate = SM.EffectiveDate
AND
HRCH.ParentKey= SM.ParentKey
Where
-- MV<>0
SM.Total<>0
--Update Category WeightedReturns
Update @HierachyReturns set CategoryWeightReturn=CategoryReturn * CategoryWeight
Where Rank=@CNT
----Update Category Returns
Update @HierachyReturns set CategoryReturn = SM.Total
FROM
@HierachyReturns HRCH INNER JOIN
(SelectEffectiveDate,
ParentKey ,
SUM(CategoryWeightReturn) As Total
FROM
@HierachyReturns
where
Rank=@CNT
group by effectiveDate,ParentKey
) SM
ON
HRCH.EffectiveDate = SM.EffectiveDate
AND
HRCH.Pathkey= SM.ParentKey
----Update Parent Category Returns
Update @HierachyReturns set ParentCategoryReturn = SM.CategoryReturn
FROM
@HierachyReturns HRCH INNER JOIN
(SelectEffectiveDate,
PathKey ,
CategoryReturn
FROM
@HierachyReturns
where
Rank=@CNT
--group by effectiveDate,ParentKey
) SM
ON
HRCH.EffectiveDate = SM.EffectiveDate
AND
HRCH.ParentKey= SM.PathKey
--Update Category WeightedReturns
Update @HierachyReturns set PortfolioRelReturn=CategoryWeightReturn*(MVparent/Tmv)
FROM
@HierachyReturns HRCH Left Outer Join
(Select EffectiveDate,SUm(MV) as TMV
from @HierachyReturns
where Rank=4 --Node join point rank
Group By EffectiveDate) TMV ON
TMV.EffectiveDate = HRCH.EffectiveDate
Where Rank=@CNT
OPTION(RECOMPILE);
--OPTION(RECOMPILE);
----Decrement Counter
SET @CNT = @CNT -1
END
--Loop Through Days to update compound index
Insert into @DatesForCompound
Select Distinct EffectiveDate from @HierachyReturns
SELECT @TOTROWS=MAX(ID) FROM @DatesForCompound
--Set the Index for the first day , using base index value of 100
Update @HierachyReturns set IDXReturn = 100 * (1 + isnull(HR.CategoryReturn,0))
From
@HierachyReturns HR
--Darron 17-12-2008 :Cash instruments coming back with zero holding causing problems
Inner Join
(Select Distinct Pathkey,min(EffectiveDate)as EffectiveDate from @HierachyReturns group by pathkey ) MNDT ON
MNDT.EffectiveDate = HR.EffectiveDate
And
MNDT.PathKey = HR.PathKey
Where
MV <>0
OPTION(RECOMPILE);
--Update @HierachyReturns set IDXReturn = 100 * (1 + CategoryReturn)
--where EffectiveDate =(Select min(EffectiveDate) from @DatesForCompound)
----And Rank <=4
--Initialise @loopCnt
SET @LoopCnt=1
WHILE @LoopCnt < @TOTROWS
BEGIN
--SetDates for compond
SELECT @PrevDate = EffectiveDate FROM @DatesForCompound WHERE ID=@LoopCnt
SELECT @CurrDate = EffectiveDate FROM @DatesForCompound WHERE ID=@LoopCnt +1
--Update Index
Update @HierachyReturns set IDXReturn = PREV.IDXReturn * (1 + CategoryReturn)
FROM @HierachyReturns HR Inner Join
(Select EffectiveDate,
PathKey,
IDXReturn
From @HierachyReturns
Where EffectiveDate = @PrevDate) PREV ON
PREV.PathKey=HR.PathKey
Where HR.EffectiveDate = @Currdate
OPTION(RECOMPILE);
--and
--rank <=4
--Increment loop
Set @LoopCnt = @LOOPCNT + 1
END
--RETURN
--END
___**************************************************************************
Function (Same code wrapped in function - much slower)
ALTER FUNCTION [dbo].[fnmGetCategoryReturnByCompositeFund]
(
@SchemaIdInt,
@DataSourceId INT,
@FundCode varchar(30),
@StartDate DateTime,
@EndDate DateTime ,
@ReturnValueTypeINT
)
RETURNS
@HierachyReturns TABLE (
[DataSourceId] Int,
[FundId] Int,
[EffectiveDate] DateTime,
[Rank] INT,
[PathKey] VARCHAR(100) ,
[ParentKey] VARCHAR(100) ,
[CategoryId]INT,
[HrchCatName]VARCHAR(100),
[MV]FLOAT,
[MVParent] FLOAT,
[CategoryWeight] FLOAT,
[CategoryWeightReturn] FLOAT,
[CategoryReturn] FLOAT,
[ParentCategoryReturn] FLOAT,
[PortfolioRelReturn] FLOAT,
[IDXReturn]Float
PRIMARY KEY (DataSourceId,FundId,EffectiveDate,PathKey)
) --with Recompile
AS
BEGIN
DECLARE @FundID INT
DECLARE @CNT as INT
DECLARE @MAXCNT as INT
DECLARE @ExtManPathKeyVarchar(1000)
DECLARE @RankFromNewParent Int
DECLARE @CurrentKeyVarchar(100)
DECLARE @CategoryWeightReturn Float
DECLARE @ParentFundId Int
DECLARE @TMV FLOAT
DECLARE @PrevDate DATETIME
DECLARE @CurrDate DateTime
DECLARE @LOOPCNT INT
DECLARE @TOTROWS INT
/*
DECLARE @DataSourceID INT
Declare @HierachyReturns TABLE (
[FundId] Int,
[EffectiveDate] DateTime,
[Rank] INT,
[PathKey] VARCHAR(100) ,
[ParentKey] VARCHAR(100) ,
[CategoryId]INT,
[HrchCatName]VARCHAR(100),
[MV]FLOAT,
[MVParent] FLOAT,
[CategoryWeight] FLOAT,
[CategoryWeightReturn] FLOAT,
[CategoryReturn] FLOAT,
[ParentCategoryReturn] FLOAT,
[PortfolioRelReturn] FLOAT,
PRIMARY KEY (EffectiveDate,PathKey,ParentKey)
--[PortfolioReturn] FLOAT
)
DECLARE @StartDateDateTime
DECLARE@EndDateDateTime
DECLARE @FundCode Varchar(100)
DECLARE @ReturnValueType Int
DECLARE @SchemaIdInt
SET @ReturnValueType = 1
Set @StartDate = '31 Oct 2008'
Set @EndDate = '31 Oct 2008'
SET @SchemaId = 31
SET @ParentFundId = 2592
*/
Declare @ChildFunds Table
([Indx]int identity(1,1),
[SchemaId] [int] NOT NULL,
[ParentFundId] [int] NOT NULL,
[ChildFundId] [int] NOT NULL,
[HierachyMergeNode] [varchar](50) NOT NULL
PRIMARY KEY (ChildFundId)
)
Declare @DatesForCompound Table
(IDInt Identity(1,1),
EffectiveDate DateTime,
Primary Key(EffectiveDate)
)
--Darron come back to this to , will not work for rischedge
IF @DataSourceId = 1
BEGIN
Select @ParentFundId = FundId from riscsql01.reporting.dbo.fund where FundCode=@FundCode
END
ELSE
BEGIN
Select @ParentFundId = FundId from riscsql01.RiscHedge.dbo.fund where FundCode=@FundCode
END
--Get list of funds to loop through
INSERT INTO @ChildFunds
Select * from dbo.HrchFoFMapping where SchemaId = @SchemaId and ParentFundId = @ParentFundId
-- Loop Through
SET @CNT = 1
SELECT @MAXCNT = MAX(Indx) from @ChildFunds
WHILE @CNT <= @MAXCNT
BEGIN
--SET Current Variables
SELECT @FundCode =Fundcode,
@FundId = ChildFundid,
@ExtManPathKey =HierachyMergeNode,
@RankFromNewParent= dbo.fnGetHierachyDepthByPathKey(HierachyMergeNode)-1
FROM RISCSQL01.REPORTING.DBO.FUND FND
INNER JOIN @ChildFunds CHLD ON
FND.Fundid = CHLD.ChildFundId
WHERE Indx = @CNT
-- GetValues From Function with appended PathKey
Insert Into @HierachyReturns
--CASE manipulation to remove redundant portfolio level before manager level
select
@DataSourceId,
FundId,
EffectiveDate,
@RankFromNewParent + Rank as Rank,
CASEWHEN CategoryId = 1
THEN @ExtManPathKey
ELSE REPLACE(@ExtManPathKey + '|' + pathkey,'|1|','|')
END as PathKey,
CASEWHEN CategoryId = 1
THEN dbo.fnGetPathKeyParent(@ExtManPathKey)
WHEN ParentKey='1'
THEN @ExtManPathKey
ELSE REPLACE(@ExtManPathKey + '|' + ParentKey,'|1|','|')
END as ParentKey,
CategoryId,
CASEWHEN Categoryid =1
THEN @FundCode
ELSE HrchCatName
END as HrchCatName,
MV,
MVParent,
CategoryWeight,
CategoryWeightReturn,
CategoryReturn,
ParentCategoryReturn,
PortfolioRelReturn,
IDXReturn
from HrchFundsStagedData --fnmGetCategoryReturnByFund_NEWDEV_V3(@SchemaId,@DataSourceId,@FundCode,@StartDate,@EndDate,@ReturnValueType)
Where
SchemaId = @SchemaId
And
DataSourceId = @DataSourceId
And
FundId = @FundId
And
EffectiveDate between @StartDate and @EndDate
OPTION(RECOMPILE);
--Increment Counter
SET @CNT = @CNT + 1
END
--Get top level hierachy structure --Exploded
INSERT INTO @HierachyReturns
SELECT@DataSourceId,
0, --Fund no longer specific
EffectiveDate ,
dbo.fnGetHierachyDepthByPathKey(PathKey) as Rank,
PathKey,
dbo.fnGetPathKeyParent(PathKey) as ParentKey,
dbo.fnGetCatIdfromPathKey(PathKey) as CategoryId,
dbo.fnGetCatgegoryFromID(dbo.fnGetCatIdfromPathKey(PathKey)) as HrchCatName,
0,
0,
0,
0,
0,
0,
0,
0
From
(SELECT DISTINCT
DelimItem as PathKey,
EffectiveDate
from @HierachyReturns Hrch Cross apply
dbo.fncDelimitedStringtoAccumKeyTableVariable(Pathkey)
where Rank=4) Wrp
Where dbo.fnGetHierachyDepthByPathKey(PathKey)<4 ---Level 4 is manager level , already present
OPTION(RECOMPILE);
----Roll Forward and create top Hierachy
----Reset @CNT and @MaxCnt
SET @CNT = 4
WHILE @CNT >= 1 --Step Back through hierachy
BEGIN
Update @HierachyReturns set MV = SM.Total
FROM
@HierachyReturns HRCH INNER JOIN
(SelectEffectiveDate,
ParentKey ,
SUM(MV) As Total
FROM
@HierachyReturns
where
Rank=@CNT
group by effectiveDate,ParentKey
) SM
ON
HRCH.EffectiveDate = SM.EffectiveDate
AND
HRCH.PathKey = SM.ParentKey
--Set MV of Child Records and category Weighting and category weight return
Update @HierachyReturns set MVParent = SM.Total,CategoryWeight=MV/SM.Total
FROM
@HierachyReturns HRCH INNER JOIN
(SelectEffectiveDate,
ParentKey ,
SUM(MV) As Total
FROM
@HierachyReturns
where
Rank=@CNT
group by effectiveDate,ParentKey
) SM
ON
HRCH.EffectiveDate = SM.EffectiveDate
AND
HRCH.ParentKey= SM.ParentKey
Where
-- MV<>0
SM.Total<>0
--Update Category WeightedReturns
Update @HierachyReturns set CategoryWeightReturn=CategoryReturn * CategoryWeight
Where Rank=@CNT
----Update Category Returns
Update @HierachyReturns set CategoryReturn = SM.Total
FROM
@HierachyReturns HRCH INNER JOIN
(SelectEffectiveDate,
ParentKey ,
SUM(CategoryWeightReturn) As Total
FROM
@HierachyReturns
where
Rank=@CNT
group by effectiveDate,ParentKey
) SM
ON
HRCH.EffectiveDate = SM.EffectiveDate
AND
HRCH.Pathkey= SM.ParentKey
----Update Parent Category Returns
Update @HierachyReturns set ParentCategoryReturn = SM.CategoryReturn
FROM
@HierachyReturns HRCH INNER JOIN
(SelectEffectiveDate,
PathKey ,
CategoryReturn
FROM
@HierachyReturns
where
Rank=@CNT
--group by effectiveDate,ParentKey
) SM
ON
HRCH.EffectiveDate = SM.EffectiveDate
AND
HRCH.ParentKey= SM.PathKey
--Update Category WeightedReturns
Update @HierachyReturns set PortfolioRelReturn=CategoryWeightReturn*(MVparent/Tmv)
FROM
@HierachyReturns HRCH Left Outer Join
(Select EffectiveDate,SUm(MV) as TMV
from @HierachyReturns
where Rank=4 --Node join point rank
Group By EffectiveDate) TMV ON
TMV.EffectiveDate = HRCH.EffectiveDate
Where Rank=@CNT
OPTION(RECOMPILE);
--OPTION(RECOMPILE);
----Decrement Counter
SET @CNT = @CNT -1
END
--Loop Through Days to update compound index
Insert into @DatesForCompound
Select Distinct EffectiveDate from @HierachyReturns
SELECT @TOTROWS=MAX(ID) FROM @DatesForCompound
--Set the Index for the first day , using base index value of 100
Update @HierachyReturns set IDXReturn = 100 * (1 + isnull(HR.CategoryReturn,0))
From
@HierachyReturns HR
--Darron 17-12-2008 :Cash instruments coming back with zero holding causing problems
Inner Join
(Select Distinct Pathkey,min(EffectiveDate)as EffectiveDate from @HierachyReturns group by pathkey ) MNDT ON
MNDT.EffectiveDate = HR.EffectiveDate
And
MNDT.PathKey = HR.PathKey
Where
MV <>0
OPTION(RECOMPILE);
--Update @HierachyReturns set IDXReturn = 100 * (1 + CategoryReturn)
--where EffectiveDate =(Select min(EffectiveDate) from @DatesForCompound)
----And Rank <=4
--Initialise @loopCnt
SET @LoopCnt=1
WHILE @LoopCnt < @TOTROWS
BEGIN
--SetDates for compond
SELECT @PrevDate = EffectiveDate FROM @DatesForCompound WHERE ID=@LoopCnt
SELECT @CurrDate = EffectiveDate FROM @DatesForCompound WHERE ID=@LoopCnt +1
--Update Index
Update @HierachyReturns set IDXReturn = PREV.IDXReturn * (1 + CategoryReturn)
FROM @HierachyReturns HR Inner Join
(Select EffectiveDate,
PathKey,
IDXReturn
From @HierachyReturns
Where EffectiveDate = @PrevDate) PREV ON
PREV.PathKey=HR.PathKey
Where HR.EffectiveDate = @Currdate
OPTION(RECOMPILE);
--Increment loop
Set @LoopCnt = @LOOPCNT + 1
END
Delete from @HierachyReturns
RETURN
END
December 19, 2008 at 5:18 am
Why don't you rewrite algorithm to use recursive cte instead?
N 56°04'39.16"
E 12°55'05.25"
December 19, 2008 at 5:23 am
Hi
I can see that it would be difficult to assess my problem from the chunk of code i placed down. But i can assure you the only difference is the function wrapper i place around the code.
So i guess the question is what optimisation issues does sql have when code is run in a multi line tvf as opposed to straight tsql
December 19, 2008 at 5:45 am
2 things : 1 the optimizer must assume that the parameters will change. Maybe it tries to figure out the best plan for the normal or worst case scenarios... choosing to do scans instead of seeks + lookups which would be far more costly to run with lots of data.
2 - Did you empty the proc cache instead of just using the local variable thing? Maybe the compiled plan still gets used instead of being remade.
December 19, 2008 at 5:49 am
ddorfman (12/19/2008)
HiI can see that it would be difficult to assess my problem from the chunk of code i placed down. But i can assure you the only difference is the function wrapper i place around the code.
So i guess the question is what optimisation issues does sql have when code is run in a multi line tvf as opposed to straight tsql
It's a longshot, but have you tried putting this little lot into a sproc and running the results into a prepared table?
CREATE TABLE...
INSERT INTO ... EXEC ...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 19, 2008 at 6:25 am
Multi-line table valued functions are notorious performance pigs. They have not statistics so anything more than a few rows will perform pretty poorly. For more information, check out Gail's blog: SQL In The Wild[/url].
I've also posted a little on the same topic: Scary DBA[/url]
There are any number of other alternatives. If you really absolutely have to perform this type of function on the database, you might consider going to CLR. Andy Novick (who wrote a book on UDF's) suggested it to me as a good alternative[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2008 at 6:59 am
Chris Morris (12/19/2008)
It's a longshot, but have you tried putting this little lot into a sproc and running the results into a prepared table?CREATE TABLE...
INSERT INTO ... EXEC ...
That's very likely to run significantly better than the function as written.
It can be a temp table, doesn't need to be permanent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 7:07 am
Thanks
How to i get the actual execution plan of running just a TVF , all i get back now is the TVF as 100%of the cost of the batch , would like to see whats going on during the execution so i can pinpoint what is making it so slow.
December 19, 2008 at 7:14 am
ddorfman (12/19/2008)
ThanksHow to i get the actual execution plan of running just a TVF
You don't. 🙁
Take the contents of the function, put in management studio, declare variables in place of the parameters and debug there. Functions are a complete 'black box' in terms of performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 7:16 am
Not even with SQL Profiler?
You can get every statement within the function there.
N 56°04'39.16"
E 12°55'05.25"
December 19, 2008 at 7:17 am
Another reason I dislike the things.
Single line table valued UDF's do resolve out more like views and are part of the execution plan, for what it's worth.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2008 at 7:35 am
Peso (12/19/2008)
Not even with SQL Profiler?You can get every statement within the function there.
True, though that's a hellishly painful way to get execution plans.
I need to check and see if there's a connect on this yet, if not submit one. There needs to be a way to see inside these things from management studio.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 7:57 am
Once again thanks for all your help on this ,
I have used profiler to with showplan_xml to get the execution plan but there seems to be a limitation on viewing the number of execution plans from a batch and i can never get to the plans that are causing the problem .
I need to see it as a single batch as in need to see the overall cost of the executions.
I think im heading towards an stored procedure execute.
Will only be back on line on monday morning , would really appreaciate whatever other input you can give me.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply