Why do SQL Server Scalar-valued functions get slower?

by

In most cases, it’s best to avoid scalar valued functions that reference tables because (as others said) they are basically black boxes that need to be ran once for every row, and cannot be optimized by the query plan engine. Therefore, they tend to scale linearly even if the associated tables have indexes.

You may want to consider using an inline-table-valued function, since they are evaluated inline with the query, and can be optimized. You get the encapsulation you want, but the performance of pasting the expressions right in the select statement.

As a side effect of being inlined, they can’t contain any procedural code (no declare @variable; set @variable = ..; return). However, they can return several rows and columns.

You could re-write your functions something like this:

create function usf_GIS_GET_LAT(
    @City varchar (30),
    @State char (2)
)
returns table
as return (
  select top 1 lat
  from GIS_Location with (nolock) 
  where [State] = @State
    and [City] = @City
);

GO

create function usf_GIS_GET_LON (
    @City varchar (30),
    @State char (2)
)
returns table
as return (
  select top 1 LON
  from GIS_Location with (nolock)
  where [State] = @State
    and [City] = @City
);

The syntax to use them is also a little different:

select
    Lat.Lat,
    Lon.Lon
from
    Address_Location with (nolock)
    cross apply dbo.usf_GIS_GET_LAT(City,[State]) AS Lat
    cross apply dbo.usf_GIS_GET_LON(City,[State]) AS Lon
WHERE
    ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. SET versus SELECT when assigning variables?
  4. How important is the order of columns in indexes?
  5. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  6. How to print VARCHAR(MAX) using Print Statement?
  7. Concatenate values based on ID
  8. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  9. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  10. Splitting delimited values in a SQL column into multiple rows
  11. Is it possible to select sql server data using column ordinal position
  12. How to Replace Multiple Characters in SQL?
  13. varbinary to string on SQL Server
  14. How do I group on continuous ranges
  15. How can a LEFT OUTER JOIN return more records than exist in the left table?
  16. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  17. Dynamic SQL to generate column names?
  18. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  19. Column name or number of supplied values does not match table definition
  20. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  21. SQL Server 2005 ROW_NUMBER() without ORDER BY
  22. How to split string using delimiter char using T-SQL?
  23. How to find consecutive rows based on the value of a column?
  24. Can an INNER JOIN offer better performance than EXISTS
  25. SQL Query – Concatenating Results into One String [duplicate]
  26. How to escape square brackets inside square brackets for field name
  27. How do you copy a record in a SQL table but swap out the unique id of the new row?
  28. Cumulative Total in MS Sql server [duplicate]
  29. Memory effective way to read BLOB data in C#/SQL 2005
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment