如何提高SQL Server中纯用户定义函数的性能? [英] What can I do to improve performance of my pure User Defined Function in SQL Server?
问题描述
我制作了一个简单但计算复杂的UDF，用于查询很少更改的表.在典型用法中，该函数在很小的参数域中多次通过WHERE子句调用.
I have made a simple, but relatively computationally complex, UDF that queries a rarely changing table. In typical usage this function is called many many times from a WHERE clauses over a very small domain of parameters.
我该怎么做才能更快地使用UDF?我的想法是应该有某种方式告诉SQL Server我的函数使用相同的参数返回相同的结果，因此应该被记住.在UDF中似乎没有办法做到这一点，因为它们必须是纯净的，因此不能写入临时表.
What can I do to make my usage of the UDF faster? My thoughts are that there should be some way to tell SQL Server that my function returns the same result with the same parameters and thus should be memoized. There doesn't seem a way to do it within the UDF because they are required to be pure and thus can't write to a temp table.
为完整起见，我的UDF在下面，尽管我正在寻求有关如何使小域上的调用UDF更快，而不是如何优化此特定UDF的一般性答案.
For completeness my UDF is below, though I am seeking a general answer on how to make calling UDFs on small domains faster, and not how to optimize this particular UDF.
CREATE function [dbo].[WorkDay] (
@inputDate datetime,
@offset int)
returns datetime as begin
declare
@result datetime
set @result = @inputDate
while @offset != 0
begin
set @result = dateadd( day, sign(@offset), @result )
while ( DATEPART(weekday, @result ) not between 2 and 6 )
or @result in (select date from myDB.dbo.holidays
where calendar = 'US' and date = @result)
begin
set @result = dateadd( day, sign(@offset), @result )
end
set @offset = @offset - sign(@offset)
end
return @result
END
推荐答案
我的第一个想法是-性能问题是什么?确保在运行查询的循环中有一个循环(每行一次，在其中应用).但是，您得到的执行计划很差吗?您的结果集巨大吗?但是，让我们转向通用.曾经如何解决这个问题? SQL并没有真正做记忆(正如杰出的@Martin_Smith指出的那样).那男孩要做什么?
My first thought here is -- what's the performance problem? Sure you have a loop (once per row to apply where) within a loop that it runs a query. But are you getting poor execution plans? Are your result sets huge? But lets turn to the generic. How does once solve this problem? SQL doesn't really do memoization(as the illustrious @Martin_Smith points out). So what's a boy to do?
选项1-新设计
创建一个全新的设计.在这种特定情况下，@ Aaron_Bertrand指出日历表可以满足您的需求.完全正确.对于非日历情况，这并不能真正帮助您，但是在SQL中，通常情况下您需要有所不同.
Create an entirely new design. In this specific case @Aaron_Bertrand points out that a calendar table may meet your needs. Quite right. This doesn't really help you with non calendar situations, but as is often the case in SQL you need to think a bit different.
选项2-减少UDF呼叫
Narrow the set of items that call this function. This reminds me a lot of how to do successful paging/row counting. Generate a small result set that has the distinct values required and then call your UDF so it is only called a few times. This may or may not be an option, but can work in many scenarios.
选项3-动态UDF
对于这个建议，我可能会被嘘出房间，但是这里有.使该UDF变慢的是循环内的select语句.如果您的假期"表确实很少更改，则可以在表上放置一个触发器.触发器将写出并更新UDF.新的UDF可能会强行执行所有假期决定.用SQL编写SQL会有点吃人吗?当然.但这将摆脱子查询并加快UDF的速度.让骇客开始吧.
I'll probably get booed out of the room for this suggestion, but here goes. What makes this UDF slow is the select statement inside the loop. If your Holiday table really changes infrequently you could put a trigger on the table. The trigger would write out and updated UDF. The new UDF could brute force all the holiday decisions. Would it bit a bit like cannibalism with SQL writing SQL? Sure. But it would get rid of the sub-query and speed the UDF up. Let the heckling begin.
选项4-记住！
虽然SQL无法直接记忆，但我们确实有SQL CLR.将UDF转换为SQL CLR udf.在CLR中，您可以使用静态变量.您可以轻松地按一定的时间间隔获取假期"表，并将其存储在哈希表中.然后，只需在CLR中重写您的循环即可.如果这是适当的逻辑，您甚至可以走得更远并记住整个答案.
While SQL can't directly memoize, we do have SQL CLR. Convert the UDF to a SQL CLR udf. In CLR you get to use static variables. You could easily grab the Holidays table at some regular interval and store them in a hashtable. Then just rewrite your loop in the CLR. You could even go further and memoize the entire answer if that's appropriate logic.
更新:
选项1-我实际上是在尝试着眼于一般性，而不是您上面使用的示例功能.但是，如果您碰巧连续碰到几次，则UDF的当前设计允许多次调用Holiday表.使用某种日历样式表，其中包含不好的日子"和相应的下一个工作日"列表，这样您就可以消除多次点击和删除的可能性查询.
Option 1 - I was really trying to focus on the general here, not the example function you used above. However, the current design of your UDF allows for multiple calls to the Holiday table if you happen to hit a few in a row. Using some sort of calendar-style-table that contains a list of 'bad days' and the corresponding 'next business day' will allow you to remove the potential for multiple hits & queries.
选项3-虽然域名未知，但您可以很好地修改假期表.对于给定的假日，它将包含下一个相应的工作日.根据这些数据，您可以在底部吐出带有长case语句的UDF("5/5/2012"之后为"5/14/2012"或类似内容).此策略可能不适用于每种类型的问题，但对于某些类型的问题可能会很好.
Option 3 - While the domain is unknown ahead of time you could very well modify your holiday table. For a given holiday day it would contain the next corresponding work day. From this data you could spit out a UDF with a long case statement (when '5/5/2012' then '5/14/2012' or something similar) at the bottom. This strategy may not work for every type of problem, but could work well for some types of problems.
选项4-每种技术都有其含义.需要部署CLR，修改SQL Server配置，并且将SQL CLR限制为3.5框架.就个人而言，我发现这些调整很容易，但是您的情况可能有所不同(例如顽固的DBA或对生产服务器进行修改的限制).
Option 4 - There are implications to every technology. CLR needs to be deployed, the SQL Server configuration modified and SQL CLR is limited to the 3.5 framework. Personally, I've found these adjustments easy enough, but your situation may be different (say a recalcitrant DBA, or restrictions on modifications to production servers).
使用静态变量需要对程序集授予完全信任.您必须确保锁定正确.
Using static variables requires the assemblies be granted FULL TRUST. You'll have to make sure you get your locking correct.
有一些证据表明，在非常高的事务级别，CLR的性能不如直接SQL.但是，在您的方案中，此观察可能不适用，因为您的尝试(记忆)操作没有直接的SQL相关信息.
There is some evidence that at very high transaction levels CLR doesn't perform as well as direct SQL. In your scenario, however, this observation might not be applicable because there isn't a direct SQL correlary for what your trying to do (memoize).
这篇关于如何提高SQL Server中纯用户定义函数的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
