Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel