UDF vs Subquery Performance issue

Question

I have tried find the monthly total sales, total sendback values for every customer for 12 months.

With other 2 subqueries then the query become use 26 subqueries for every row and SQL statement is very big.

Then I created two sub-selects for sale and sendback totals...

The result query is become shorter but execution time is bigger than 20-30 times.

Is this normal
How can I return 0 if the subquery returns NULL..

EDIT:

(Select sum(chh2.cha_meblag)
 from dbo.CARI_HESAP_HAREKETLERI chh2
 where chh2.cha_normal_Iade=1 and
       chh2.cha_kod=ch.cari_kod and
       (chh2.cha_tarihi>='20130101' and chh2.cha_tarihi<='20130131')
) as Ocai

Questions like these are impossible to answer intelligently without seeing the code. For your first question, though, it doesn't surprise me that a query with a UDF would be slower than an equivalent query without one. — Gordon Linoff, Jun 10 '13 at 11:11
I think to post code for 1. but i saw some question like this when googled it and just want to get a simple answer. for the second one. (Select sum(chh2.cha_meblag) from dbo.CARI_HESAP_HAREKETLERI chh2 where chh2.cha_normal_Iade=1 and chh2.cha_kod=ch.cari_kod and (chh2.cha_tarihi>='20130101' and chh2.cha_tarihi<='20130131')) as Ocai, How can set this query for return 0 when null. — ikutluay, Jun 10 '13 at 11:23

Gordon Linoff · Answer 1 · 2013-06-10T13:07:37.237

The answer to your second question is to wrap the subquery in a coalesce():

coalesce((Select sum(chh2.cha_meblag)
          from dbo.CARI_HESAP_HAREKETLERI chh2
          where chh2.cha_normal_Iade=1 and
                chh2.cha_kod=ch.cari_kod and
                (chh2.cha_tarihi>='20130101' and chh2.cha_tarihi<='20130131')
         ), '') as Ocai

As Martin points out in his comment, coalesce() has what I consider to be a bug in SQL Server. In this case, it will execute the first expression twice, once to check the value against NULL and the second time to get the value. Given this, ISNULL() is better (even though I prefer standard functions in general):

isnull((Select sum(chh2.cha_meblag)
          from dbo.CARI_HESAP_HAREKETLERI chh2
          where chh2.cha_normal_Iade=1 and
                chh2.cha_kod=ch.cari_kod and
                (chh2.cha_tarihi>='20130101' and chh2.cha_tarihi<='20130131')
         ), '') as Ocai

[`ISNULL is better`](http://connect.microsoft.com/SQLServer/feedback/details/336002/unnecessarily-bad-performance-for-coalesce-subquery) — Martin Smith, Jun 10 '13 at 11:45
@MartinSmith . . . Thank you for reminding me about that shortcoming in SQL Server. Are there any plans to fix it? — Gordon Linoff, Jun 10 '13 at 13:05

score 1 · Answer 2 · edited May 23 '17 at 10:25

Based on your subquery it appears you could resolve your issue without correlated subqueries, and only using joins. However with only a portion of a query to work with it is difficult to be sure. This is why when posting questions it is a good idea to post as much relevant code as possible. Nonetheless, from what I gather you have somthing like this:

SELECT  (   SELECT  SUM(chh2.cha_meblag)
            FROM    dbo.CARI_HESAP_HAREKETLERI chh2
            WHERE   chh2.cha_normal_Iade = 1 
            AND     chh2.cha_kod = ch.cari_kod
            AND     chh2.cha_tarihi >= '20130101' 
            AND     chh2.cha_tarihi <= '20130131'
        ) AS Ocai,
        (   SELECT  SUM(chh2.cha_meblag)
            FROM    dbo.CARI_HESAP_HAREKETLERI chh2
            WHERE   chh2.cha_normal_Iade = 1 
            AND     chh2.cha_kod = ch.cari_kod
            AND     chh2.cha_tarihi >= '20130201' 
            AND     chh2.cha_tarihi <= '20130228'
        ) AS Ocai,
        ... etc for 12 months
FROM    SomeTable ch

You could turn this into:

SELECT  ch.cari_kod,
        [20130101] = ISNULL([20130101], 0),
        [20130201] = ISNULL([20130201], 0),
        [20130301] = ISNULL([20130301], 0),
        [20130401] = ISNULL([20130401], 0),
        [20130501] = ISNULL([20130501], 0),
        [20130601] = ISNULL([20130601], 0),
        [20130701] = ISNULL([20130701], 0),
        [20130801] = ISNULL([20130801], 0),
        [20130901] = ISNULL([20130901], 0),
        [20131001] = ISNULL([20131001], 0),
        [20131101] = ISNULL([20131101], 0),
        [20131201] = ISNULL([20131201], 0)
FROM    SomeTable
        LEFT JOIN
        (   SELECT  chh2.cha_kod,
                    [20130101] = SUM(CASE WHEN chh2.cha_tarihi >= '20130101' AND chh2.cha_tarihi < '20130201' THEN chh2.cha_meblag ELSE 0 END),
                    [20130201] = SUM(CASE WHEN chh2.cha_tarihi >= '20130201' AND chh2.cha_tarihi < '20130301' THEN chh2.cha_meblag ELSE 0 END),
                    [20130301] = SUM(CASE WHEN chh2.cha_tarihi >= '20130301' AND chh2.cha_tarihi < '20130401' THEN chh2.cha_meblag ELSE 0 END),
                    [20130401] = SUM(CASE WHEN chh2.cha_tarihi >= '20130401' AND chh2.cha_tarihi < '20130501' THEN chh2.cha_meblag ELSE 0 END),
                    [20130501] = SUM(CASE WHEN chh2.cha_tarihi >= '20130501' AND chh2.cha_tarihi < '20130601' THEN chh2.cha_meblag ELSE 0 END),
                    [20130601] = SUM(CASE WHEN chh2.cha_tarihi >= '20130601' AND chh2.cha_tarihi < '20130701' THEN chh2.cha_meblag ELSE 0 END),
                    [20130701] = SUM(CASE WHEN chh2.cha_tarihi >= '20130701' AND chh2.cha_tarihi < '20130801' THEN chh2.cha_meblag ELSE 0 END),
                    [20130801] = SUM(CASE WHEN chh2.cha_tarihi >= '20130801' AND chh2.cha_tarihi < '20130901' THEN chh2.cha_meblag ELSE 0 END),
                    [20130901] = SUM(CASE WHEN chh2.cha_tarihi >= '20130901' AND chh2.cha_tarihi < '20131001' THEN chh2.cha_meblag ELSE 0 END),
                    [20131001] = SUM(CASE WHEN chh2.cha_tarihi >= '20131001' AND chh2.cha_tarihi < '20131101' THEN chh2.cha_meblag ELSE 0 END),
                    [20131101] = SUM(CASE WHEN chh2.cha_tarihi >= '20131101' AND chh2.cha_tarihi < '20131201' THEN chh2.cha_meblag ELSE 0 END),
                    [20131201] = SUM(CASE WHEN chh2.cha_tarihi >= '20131201' AND chh2.cha_tarihi < '20140101' THEN chh2.cha_meblag ELSE 0 END)
            FROM    dbo.CARI_HESAP_HAREKETLERI chh2
            WHERE   chh2.cha_normal_Iade = 1 
            GROUP BY chh2.cha_kod
        ) chh2
            ON chh2.cha_kod = ch.cari_kod;

This may not even require a subquery, however as I have said, without your full query I can only speculate on your table structure, and the results you actually want.

To answer 1 yes it is normal for a UDF to perform worse than a subquery doing the same thing. There are a number of posts on the subject This answer was top of google when I searched for SQL-Server UDF versus subquery

UDF vs Subquery Performance issue

2 Answers2