2

I wrote a multi statement table valued function. But I encountered slowness in this function.

CREATE FUNCTION [dbo].[userFunc]
(   
-- Input param
)
RETURNS 
@Results TABLE 
(
    UserId BIGInt,
    FirstName  BIGINT,  
    LastName INT
)
AS
BEGIN
    INSERT INTO @Results
    SELECT UserId, FirstName, LastName
    FROM MyTable
RETURN 
END

When I investigate this issue i found the cause of the slowness. Issue is with below Insert query.

    INSERT INTO @Results
    SELECT UserId, FirstName, LastName
    FROM MyTable

But when I remove INSERT INTO @Results, the query is fast and return the result. Any idea why?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dumi
  • 1,414
  • 4
  • 21
  • 41
  • User defined functions are slow. What are you trying to do? – Gordon Linoff Nov 24 '16 at 16:26
  • 1
    *"Insert to table variable is very slow"* >> Yes. Either redesign your function to be an inline table valued function or start using temporary tables. Since this question has been asked and answered before, please read the following: [1](http://stackoverflow.com/q/1643687/243373) and [2](http://stackoverflow.com/q/10899495/243373). – TT. Nov 24 '16 at 16:29
  • I have a very big stored procedure. So i have broken it in to parts and moved some queries to user defined functions. For example, to get user data i use separate user defined function, to get department data i use separate user defined function etc – Dumi Nov 24 '16 at 16:31
  • Any idea how I use temp table in multi statement table valued function? – Dumi Nov 24 '16 at 16:32
  • 2
    Create the temporary table before you call the function and have the function insert into the temporary table instead of return a table variable. But then I would personally make that a stored procedure since you aren't returning anything. Then when the function or stored procedure is done executing, the temporary table is filled and you can work with the temporary table as you would with a table variable or a normal table. – TT. Nov 24 '16 at 16:43
  • I recall running some performance metrics and in many of them I found that inserting into the temp tables was faster than into the table variables. Worth exploring. Have you confirmed that insert into a table variable is still slow outside of a function call? What does the plan say? – ajeh Nov 24 '16 at 16:50
  • I think you need to investigate how your function is different than using an actual table variable. A function is not the same as a variable table you are inserting and then using a function to call it. It is probable you are adding extra steps...maybe even cursive steps...to your DML. – clifton_h Nov 24 '16 at 18:32

0 Answers0