CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012stored-functions
user990016
user990016

Reputation: 3378

sql scalar-valued functions incredibly slow

SQL Server 2012 - I have a view (complicated) and one of the columns needs to have anything non-numeric stripped out. The following works to a point;

STUFF(dbo.campaign_tracking_clicks.tt_cpn, 1, PATINDEX('%[0-9]%', dbo.campaign_tracking_clicks.tt_cpn) - 1, '') AS emailerid

I get an error if anything but numbers are at the end of the value.

I have a scalar-valued function'

/****** Object:  UserDefinedFunction [dbo].[KeepNumCharacters]    Script Date: 10/11/2016 1:05:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[KeepNumCharacters](@Temp VarChar(100)) Returns VarChar(100)
 AS
 Begin    
 While PatIndex('%[^0-9]%', @Temp) > 0  
 Set @Temp = Stuff(@Temp, PatIndex('%[^0-9+]%', @Temp), 1, '')    
  Return @TEmp
 End

I'm using;

dbo.KeepNumCharacters(dbo.campaign_tracking_clicks.tt_cpn) AS emailerid

But, it's taking a very long time to execute. I've searched and searched but without finding an alternative.

Upvotes: 1

Views: 984

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Yes, scalar user-defined functions often make queries slow. Sometimes very slow. See for example T-SQL User-Defined Functions: the good, the bad, and the ugly.

In your case I don't see how to rewrite the scalar function into an inlined table-valued function.

One option is to add an extra column to your table that would hold the result of your scalar function calculations. You can write a trigger that would keep its content in sync with the main column as the main column changes.

It will slow down the updates and inserts, but it will speed up your SELECT queries.

Upvotes: 1

Related Questions