Reuse SQL Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Reuse SQL Function

Author

Topic

psikadelik
Starting Member

5 Posts

Posted - 2011-04-09 : 08:21:42

Hi,

Is there a way to only call the function one time in this query ?


SELECT 1, CASE WHEN [dbo].CHARGEVAT(T_BILL_Service.ServiceId) = 1 THEN 'SERVICO' ELSE 'SERVICONOVAT' END, 4, '' AS Notes, SUM(T_BILL_Service.BillingValue), 1 AS Qty,0,0,0,0 
FROM T_BILL_Service INNER JOIN T_BILL_InvoicePlan ON T_BILL_Service.InvoicePlanID = T_BILL_InvoicePlan.InvoicePlanID 
WHERE     (T_BILL_Service.InvoiceID = 385266)
GROUP BY CASE WHEN [dbo].CHARGEVAT(T_BILL_Service.ServiceId) = 1 THEN 'SERVICO' ELSE 'SERVICONOVAT' END

Thank you for your time.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-09 : 08:57:41

Try this:

;WITH C AS
(
SELECT CASE WHEN [dbo].CHARGEVAT(T_BILL_Service.ServiceId) = 1 THEN 'SERVICO' ELSE 'SERVICONOVAT' END AS grp_fct, 
       T_BILL_Service.BillingValue
  FROM T_BILL_Service 
       JOIN T_BILL_InvoicePlan 
         ON T_BILL_Service.InvoicePlanID = T_BILL_InvoicePlan.InvoicePlanID 
 WHERE (T_BILL_Service.InvoiceID = 385266)
)
SELECT 1,
       grp_fct,
       4,
       '' AS Notes, 
       SUM(BillingValue), 
       1 AS Qty,
       0,
       0,
       0,
       0 
  FROM C
 GROUP BY grp_fct;

______________________

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-09 : 09:01:44

even shorter code:

;WITH C AS
(
SELECT CASE WHEN [dbo].CHARGEVAT(TS.ServiceId) = 1 THEN 'SERVICO' ELSE 'SERVICONOVAT' END AS grp_fct, 
       TS.BillingValue
  FROM T_BILL_Service TS
       JOIN T_BILL_InvoicePlan TI
         ON TS.InvoicePlanID = TI.InvoicePlanID 
 WHERE TS.InvoiceID = 385266
)
SELECT 1,
       grp_fct,
       4,
       '' AS Notes, 
       SUM(BillingValue), 
       1 AS Qty,
       0,
       0,
       0,
       0 
  FROM C
 GROUP BY grp_fct;

______________________

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-09 : 09:42:42

Is your interest in avoiding the repetition of the lengthy "case-when" statement, or is it to avoid the overhead of two function calls per row?

If it is the latter, it would seem to me that the optimizer should generate an execution plan that calls the function only once per row. May be you can look at the execution plan to see if that is the case. I might be harboring a very high opinion of the optimizer that is not really warranted.

I wonder if there is another way to find how many times a function gets called. You can't do anything in a function that would cause side-effects, and I don't know of a way to find how many times it gets called without causing a side effect.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-09 : 10:20:39

Scalar functions always get evaluated for each row the query has to process. If the function performs SELECT operations on tables or views (or table-valued functions) then the optimizer will likely pick nested loop joins on the results, which are a poor join type for larger results (> 1000 on either side of the join).

If you need to optimize this for performance and still need to use the function, I'd recommend reading Paul White's blog:

http://sqlblog.com/blogs/paul_white/

He has a lot of great material on optimization and optimizer internals that show the merits and weaknesses of different approaches.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources