Optimizing SQL query using function

Question

I have a stored procedure which is going really slow. I ran the SP and looked at the execution plan and was able to see what was taking so long time.

The part that is slow:

DECLARE
 @id int 
,@date datetime
,@endDate datetime 


SELECT   @id = 3483
        ,@date = DATEADD(DAY, -10, GETDATE())
        ,@endDate = GETDATE()


SET NOCOUNT ON

SELECT   *
        ,prevId = dbo.fnGetPrevId(id)   
FROM    dbo.table WITH(READUNCOMMITTED)

And the part in this query that is slow is where I call the function dbo.fnGetPrevId.

dbo.fnGetPrevId:

DECLARE  @prevId int

SELECT  TOP 1 @prevId = t2.id 
FROM    dbo.table2 AS t2 WITH(READUNCOMMITTED)

RETURN @prevId

Is this possible to rewrite for better performance without create index or something like that to table?

Yes, but how do I do that? Can't do = nPrevResultsId = (SELECT TOP 1...) ? — MrProgram, Aug 14 '14 at 12:30

score 3 · Accepted Answer · edited Sep 06 '18 at 20:04

3

You could use a sub-query instead of the scalar valued function.

// ...

,prevId = (
 SELECT  TOP 1 x.id
 FROM    dbo.table AS x WITH(READUNCOMMITTED)
 WHERE 1 = 1)

// ...

In most cases, it's best to avoid scalar valued functions that reference tables because they are basically black boxes that need to be ran once for every row, and cannot be optimized by the query plan engine.

edited Sep 06 '18 at 20:04

MrProgram

5,044
13
58
98

answered Aug 14 '14 at 12:32

Tim Schmelter

450,073
74
686
939

Will try this solution – MrProgram Aug 14 '14 at 12:41
Thank you very much. That worked much better. How come this is so much faster? – MrProgram Aug 14 '14 at 12:53
@krillezzz: as mentioned, the function is a _black box_ for the optimizer, the engine may not use indexes or other optimizations. Here is a related question: [Why is a UDF so much slower than a subquery?](http://stackoverflow.com/questions/510743/why-is-a-udf-so-much-slower-than-a-subquery) – Tim Schmelter Aug 14 '14 at 13:00

score 1 · Answer 2 · answered Aug 14 '14 at 12:31

1

First thing, you should cut the function all together and inline the query. Which from what I see it would be fairly simple. Or if you want to preserve a function there use a table valued function. For both check:

http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

Second, the best results in optimizing you will get with building an index (HUGE improvement)

answered Aug 14 '14 at 12:31

Dumitrescu Bogdan

7,127
2
23
31

Index is not an option for me here – MrProgram Aug 14 '14 at 12:42

score 0 · Answer 3 · edited Sep 06 '18 at 20:08

0

Start by creating an index on table(id, id2, id3....)`.

This may solve your problem. Otherwise, try cross apply:

FROM    dbo.table1 AS x WITH(READUNCOMMITTED) cross apply
        (SELECT  TOP 1 x2.id
         FROM    dbo.table2 as x2 WITH(READUNCOMMITTED)
          WHERE x.id= x2.id
            AND x.id2= x2.id2
            AND x.id3= x2.id3
         ORDER BY x.Date DESC
        ) x

edited Sep 06 '18 at 20:08

MrProgram

5,044
13
58
98

answered Aug 14 '14 at 12:34

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · edited Sep 06 '18 at 20:06

Your UDF is recompiling for every row it runs against. To stop that, Make the function a Table valued inline function, as like this:

create function dbo.fnFunction:
( list of parameters here)
Returns Table
As
  Return 
   (
    SELECT TOP 1 id
    FROM  dbo.table WITH(READUNCOMMITTED)
    WHERE id= @id
       AND id2= @id2
       ...
   ORDER BY date DESC
  )

This eliminates the recompile for every row, as the SQL in an inline table valued function is included in the sql of the query it is used in, and stored in the cache plan for entire query ONCE and only once. To use the results, you need to treat trhe results as you would a table. Simply join to it in your outer query check this link

Optimizing SQL query using function

4 Answers4