如何将参数传递到sql server UDF并缓存它们(How to pass parameters into sql server UDF and have them cached)

如何将参数传递到sql server UDF并缓存它们(How to pass parameters into sql server UDF and have them cached)

我有一个UDF函数，它根据作为参数传入的其他字段值计算“状态”。 我使用C#作为sql server项目。

不是实际的功能，而是我的问题作为一个更简单的例子：

MyUdf(int a,int b) ：

return a+b

但我想要 ：

返回a+b+c ，其中c是预定义值，从另一个表中检索为参数，以便稍后更改。

问题有两个：

1）如何从UDF另一个表加载此值？

更重要的是......

2）如何为以后缓存该值，以便不为每一行检索它，因为这可能是性能问题。

我不是在寻找解决方案，而是寻找有关如何进行的提示。

我的实际功能：

[SqlFunction] public static string CalcBDAlert(DateTime currentTime, string status, DateTime prevMilestoneAt, DateTime nextMilestoneAt) { // quick process if (status == "Finished") { return "4,..."; } else if (status == "Cancelled") { return "5,..."; } #region Un-Allocated if (status == "Un-Alloc.") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was start, creation of call. if (minPassedSinceMilestone < 15) { return "3,..."; } else if (minPassedSinceMilestone < 30) { return "2,>15 Min"; } else if (minPassedSinceMilestone < 35) { return "1,>30 Min!"; } else { return "0,>35 Min!!"; } } #endregion #region Allocated else if (status == "Alloc.") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was allocation of engineer. var minRemTillNextMilestone = (nextMilestoneAt - prevMilestoneAt).TotalMinutes; // next milestone is ETA, predicted arrival time. var percentageTillNextMilestone = minPassedSinceMilestone / minRemTillNextMilestone; if (percentageTillNextMilestone < 0.90) { return "3,..."; } else if (percentageTillNextMilestone < 0.95) { return "2,90-95%"; } else if (percentageTillNextMilestone < 1) { return "1,>95%!"; } else { return "0,>100%!!"; } } #endregion #region Arrived else if (status == "Arrived") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was arrival of engineer. var minRemTillNextMilestone = (nextMilestoneAt - prevMilestoneAt).TotalMinutes; // next milestone is completion of work. var percentageTillNextMilestone = minPassedSinceMilestone / minRemTillNextMilestone; if (percentageTillNextMilestone < 0.90) { return "3,..."; } else if (percentageTillNextMilestone < 0.95) { return "2,90-95%"; } else if (percentageTillNextMilestone < 1) { return "1,>95%!"; } else { return "0,>100%!!"; } } #endregion return "-1,Unknown"; }

我想参数化并缓存百分比（.9，.95,1）和分钟（15,30,35）等。

I have a UDF function that calculates the "status" based on other field values passed in as parameters. I have made this using C# as a sql server project.

Not the actual function, but my problem as a simpler example :

MyUdf(int a,int b) :

return a+b

But I want :

return a+b+c, where c is a predefined value, retrieved from another table as a parameter so it can be changed later.

Problem is two fold :

1) How to load this value from another table in a UDF?

and more importantly...

2) How to cache the value for later so that it is not retrieved for every row as that could be a performance issue.

I am not looking for a solution, but for tips on how to proceed.

My Actual function :

[SqlFunction] public static string CalcBDAlert(DateTime currentTime, string status, DateTime prevMilestoneAt, DateTime nextMilestoneAt) { // quick process if (status == "Finished") { return "4,..."; } else if (status == "Cancelled") { return "5,..."; } #region Un-Allocated if (status == "Un-Alloc.") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was start, creation of call. if (minPassedSinceMilestone < 15) { return "3,..."; } else if (minPassedSinceMilestone < 30) { return "2,>15 Min"; } else if (minPassedSinceMilestone < 35) { return "1,>30 Min!"; } else { return "0,>35 Min!!"; } } #endregion #region Allocated else if (status == "Alloc.") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was allocation of engineer. var minRemTillNextMilestone = (nextMilestoneAt - prevMilestoneAt).TotalMinutes; // next milestone is ETA, predicted arrival time. var percentageTillNextMilestone = minPassedSinceMilestone / minRemTillNextMilestone; if (percentageTillNextMilestone < 0.90) { return "3,..."; } else if (percentageTillNextMilestone < 0.95) { return "2,90-95%"; } else if (percentageTillNextMilestone < 1) { return "1,>95%!"; } else { return "0,>100%!!"; } } #endregion #region Arrived else if (status == "Arrived") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was arrival of engineer. var minRemTillNextMilestone = (nextMilestoneAt - prevMilestoneAt).TotalMinutes; // next milestone is completion of work. var percentageTillNextMilestone = minPassedSinceMilestone / minRemTillNextMilestone; if (percentageTillNextMilestone < 0.90) { return "3,..."; } else if (percentageTillNextMilestone < 0.95) { return "2,90-95%"; } else if (percentageTillNextMilestone < 1) { return "1,>95%!"; } else { return "0,>100%!!"; } } #endregion return "-1,Unknown"; }

I would like to parametrize and cache the percentages (.9, .95, 1) and minutes (15,30,35) etc.

最满意答案

嗯，udf本身可以是这样的：

CREATE FUNCTION myUdf ( @a INT, @b INT ) RETURNS INT AS BEGIN DECLARE @c INT SET @c = (SELECT TOP(1) id FROM my_table) RETURN @a + @b + @c END GO

那么缓存 - 你根本不需要关心它，rdbms会为你做。 Sql server在内存中保留了一些选择结果，执行计划等，所以如果你的udf会经常执行--sql server cache it

Well, udf itself can be kind of:

CREATE FUNCTION myUdf ( @a INT, @b INT ) RETURNS INT AS BEGIN DECLARE @c INT SET @c = (SELECT TOP(1) id FROM my_table) RETURN @a + @b + @c END GO

What about caching - you don't have to care about it at all, rdbms will do it for you. Sql server keeps in memory some selection results, execution plans etc, so if your udf will execute often enough - sql server cache it

如何将参数传递到sql server UDF并缓存它们(How to pass parameters into sql server UDF and have them cached)

我有一个UDF函数，它根据作为参数传入的其他字段值计算“状态”。 我使用C#作为sql server项目。

不是实际的功能，而是我的问题作为一个更简单的例子：

MyUdf(int a,int b) ：

return a+b

但我想要 ：

返回a+b+c ，其中c是预定义值，从另一个表中检索为参数，以便稍后更改。

问题有两个：

1）如何从UDF另一个表加载此值？

更重要的是......

2）如何为以后缓存该值，以便不为每一行检索它，因为这可能是性能问题。

我不是在寻找解决方案，而是寻找有关如何进行的提示。

我的实际功能：

[SqlFunction] public static string CalcBDAlert(DateTime currentTime, string status, DateTime prevMilestoneAt, DateTime nextMilestoneAt) { // quick process if (status == "Finished") { return "4,..."; } else if (status == "Cancelled") { return "5,..."; } #region Un-Allocated if (status == "Un-Alloc.") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was start, creation of call. if (minPassedSinceMilestone < 15) { return "3,..."; } else if (minPassedSinceMilestone < 30) { return "2,>15 Min"; } else if (minPassedSinceMilestone < 35) { return "1,>30 Min!"; } else { return "0,>35 Min!!"; } } #endregion #region Allocated else if (status == "Alloc.") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was allocation of engineer. var minRemTillNextMilestone = (nextMilestoneAt - prevMilestoneAt).TotalMinutes; // next milestone is ETA, predicted arrival time. var percentageTillNextMilestone = minPassedSinceMilestone / minRemTillNextMilestone; if (percentageTillNextMilestone < 0.90) { return "3,..."; } else if (percentageTillNextMilestone < 0.95) { return "2,90-95%"; } else if (percentageTillNextMilestone < 1) { return "1,>95%!"; } else { return "0,>100%!!"; } } #endregion #region Arrived else if (status == "Arrived") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was arrival of engineer. var minRemTillNextMilestone = (nextMilestoneAt - prevMilestoneAt).TotalMinutes; // next milestone is completion of work. var percentageTillNextMilestone = minPassedSinceMilestone / minRemTillNextMilestone; if (percentageTillNextMilestone < 0.90) { return "3,..."; } else if (percentageTillNextMilestone < 0.95) { return "2,90-95%"; } else if (percentageTillNextMilestone < 1) { return "1,>95%!"; } else { return "0,>100%!!"; } } #endregion return "-1,Unknown"; }

我想参数化并缓存百分比（.9，.95,1）和分钟（15,30,35）等。

I have a UDF function that calculates the "status" based on other field values passed in as parameters. I have made this using C# as a sql server project.

Not the actual function, but my problem as a simpler example :

MyUdf(int a,int b) :

return a+b

But I want :

return a+b+c, where c is a predefined value, retrieved from another table as a parameter so it can be changed later.

Problem is two fold :

1) How to load this value from another table in a UDF?

and more importantly...

2) How to cache the value for later so that it is not retrieved for every row as that could be a performance issue.

