1

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?

Improve this question
1
  • Yes, but how do I do that? Can't do = nPrevResultsId = (SELECT TOP 1...) ? Commented Aug 14, 2014 at 12:30

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Will try this solution
Thank you very much. That worked much better. How come this is so much faster?
@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?
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)

Improve this answer

1 Comment

Index is not an option for me here
0

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

Improve this answer

Comments

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.