Link to home
Start Free TrialLog in
Avatar of AkAlan
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_KEY) AS TotalFringeCosts,     --This returns the sum of fringe costs,
dbo.fn_Eng_OaFringeTotals(hs.TS_LN_KEY) + hs.LAB_CST_AMT  AS TotalCosts,   --have to rerun the function and add it to the laborcost

FROM ....etc

Open in new window


Is ther a way to assign the fringe costs to a variable and reuse it?
 
Avatar of JoeNuvo
JoeNuvo
Flag of Viet Nam image

take a look at this one to see if any comment might good to apply for you
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(hs.TS_LN_KEY) AS TotalFringeCosts,
      .....
      FROM ....etc
) Sub
Avatar of Bhavesh Shah

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(hs.TS_LN_KEY) AS TotalFringeCosts,     --This returns the sum of fringe costs,
dbo.fn_Eng_OaFringeTotals(hs.TS_LN_KEY) + hs.LAB_CST_AMT  AS TotalCosts,   --have to rerun the function and add it to the laborcost

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
Avatar of AkAlan
AkAlan

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.
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_KEY) 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
Avatar of AkAlan

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
Avatar of AkAlan

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."


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.

Open in new window

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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)