Yes, yes, yes, finally!<\/p>\n\n\n\n
It\u2019s hardly a secret that I\u2019m not a fan of scalar user-defined functions. I refer to them as \u2018developer pit-traps\u2019 due to the amount of times I\u2019ve seen developers absolutely wreck their database performance by over-using them (or using them at all).<\/p>\n\n\n\n
The main problem with them is that they haven\u2019t been in-line, meaning the \nfunction gets evaluated on every single row, and the overhead from doing so is \nusually terrible. <\/p>\n\n\n\n
One of the improvements in SQL Server 2019 is that scalar user-defined functions now are in-line. Not all of them, there are conditions that have to be met. Most scalar UDFs that I\u2019ve seem in client systems will meet them, the not referencing table variables will probably be the main limiting factor.<\/p>\n\n\n\n
The full requirements are laid out in the documentation: https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/user-defined-functions\/scalar-udf-inlining<\/a><\/p>\n\n\n\n I\u2019m going to use the same function that I used when I evaluated natively-compiled functions (https:\/\/www.sqlinthewild.co.za\/index.php\/2016\/01\/12\/natively-compiled-user-defined-functions\/<\/a>), and run it against a table with 860k rows in it, both in compat mode 140 (SQL Server 2017) and compat mode 150 (SQL Server 2019)<\/p>\n\n\n As in the earlier post, I\u2019ll use extended events to catch the performance \ncharacteristics. <\/p>\n\n\n\n First, something to compare against. The query, without functions, is:<\/p>\n\n\n This takes, on average, 343ms to run, and 320ms of CPU time.<\/p>\n\n\n\n The results of the first test are impressive.<\/p>\n\n\n\n I keep having people ask about SCHEMABINDING, so same test again, with the \nfunction recreated WITH SCHEMABINDING<\/p>\n\n\n\n Better, but still over an order of magnitude slower than the query without \nthe function in SQL 2017 and earlier.<\/p>\n\n\n\n Last test, what about something with data access? I\u2019ll switch to my Shipments and ShipmentDetails tables for this. The base query without the function is:<\/p>\n\n\n I\u2019m writing it with a subquery instead of a join to keep it as similar as \npossible to the version with the function. It should be the same as if I had \nused a join though. That query takes, on average, 200ms, with 145ms CPU time. \n<\/p>\n\n\n\n There are 26240 rows in the Shipments table, and on average 34 detail rows per shipment. The function is:<\/p>\n\n\n And the results are:<\/p>\n\n\n\n\nCREATE FUNCTION dbo.DateOnly (@Input DATETIME)\n RETURNS DATETIME\nAS\nBEGIN\n RETURN DATEADD(dd, DATEDIFF (dd, 0, @Input), 0);\nEND\nGO\n<\/pre><\/div>\n\n\n
\nSELECT DATEADD(dd, DATEDIFF (dd, 0, TransactionDate), 0) FROM Transactions\n<\/pre><\/div>\n\n\n
Compat Mode<\/td> Duration (ms)<\/td> CPU (ms)<\/td><\/tr> 140<\/td> 10 666<\/td> 8594<\/td><\/tr> 150<\/td> 356<\/td> 353<\/td><\/tr><\/tbody><\/table>\n\n\n\n Compat Mode<\/td> Duration (ms)<\/td> CPU (ms)<\/td><\/tr> 140<\/td> 5448<\/td> 3818
<\/td><\/tr>150<\/td> 325<\/td> 320<\/td><\/tr><\/tbody><\/table>\n\n\n\n \nSELECT s.ShipmentID, \n (SELECT SUM(Mass) AS TotalMass FROM ShipmentDetails sd WHERE sd.ShipmentID = s.ShipmentID) TotalShipmentMass\nFROM Shipments s;\n<\/pre><\/div>\n\n\n
\nCREATE FUNCTION dbo.ShipmentMass(@ShipmentID INT)\nRETURNS NUMERIC(10,2)\nAS\nBEGIN\n DECLARE @ShipmentMass NUMERIC(10,2);\n SELECT @ShipmentMass = SUM(Mass) FROM ShipmentDetails sd WHERE sd.ShipmentID = @ShipmentID;\n\n RETURN @ShipmentMass;\n\nEND\n<\/pre><\/div>\n\n\n