Why do Scalar-valued functions seem to cause queries to run cumulatively slower the more times in succession that they are used?
I have this table that was built with data purchased from a 3rd party.
I've trimmed out some stuff to make this post shorter... but just so you get the idea of how things are setup.
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,
Then I have two functions that look up LAT and LON.
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
When I run the following...
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
100 ~= 8 ms, 200 ~= 32 ms, 400 ~= 876 ms
--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.