SQL Table Valued Function in Select Statement

Question

SQL is not my best thing but I have been trying to optimize this stored procedure. It had multiple scalar-valued functions that I tried to change to table-valued functions because I read in many places that it's a more efficient way of doing it. And now I have them made but not real sure how to implement or if I maybe just didn't create them correctly.

This is the function I'm calling.

Alter FUNCTION [IsNotSenateActivityTableValue]
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
returns @T table(result varchar(max))
as
begin
DECLARE @result varchar(max);
    declare @countcodes int;


declare @ishousebill int;

select @ishousebill = count(billid)
from BillMaster
where BillID = @BillID and Chamber = 'H'

If (@ishousebill = 0)
begin


SELECT @countcodes = count([ActivityCode])
      FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode

if (@countcodes = 0)
begin
    set @result = 'test'
   end
  else
     begin
        set @result = 'test2'
    end
end
else
begin
    set @result = @TextToDisplay
end
RETURN 

END

And this is how I was trying to call them like this. I would prefer just being able to put them in the top but really anything that works would be good.

SELECT distinct       
      ActionDates.result as ActionDate
      ,ActivityDescriptions.result as ActivityDescription        
  FROM BillWebReporting.vwBillDetailWithSubjectIndex as vw
  left outer join [BillWebReporting].[HasHouseSummary] as HasSummary on vw.BillID = HasSummary.BillID
  outer APPLY dbo.IsNotSenateActivityDateTableValue(ActivityCode,vw.BillID,[ActionDate]) ActionDates    
  OUTER APPLY dbo.IsNotSenateActivityTableValue(ActivityCode,vw.BillID,[ActivityDescription]) as ActivityDescriptions

Tag says mysql, but this looks very much like T-SQL and SQL Server. — RBarryYoung
– RBarryYoung, Commented Oct 11, 2013 at 14:44
Does your function actually work? Where do you ever insert into @T? Is it only ever meant to return a single row? — Aaron Bertrand
– Aaron Bertrand, Commented Oct 11, 2013 at 14:49
Also, the most efficient type of table-valued function is an inline table-valued function (which only has a RETURN (SELECT ...); and none of these other bits of code). A multi-statement table-valued function, like the one you're trying to write, is actually prone to a lot of the same performance issues you're trying to avoid. — Aaron Bertrand
– Aaron Bertrand, Commented Oct 11, 2013 at 14:51
Ok ya I did not having it inserting into t was the problem its working now, but i see some people not liking it. Is there a better way to go about it? — Ryan Schlueter
– Ryan Schlueter, Commented Oct 11, 2013 at 14:59
Please refrain from putting words in people's mouths. Nobody said they didn't like your code. We're just trying to help, remember? — geomagas
– geomagas, Commented Oct 11, 2013 at 15:01

Aaron Bertrand · Accepted Answer · 2013-10-11 15:01:27Z

3

Getting a count just to see if at least one row exists is very expensive. You should use EXISTS instead, which can potentially short circuit without materializing the entire count.

Here is a more efficient way using an inline table-valued function instead of a multi-statement table-valued function.

ALTER FUNCTION dbo.[IsNotSenateActivityTableValue] -- always use schema prefix!
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
RETURNS TABLE
AS
  RETURN (SELECT result = CASE WHEN EXISTS 
    (SELECT 1 FROM dbo.BillMaster 
     WHERE BillID = @BillID AND Chamber = 'H'
  ) THEN @TextToDisplay ELSE CASE WHEN EXISTS 
    (SELECT 1 FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' 
      and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode
  ) THEN 'test2' ELSE 'test' END
  END);
GO

Of course it could also just be a scalar UDF...

ALTER FUNCTION dbo.[IsNotSenateActivityScalar] -- always use schema prefix!
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @result VARCHAR(MAX);

  SELECT @result = CASE WHEN EXISTS 
    (SELECT 1 FROM dbo.BillMaster 
     WHERE BillID = @BillID AND Chamber = 'H'
  ) THEN @TextToDisplay ELSE CASE WHEN EXISTS 
    (SELECT 1 FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' 
      and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode
  ) THEN 'test2' ELSE 'test' END
  END;

  RETURN (@result);
END
GO

answered Oct 11, 2013 at 15:01

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

4 Comments

Ryan Schlueter Over a year ago

It was in a scalar udf but i took it out to try speeding up the performance. You think if i tried this in the function it would improve performance enough to be useable.

Aaron Bertrand Over a year ago

I doubt your choice of scalar UDF or table-valued UDF is going to make a noticeable difference on performance - it is probably due to much bigger hitters - poor / no supporting indexes, bad stats, insufficient memory, etc. When you run this query, generate an actual execution plan, and see if anything stands out (like very costly scans, missing index recommendations, etc).

Charles Bretana Over a year ago

Aaron, In Sql Server, using an inline UDF is significantly faster than a multi-statement UDF, and in cases where the UDF is called multiple times (say once for each row of an outer query in which it is embedded), it can make a huge difference. Is this not the case in mySQL?

Aaron Bertrand Over a year ago

@Charles not sure what this has to do with MySQL at all. I agree with you that an inline UDF is better (and that's why I suggested and wrote an inline UDF version), I just don't think it will magically fix all of the OP's performance problems with this query. It may make it take 30 seconds instead of 34 seconds, but not take it the rest of the way. I could be wrong but I doubt that the performance issue that drove the OP here results from the wrong function type.

geomagas · Accepted Answer · 2013-10-11 14:55:25Z

0

Table-valued functions return a table, in which, like any other table, rows have to be inserted.

Instead of doing set @result = ....., do:

INSERT INTO @T (result) VALUES ( ..... )

EDIT: As a side note, I don't really understand the reason for this function to be table-valued. You are essentially returning one value.

answered Oct 11, 2013 at 14:55

geomagas

3,2801 gold badge19 silver badges28 bronze badges

4 Comments

Ryan Schlueter Over a year ago

Yea that was the problem went back and looked after I saw some of there comments. What would be the better way to do it. I was just going off the research of alternatives to use to speed up Scalar valued functions.

geomagas Over a year ago

Returning a single value implies a scalar function. Making it table-valued would be an unnecessary complication -- maybe insignificant, granted, but still unnecessary. I don't really know which one is faster, but I'd guess the difference would be insignificant. Performance issues should be addressed towards the optimization of the rest of the objects used inside the function.

Charles Bretana Over a year ago

Except that in order to make it an inline UDF, it has to be table-valued, not scalar, and inline UDFs are much more performant in scenarios where they are being executed multiple times.

geomagas Over a year ago

I never had the chance to compare these approaches thotoughly, performance-wise. Thank you for your feedback. Nevertheless, I still think fine-tuning the objects that the function uses should be the prime target of an optimization process, as this would benefit all scenarios.

Charles Bretana · Accepted Answer · 2013-10-11 15:35:27Z

First of all UDFs generally are very non-performant. I am not sure about MySQL, but in Sql Server a UDF is recompiled every time (FOR EACH ROW OF OUTPUT) it is executed, except for what are called inline UDFs, which only have a single select statement, which is folded into the SQL of the outer query it is included in... and so is only compiled once.

MySQL does have inline table-valued functions, use it instead... in SQL Server, the syntax would be:

CREATE FUNCTION IsNotSenateActivityTableValue
(
@ActivityCode int,
@BillId int,
@TextToDisplay varchar(max)
)
RETURNS TABLE 
AS
RETURN 
(
Select case 
   When y.bilCnt + z.actCnt = 0 Then 'test'
   when y.bilCnt = 0 then 'test2'
   else @TextToDisplay end result
From (Select Count(billId) bilCnt
      From BillMaster
      Where BillID = @BillID 
         And Chamber = 'H') y
    Full Join 
     (Select count([ActivityCode]) actCnt
      From [HouseCoreData].[dbo].[ActivityCode]
      Where ActivityDescription not like '%(H)%' 
         And ActivityType = 'S'
         And [ActivityCode] = @ActivityCode) z

)
GO

Collectives™ on Stack Overflow

SQL Table Valued Function in Select Statement

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related