Multi-statement table valued function and inefficient Excecution Plan

16 May,2013 by Jack Vamvas

Working with Multi-statement table valued function can cause the SQL Optimizer to not estimate accurately the amount of rows. The SQL Optimizer cannot \ doesn’t know the amount of rows ,  building the execution plan on guesswork .    This can result in an  inefficient execution plan causing the Estimated number of rows = 1

Similar problems are experienced when working with Table Variables or Recursive CTEs. Read up on SQL Table variable and temporary table

This basic example illustrates the problem. Below the code , I’ve included the execution plan from Test 1 (using the select statement calling function)  and Test 2 ( a straightforward SELECT statement)

In Test 1 – the estimated Rows are 1 and in Test 2 – the estimated rows are 999. 

Avoid using Multi-statement table valued function  in these situations. Focus on creating code which allows the SQL Optimizer to make an accurate estimate .

 

use master
go 
--create a table to store the values
create table jvtest
(ID INT, 
values_insert nvarchar(100)
) 
--add rows to the table
declare @cnt INT
SET @cnt = 0 
while @cnt < 1000
begin 
  INSERT INTO jvtest 
  select @cnt, @cnt
set @cnt = @cnt + 1
end 

--create a multi statement table valued function
CREATE FUNCTION dbo.estimatedRows(@ID INT)
RETURNS @IDS TABLE
(ID    INT         NOT NULL,
 values_insert     NVARCHAR(100)         NOT NULL
)
AS
BEGIN
    INSERT INTO @IDS
    select ID,values_insert from jvtest where ID > @ID
    RETURN
END
GO


--Test 1 : execute select using the multi table valued function
select * from dbo.estimatedRows(0)

--Test 2 : execute a standard select statement
select ID,values_insert from jvtest where ID > 0


--drop the table
drop table jvtest

 Multi_table

Read More

 

 

SQL Execution Plan - Comparing Estimated Rows and Actual Rows

Performance Tuning - Save hundreds of hours

How to troubleshoot a slow running query - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Multi-statement table valued function and inefficient Excecution Plan


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer