8

I have this:

SELECT
A
,B
,dbo.Func(C)
,dbo.Func(D)
,dbo.Func(E)
,F
FROM abcdef
WHERE
0 = dbo.Func(C) + dbo.Func(D)

I have read that this is not good practice because the function is called million of times and it has a bad impact on performance.

I tried to rewrite it with CROSS APPLY:

SELECT *
 FROM abcdef
  CROSS APPLY dbo.Func(D) as FD

but it returns this error:

Invalid object name 'dbo.Func'

Can I use CROSS APPLY only on TABLE VALUED functions ?

And is CROSS APPLY suitable for scalar functions (converted to table valued function) anyway ? Because on my small performance test CROSS APPLY is a bit slower.

0

2 Answers 2

13

I read that this is not good practice because function is called 'zilion' times and it have bad impact on performance.

While CROSS APPLY can be useful in some cases, I don't expect any difference in performance between calling the function in WHERE or CROSS APPLY in the specific case. If the table has a million rows (and columns C and D possibly a million different values), a million times the function will be called. How can it be otherwise?

I tried to rewrite it with CROSS APPLY.

Here's how:

SELECT
    t.A,
    t.B,
    ca.Fc,
    ca.Fd,
    dbo.Func(t.E) AS Fe
    t.F,
FROM abcdef AS t
  CROSS APPLY 
    ( SELECT
          dbo.Func(t.C) AS Fc, 
          dbo.Func(t.D) AS Fd
    ) AS ca
WHERE 0 = ca.Fc + ca.Fd ;

or:

SELECT
...
FROM abcdef AS t
  CROSS APPLY 
    ( SELECT
          dbo.Func(t.C) AS Fc, 
          dbo.Func(t.D) AS Fd
      FROM (SELECT NULL) AS dummy
      WHERE 0 = dbo.Func(t.C) + dbo.Func(t.d) 
    ) AS ca ;

Again, I don't think this will have any effects on efficiency.

1
  • thank you, about performance, it was ment that if you need to use the output of the function more then one. In another column, for example SELECT ca.Fc, ca.Fc + ca.Fd
    – Muflix
    Feb 3, 2017 at 15:00
1

Scalar udf's may cause your query to run single threaded.

See Ozar's post on the matter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.