SQL语句调用自定义函数查询很慢优化问题

同事说，某某报表查询很慢，让我改一下

优化前：

 

我这里用的数据库是：SqlServer

优化前

SELECT
     COUNT(subquery.JobID) JobNoCount,
     SISendUser, 
     MAX(SenderName) SendUserName,
     LocalName,
     SUM(MasterBillID) MasterBillCopies,
     SUM(TEU) TEU 
     -- 临时表:subquery
     FROM (
            SELECT 
            job.jobid
            ,MAX(bill.SISendTime) SISendTime
            ,MAX(bill.SISendUser) SISendUser
            ,MAX(con.FIRST_NAME) SenderName
            ,MAX(cu.LocalName) LocalName
            ,count (bill.MasterBillID) MasterBillID
            -- 这里调用自定义函数查询
            ,COnvert(DECIMAL,dbo.GetContainerTEU(job.jobid)) TEU
            FROM dbo.oxJob job
            LEFT JOIN dbo.oxOrder ox ON ox.JobID = job.JobID
            LEFT JOIN  dbo.oxMasterBill bill ON bill.JobID = job.JobID
            LEFT JOIN dbo.CuCustomer  cu ON cu.CustomerID = ox.CarrierCode
            LEFT JOIN dbo.CONTACT con ON con.CONTACT_GID=bill.SISendUser
            WHERE 
            CONVERT(char(10),bill.SISendTime,120) BETWEEN '2020-01-01' AND '2020-04-24' 
            GROUP BY job.JobID
        ) subquery
GROUP BY subquery.SISendUser,subquery.LocalName

　　执行时间：2分钟

　　　　　

优化后

SELECT
     COUNT(subquery.JobID) JobNoCount,
     SISendUser, 
     MAX(SenderName) SendUserName,
     LocalName,
     SUM(MasterBillID) MasterBillCopies,
     SUM(TEU) TEU 
     FROM (
            SELECT 
            job.jobid
            ,MAX(bill.SISendTime) SISendTime
            ,MAX(bill.SISendUser) SISendUser
            ,MAX(con.FIRST_NAME) SenderName
            ,MAX(cu.LocalName) LocalName
            ,count (bill.MasterBillID) MasterBillID
            -- 这里调用自定义函数查询 性能比较慢
            --,COnvert(DECIMAL,dbo.GetContainerTEU(job.jobid)) TEU
            ,(
                --把自定义函数中的代码提取出来
                select   convert(DECIMAL,sum(b.TEU)) FROM oxContainer ta 
                    left join StContainer b on ta.ContainerType = b.ISOCode
                    where ta.JobID = job.JobID
            ) TEU
            FROM oxjob job
            LEFT JOIN oxorder ox ON ox.JobID = job.JobID
            LEFT JOIN  dbo.oxMasterBill bill ON bill.JobID = job.JobID
            LEFT JOIN dbo.CuCustomer  cu ON cu.CustomerID = ox.CarrierCode
            LEFT JOIN dbo.CONTACT con ON con.CONTACT_GID=bill.SISendUser
            WHERE 
            CONVERT(char(10),bill.SISendTime,120) BETWEEN '2020-04-08' AND '2020-04-24' 
            GROUP BY job.JobID
            ) subquery
GROUP BY subquery.SISendUser,subquery.LocalName

　　执行时间：1秒都不到

　　　　　

自定义函数

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE function [com].[GetContainerTEU]
(@JobID uniqueidentifier)
returns nvarchar(4000)
as
begin 
    declare @rtn nvarchar(4000)
    select @rtn = ''
    select @rtn  = convert(varchar(50),sum(b.TEU))  
    from oxContainer a 
    left join StContainer b on a.ContainerType = b.ISOCode
    where a.JobID = @JobID
    return @rtn
end
GO

总结

1. 自定义函数没有办法建立函数索引，这样导致查询结果很慢。
2. 自定义函数的性能比较差，能不用的情况尽量不要用。
3. 能用存储过程，就不用自定义函数。

　　

 

　　以上属于个人总结，如有不足之处，希望可以留言哦！