One new thing that SQL Server 2016 has added is the ability to natively compile user-defined functions. Previously, native compilation, part of the hekaton feature, was limited to stored procedures.<\/p>\n
When I saw that, the first question that came to mind is whether natively compiling a scalar function reduces the overhead when calling that function within another query. I\u2019m not talking about data-accessing scalar UDFs, since natively compiled functions can only access in-memory tables, but functions that do simple manipulation of the parameters passed in. String formatting, for example, or date manipulation.<\/p>\n
While not as harmful as data-accessing scalar UDFs, there\u2019s still overhead as these are not inline functions, they\u2019re called for each row in the resultset (as a look at the Stored Procedure Completed XE event would show), and the call to the function takes time. Admittedly not a lot of time, but when it\u2019s on each row of a large resultset the total can be noticeable.<\/p>\n
I decided to do a simple test. A query against a table with ~600k rows, one query with a traditional scalar function, one with a natively compiled function and one with the function\u2019s contents in the query as a column.<\/p>\n
The two functions:<\/p>\n
CREATE FUNCTION dbo.DateOnly (@Input DATETIME)\r\nRETURNS DATETIME\r\nAS\r\nBEGIN\r\n RETURN DATEADD(dd, DATEDIFF (dd, 0, @Input), 0);\r\nEND\r\nGO<\/pre>\nand<\/p>\n
CREATE FUNCTION dbo.DateOnlyHekaton (@Input DATETIME)\r\nRETURNS DATETIME\r\nWITH NATIVE_COMPILATION, SCHEMABINDING\r\nAS\r\nBEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')\r\n RETURN DATEADD(dd,DATEDIFF(dd,0,@Input),0);\r\nEND\r\nGO<\/pre>\nAnd the query without the function would be a simple<\/p>\n
SELECT DATEADD(dd, DATEDIFF(dd, 0, SomeDate), 0)\r\nFROM SomeTable;<\/pre>\nDurations and CPU usage were caught with Extended Events. I ran each query 25 times and aggregated the results.<\/p>\n
| <\/td>\n | Average CPU (ms)<\/td>\n | Average Duration (ms)<\/td>\n<\/tr>\n |
| In-line expression<\/td>\n | 289<\/td>\n | 294<\/td>\n<\/tr>\n |
| Normal function<\/td>\n | 3555<\/td>\n | 3814<\/td>\n<\/tr>\n |
| Natively Compiled Function<\/td>\n | 3318<\/td>\n | 3352<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Not quite what I hoped. While the overhead of the natively compiled function is lower, it\u2019s lower only by about 10%, which really is not worth it, now when we\u2019re talking about an order of magnitude difference from the query without the function call.<\/p>\n Looks like the guidance is still going to be to not use scalar UDFs within other queries.<\/p>\n","protected":false},"excerpt":{"rendered":" |