Why is a UDF so much slower than a subquery?

by

The UDF is a black box to the query optimiser so it’s executed for every row.
You are doing a row-by-row cursor. For each row in an asset, look up an id three times in another table. This happens when you use scalar or multi-statement UDFs (In-line UDFs are simply macros that expand into the outer query)

One of many articles on the problem is “Scalar functions, inlining, and performance: An entertaining title for a boring post“.

The sub-queries can be optimised to correlate and avoid the row-by-row operations.

What you really want is this:

SELECT
   uc.id AS creator,
   uu.id AS updater,
   uo.id AS owner,
   a.[name]
FROM
    asset a
    JOIN
    user uc ON uc.user_pk = a.created_by
    JOIN
    user uu ON uu.user_pk = a.updated_by
    JOIN
    user uo ON uo.user_pk = a.owned_by

Update Feb 2019

SQL Server 2019 starts to fix this problem.

More Related Contents:

  1. Multiple INSERT statements vs. single INSERT with multiple VALUES
  2. Multi-statement Table Valued Function vs Inline Table Valued Function
  3. SQL Server SELECT LAST N Rows
  4. When using GETDATE() in many places, is it better to use a variable?
  5. Is it possible to write single query on two table which are not connected to each other?
  6. Is the NOLOCK (Sql Server hint) bad practice?
  7. How to create a table from select query result in SQL Server 2008 [duplicate]
  8. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  9. Pass table as parameter into sql server UDF
  10. Parse comma-separated string to make IN List of strings in the Where clause
  11. OPTION (RECOMPILE) is Always Faster; Why?
  12. Rename column SQL Server 2008
  13. Compare DATETIME and DATE ignoring time portion
  14. Fastest way to determine if record exists
  15. How to find third or nᵗʰ maximum salary from salary table?
  16. SQL “between” not inclusive
  17. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  18. Selecting first row per group
  19. Using PIVOT in SQL Server 2008
  20. sql group by only rows which are in sequence
  21. Function in SQL Server 2008 similar to GREATEST in mysql?
  22. How to retrieve field names from temporary table (SQL Server 2008)
  23. SQL Server SELECT INTO @variable?
  24. How do I change db schema to dbo
  25. How to subtract 30 days from the current date using SQL Server
  26. Count records for every month in a year
  27. SQL Server: +(unary) operator on non-numeric Strings
  28. Use one CTE many times
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment