CODEWITHSUNDEEP
sql-server-2008database-performance
jreed121
jreed121

Reputation: 2097

SQL Server performance issue: Why simple SQL statements and a UD scalar-valued function appear to be expensive

We've been experiencing some severe performance issues on our SQL 2008 r2 DB. When we run the Activity Monitor in SQL Server Management Studio, and a SP that returns who/what is active, it shows the following three transactions as being very expensive:

Query 1:

SET @DateMonth = '0' + @DateMonth

Query 2:

SET @DateMonth = CAST(datepart(mm, @TheDate) AS VARCHAR(2))

Function:

CREATE FUNCTION [dbo].[DateToNGDate] (@TheDate datetime)
RETURNS VARCHAR(10)
AS
BEGIN
            DECLARE @DateYear VARCHAR(4)
            DECLARE @DateMonth VARCHAR(2)
            DECLARE @DateDay VARCHAR(2)

            SET @DateYear = CAST(datepart(yyyy, @TheDate) AS VARCHAR(4))

            SET @DateMonth = CAST(datepart(mm, @TheDate) AS VARCHAR(2))
            IF (LEN (@DateMonth) = 1) SET @DateMonth = '0' + @DateMonth

            SET @DateDay = CAST(datepart(dd, @TheDate) AS VARCHAR(2))
            IF (LEN (@DateDay) = 1) SET @DateDay = '0' + @DateDay

            RETURN @DateYear+@DateMonth+@DateDay
END

That last one comes back like that, but i'm pretty sure it isn't creating the function (it already exists), rather it is just running it. It is also the one that comes up the most as appearing to be a performance killer (it's used throughout our code).

I'm sure these aren't what is actually causing the problem, but why would they appear as they are?

Upvotes: 1

Views: 75

Answers (3)

ChandanJha
ChandanJha

Reputation: 99

I like Pieter Geerkens answer and I hope it has resolved your problem. The scalar function will return results for each of the matching rows wherever it is being used. So it is something similar to cursors. A table valued function will accept a set and return a set.

To verify the I\O changes between both processes, management studio might not help but on your own machine you can put a small trace to see what magnitude of performance you get.

Cheers!!

Upvotes: 0

jazza1000
jazza1000

Reputation: 4247

Scalar UDFs are very slow if your select statement is returning a lot of rows, because they are going to be executed once for every row that is returned. Maybe you can rewrite as an inline function as per this link inline UDFs

Upvotes: 1

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

Scalar-valued functions are a known performance issue in SQL Server. One option is to define your function like this:

CREATE FUNCTION [dbo].[DateToNGDateX] (@TheDate datetime)
RETURNS table as return 
(
    select
        cast(
              CAST(datepart(yyyy, @TheDate) AS VARCHAR(4)) -- DateYear 
            + right('0' + CAST(datepart(mm, @TheDate) AS VARCHAR(2)),2) -- DateMonth 
            + right('0' + CAST(datepart(dd, @TheDate) AS VARCHAR(2)),2) -- DateDay 
        as varchar(30)) as Value
)

and reference it like this:

select Value from [dbo].[DateToNGDateX] ('20140110');

However, for the specific functionality you desire, test this as well:

select convert(char(8), cast('20140110' as date), 112);

which will return a date formatted as yyyymmdd.

Upvotes: 2

Related Questions