SQL Server Table-Value Function and Except Combination performance

Question

I have a table (Resources with about 18000 records) and a Table-Value Function with this body :

ALTER FUNCTION [dbo].[tfn_GetPackageResources]
(   
    @packageId int=null,
    @resourceTypeId int=null,
    @resourceCategoryId int=null,
    @resourceGroupId int=null,
    @resourceSubGroupId int=null
)
RETURNS TABLE 
AS
RETURN 
(

    SELECT Resources.*
    FROM    Resources 
            INNER JOIN ResourceSubGroups ON Resources.ResourceSubGroupId=ResourceSubGroups.Id
            INNER JOIN ResourceGroups ON ResourceSubGroups.ResourceGroupId=ResourceGroups.Id
            INNER JOIN ResourceCategories ON ResourceGroups.ResourceCategoryId=ResourceCategories.Id
            INNER JOIN ResourceTypes ON ResourceCategories.ResourceTypeId=ResourceTypes.Id
    WHERE
        (@resourceSubGroupId IS NULL OR ResourceSubGroupId=@resourceSubGroupId) AND
        (@resourceGroupId IS NULL OR ResourceGroupId=@resourceGroupId) AND
        (@resourceCategoryId IS NULL OR ResourceCategoryId=@resourceCategoryId) AND
        (@resourceTypeId IS NULL OR ResourceTypeId=@resourceTypeId) AND
        (@packageId IS NULL OR PackageId=@packageId) 



)

now I make a query like this :

SELECT  id
FROM    dbo.tfn_GetPackageResources(@sourcePackageId,null,null,null,null)
WHERE   id not in(  
    SELECT  a.Id 
    FROM    dbo.tfn_GetPackageResources(@sourcePackageId,null,null,null,null) a INNER JOIN
            dbo.tfn_GetPackageResources(@comparePackageId,null,null,null,null) b
            ON  a.No = b.No AND 
                a.UnitCode=b.UnitCode AND 
                a.IsCompound=b.IsCompound AND 
                a.Title=b.Title
    )

This query takes about 10 seconds!(Although each part query runs extremely fast but the whole one take time) I check it with LEFT JOIN and NOT EXISTS but the result was same. but if I run the query on the Resources table directly it only takes one second or less! the fast query is :

select * from resources where id not in (select id from resources)

how can I solve it?

How fast is it if you just do `SELECT * FROM dbo.tfn_GetPackageResources(@sourcePackageId,null,null,null,null)`? — Ivan Golović, Feb 20 '13 at 08:20
It is hard to find out the reason without looking at the execution plans. But it is likely that it is because optimizer think that table-valued function returns just one row and chooses an unefficient plan. — Igor Borisenko, Feb 20 '13 at 08:28

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

Your UDF is expanded like a macro.

So your complete query has

9 INNER JOINs in the IN clause
4 INNER JOINs in the main SELECT.
You apply (... IS NULL OR ...) 15 times in total for each of your WHERE clauses.

Your idea of clever code reuse fails because of this expansion
SQL does not usually lend itself to this reuse.

Keep it simple:

SELECT
    R.id
FROM
    Resources R
WHERE
    R.PackageId = @sourcePackageId
    AND 
    R.id not in (  
        SELECT  a.Id 
       FROM    Resources  a
               INNER JOIN
               Resources  b
                   ON  a.No = b.No AND 
                        a.UnitCode=b.UnitCode AND 
                        a.IsCompound=b.IsCompound AND 
                        a.Title=b.Title
        WHERE
             a.PackageId = @sourcePackageId
             AND
             b.PackageId = @comparePackageId
    )

For more, see my other answers here:

score 0 · Answer 2 · answered Feb 20 '13 at 08:22

0

In your function, declare the type of the table it returns, and include a primary key. This way, the ID filter will be able to look up the IDs more efficiently.

See http://msdn.microsoft.com/en-us/library/ms191165(v=sql.105).aspx for the syntax.

answered Feb 20 '13 at 08:22

flup

26,937
7
52
74

This makes each UDF run to completion with no expansion. In most cases. It will most likely run worse. See my answer for links to explain why – gbn Feb 20 '13 at 08:25

Ivan Golović · Answer 3 · 2013-02-20T09:19:31.453

Thing you should try is to break one complicated query into multiple simple ones that store their results in temporary tables, this way one complicated execution plan will be replaced by several simple plans whose total execution time might be shorter then the execution time of a complicated execution plan:

SELECT  *
INTO    #temp1
FROM    dbo.tfn_GetPackageResources(@sourcePackageId,null,null,null,null)

SELECT  *
INTO    #temp2
FROM    dbo.tfn_GetPackageResources(@comparePackageId,null,null,null,null)

SELECT  a.Id 
INTO    #ids
FROM    #temp1 a 
INNER JOIN
        #temp2 b ON  
        a.No = b.No 
AND     a.UnitCode=b.UnitCode 
AND     a.IsCompound=b.IsCompound 
AND     a.Title=b.Title

SELECT  id
FROM    #temp1
WHERE   id not in(  
    SELECT  Id 
    FROM    #ids
)

-- you can also try replacing the above query with this one if it performs faster
SELECT  id
FROM    #temp1 t
WHERE   NOT EXISTS 
(
    SELECT Id FROM #ids i WHERE i.Id = t.id
)

SQL Server Table-Value Function and Except Combination performance

3 Answers3