I am not looking for a solution, but for tips on how to proceed.

My Actual function :

[SqlFunction] public static string CalcBDAlert(DateTime currentTime, string status, DateTime prevMilestoneAt, DateTime nextMilestoneAt) { // quick process if (status == "Finished") { return "4,..."; } else if (status == "Cancelled") { return "5,..."; } #region Un-Allocated if (status == "Un-Alloc.") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was start, creation of call. if (minPassedSinceMilestone < 15) { return "3,..."; } else if (minPassedSinceMilestone < 30) { return "2,>15 Min"; } else if (minPassedSinceMilestone < 35) { return "1,>30 Min!"; } else { return "0,>35 Min!!"; } } #endregion #region Allocated else if (status == "Alloc.") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was allocation of engineer. var minRemTillNextMilestone = (nextMilestoneAt - prevMilestoneAt).TotalMinutes; // next milestone is ETA, predicted arrival time. var percentageTillNextMilestone = minPassedSinceMilestone / minRemTillNextMilestone; if (percentageTillNextMilestone < 0.90) { return "3,..."; } else if (percentageTillNextMilestone < 0.95) { return "2,90-95%"; } else if (percentageTillNextMilestone < 1) { return "1,>95%!"; } else { return "0,>100%!!"; } } #endregion #region Arrived else if (status == "Arrived") { var minPassedSinceMilestone = (currentTime - prevMilestoneAt).TotalMinutes; // milestone was arrival of engineer. var minRemTillNextMilestone = (nextMilestoneAt - prevMilestoneAt).TotalMinutes; // next milestone is completion of work. var percentageTillNextMilestone = minPassedSinceMilestone / minRemTillNextMilestone; if (percentageTillNextMilestone < 0.90) { return "3,..."; } else if (percentageTillNextMilestone < 0.95) { return "2,90-95%"; } else if (percentageTillNextMilestone < 1) { return "1,>95%!"; } else { return "0,>100%!!"; } } #endregion return "-1,Unknown"; }

I would like to parametrize and cache the percentages (.9, .95, 1) and minutes (15,30,35) etc.

最满意答案

嗯，udf本身可以是这样的：

CREATE FUNCTION myUdf ( @a INT, @b INT ) RETURNS INT AS BEGIN DECLARE @c INT SET @c = (SELECT TOP(1) id FROM my_table) RETURN @a + @b + @c END GO

那么缓存 - 你根本不需要关心它，rdbms会为你做。 Sql server在内存中保留了一些选择结果，执行计划等，所以如果你的udf会经常执行--sql server cache it

Well, udf itself can be kind of:

CREATE FUNCTION myUdf ( @a INT, @b INT ) RETURNS INT AS BEGIN DECLARE @c INT SET @c = (SELECT TOP(1) id FROM my_table) RETURN @a + @b + @c END GO

What about caching - you don't have to care about it at all, rdbms will do it for you. Sql server keeps in memory some selection results, execution plans etc, so if your udf will execute often enough - sql server cache it