title: Why do SQL Server Scalar-valued functions get slower?
body: <p>Why do Scalar-valued functions seem to cause queries to run cumulatively slower the more times in succession that they are used?</p>
  
  <p>I have this table that was built with data purchased from a 3rd party.</p>
  
  <p>I've trimmed out some stuff to make this post shorter... but just so you get the idea of how things are setup.</p>
  
  <pre><code>CREATE TABLE [dbo].[GIS_Location](
          [ID] [int] IDENTITY(1,1) NOT NULL, --PK
          [Lat] [int] NOT NULL,
          [Lon] [int] NOT NULL,
          [Postal_Code] [varchar](7) NOT NULL,
          [State] [char](2) NOT NULL,
          [City] [varchar](30) NOT NULL,
          [Country] [char](3) NOT NULL,
  
  CREATE TABLE [dbo].[Address_Location](
      [ID] [int] IDENTITY(1,1) NOT NULL, --PK
      [Address_Type_ID] [int] NULL,
      [Location] [varchar](100) NOT NULL,
      [State] [char](2) NOT NULL,
      [City] [varchar](30) NOT NULL,
      [Postal_Code] [varchar](10) NOT NULL,
      [Postal_Extension] [varchar](10) NULL,
      [Country_Code] [varchar](10) NULL,
  </code></pre>
  
  <p>Then I have two functions that look up LAT and LON.</p>
  
  <pre><code>CREATE FUNCTION [dbo].[usf_GIS_GET_LAT]
  (
      @City VARCHAR(30),
      @State CHAR(2)
  )
  RETURNS INT 
  WITH EXECUTE AS CALLER
  AS
  BEGIN
      DECLARE @LAT INT
  
      SET @LAT = (SELECT TOP 1 LAT FROM GIS_Location WITH(NOLOCK) WHERE [State] = @State AND [City] = @City)
  
  RETURN @LAT
  END
  
  
  CREATE FUNCTION [dbo].[usf_GIS_GET_LON]
  (
      @City VARCHAR(30),
      @State CHAR(2)
  )
  RETURNS INT 
  WITH EXECUTE AS CALLER
  AS
  BEGIN
      DECLARE @LON INT
  
      SET @LON = (SELECT TOP 1 LON FROM GIS_Location WITH(NOLOCK) WHERE [State] = @State AND [City] = @City)
  
  RETURN @LON
  END
  </code></pre>
  
  <p>When I run the following...</p>
  
  <pre><code>SET STATISTICS TIME ON
  
  SELECT
      dbo.usf_GIS_GET_LAT(City,[State]) AS Lat,
      dbo.usf_GIS_GET_LON(City,[State]) AS Lon
  FROM
      Address_Location WITH(NOLOCK)
  WHERE
      ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)
  
  SET STATISTICS TIME OFF
  </code></pre>
  
  <p>100 ~= 8 ms, 200 ~= 32 ms, 400 ~= 876 ms</p>
  
  <p>--Edit
  Sorry I should have been more clear.  I'm not looking to tune the query listed above.  This is just a sample to show the execution time getting slower the more records it crunches through.  In the real world application the functions are used as part of a where clause to build a radius around a city and state to include all records with in that region.</p>