AkAlan
asked on
Can I reuse UDF returned value in a SQL Stored Procedure within the same Stored Procedure ?
I have a sproc for returning LaborHours that has a User Defined Function for a row source which returns a sum of Fringe costs, usually 4 fringe rows for every LaborHours row in sproc. I want to then add the LaborHours with the sum of the Fringe costs as another column but I have to rerun the Function and it makes the query time go from 7 seconds to more than twice that. Seems to me if I already calculated the Fringe costs I could store them in a variable and then add the variable to the LaborHours for a total column.
Here is an excerpt from my sproc:
SELECT
hs.TS_LN_KEY
hs.LAB_CST_AMT AS TotalLaborCost,
dbo.fn_Eng_OaFringeTotals( hs.TS_LN_K EY) AS TotalFringeCosts, --This returns the sum of fringe costs,
dbo.fn_Eng_OaFringeTotals( hs.TS_LN_K EY) + hs.LAB_CST_AMT AS TotalCosts, --have to rerun the function and add it to the laborcost
FROM ....etc
Is ther a way to assign the fringe costs to a variable and reuse it?
Here is an excerpt from my sproc:
SELECT
hs.TS_LN_KEY
hs.LAB_CST_AMT AS TotalLaborCost,
dbo.fn_Eng_OaFringeTotals(
dbo.fn_Eng_OaFringeTotals(
FROM ....etc
Is ther a way to assign the fringe costs to a variable and reuse it?
Hi,
What I suggest is that, you can use temporary table.
like
SELECT
hs.TS_LN_KEY
hs.LAB_CST_AMT AS TotalLaborCost,
dbo.fn_Eng_OaFringeTotals(
dbo.fn_Eng_OaFringeTotals(
INTO #TempTable
FROM ....etc
If above query is inside procedure then you no need to drop that table explicitly.
else you can use
DROP TABLE #TempTable
- Bhavesh
ASKER
Joe, that looks promising, I'll try it out later today.
Brichsoft, I don't see how your solution keeps from running the same function twice.
Brichsoft, I don't see how your solution keeps from running the same function twice.
Hi,
See.I'm assuming, you calling one function twice and that you wanted to avoid..
If so then.....
This will be your 1st Query.
SELECT
hs.TS_LN_KEY,
hs.LAB_CST_AMT AS TotalLaborCost,
dbo.fn_Eng_OaFringeTotals( hs.TS_LN_K EY) AS TotalFringeCosts, --This returns the sum of fringe costs,
Other columns
into #Temp
FROM ....etc
This will be your 2nd Query.
SELECT TS_LN_KEY, TotalLaborCost, (TotalLaborCost + TotalFringeCosts) TotalCosts
FROM #Temp
- Bhavesh
See.I'm assuming, you calling one function twice and that you wanted to avoid..
If so then.....
This will be your 1st Query.
SELECT
hs.TS_LN_KEY,
hs.LAB_CST_AMT AS TotalLaborCost,
dbo.fn_Eng_OaFringeTotals(
Other columns
into #Temp
FROM ....etc
This will be your 2nd Query.
SELECT TS_LN_KEY, TotalLaborCost, (TotalLaborCost + TotalFringeCosts) TotalCosts
FROM #Temp
- Bhavesh
ASKER
Bhavesh, thanks for clearing that up. I still have not been able to play with either soultion, I will soon and see which one works best.
Cheers, Alan
Cheers, Alan
ASKER
Joe, I tried your solution and while it worked ok, it didn't really speed the time the query took to run, which is what I am looking for.
Bhavesh, I'm not familiar with temp tables so maybe there is something more that I need to do but when I try the following code, I get "There is already an object named '#Temp' in the database."
Bhavesh, I'm not familiar with temp tables so maybe there is something more that I need to do but when I try the following code, I get "There is already an object named '#Temp' in the database."
Alter PROCEDURE [dbo].[spSelectENG_OverAndAboveSummary_Test]
(@OA_Nr varchar(5) = NULL)
--@Engineer varchar(6) = NULL) --Must use a varchar to use wildcard,
AS
CREATE TABLE #Temp(OaNumber char(5),ProjectDescription Varchar(MAX),tp money,tt money, tl Money)
SELECT oap.OANumber,
oap.ProjectDescription,
dbo.fn_Eng_OaPurchaceTotals(oap.OANumber) AS TotalPurchases,
dbo.fn_Eng_OaTravelTotals(oap.OANumber) AS TotalTravel,
dbo.fn_Eng_OaLaborTotals(oap.OANumber) AS TotalLabor
INTO #Temp
FROM
dbo.ENG_OverAndAboveProjects oap
WHERE oap.OANumber = @OA_Nr
SELECT oap.OANumber FROM #Temp --There is already an object named '#Temp' in the database.
Hi Alan,
As you created
CREATE TABLE #Temp(OaNumber char(5),ProjectDescription Varchar(MAX),tp money,tt money, tl Money)
so you no need to use INTO #Temp
you need to use below query.
- Bhavesh
As you created
CREATE TABLE #Temp(OaNumber char(5),ProjectDescription
so you no need to use INTO #Temp
you need to use below query.
- Bhavesh
Alter PROCEDURE [dbo].[spSelectENG_OverAndAboveSummary_Test]
(@OA_Nr varchar(5) = NULL)
--@Engineer varchar(6) = NULL) --Must use a varchar to use wildcard,
AS
CREATE TABLE #Temp(OaNumber char(5),ProjectDescription Varchar(MAX),tp money,tt money, tl Money)
insert into #Temp
SELECT oap.OANumber,
oap.ProjectDescription,
dbo.fn_Eng_OaPurchaceTotals(oap.OANumber) AS TotalPurchases,
dbo.fn_Eng_OaTravelTotals(oap.OANumber) AS TotalTravel,
dbo.fn_Eng_OaLaborTotals(oap.OANumber) AS TotalLabor
FROM
dbo.ENG_OverAndAboveProjects oap
WHERE oap.OANumber = @OA_Nr
SELECT oap.OANumber FROM #Temp --There is already an object named '#Temp' in the database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
I am not sure, this will improve performance.
If you can share your function code then, might experts can give you suggestion for better approach.
- Bhavesh
I am not sure, this will improve performance.
If you can share your function code then, might experts can give you suggestion for better approach.
- Bhavesh
If your function doesn't contain complex query/query working with huge dataset.
Reduce 1 call may not effect much of performance. (in profiler, CPU maybe the same, but maybe reduce READ)
Reduce 1 call may not effect much of performance. (in profiler, CPU maybe the same, but maybe reduce READ)
https://www.experts-exchange.com/questions/26818174/SQL-Server-TSQL-Trying-not-to-reuse-scalar-function-call.html
and with the same way of my answer, your query should be like this
SELECT
<other field lists>,
Sub.TotalLaborCost * Sub.TotalFringeCosts AS TotalCosts
FROM (
SELECT
hs.TS_LN_KEY
hs.LAB_CST_AMT AS TotalLaborCost,
dbo.fn_Eng_OaFringeTotals(
.....
FROM ....etc
) Sub