troubleshooting Question

When is Multi-Valued UDF faster than inline UDF?

Avatar of yechan
yechanFlag for United States of America asked on
Microsoft SQL Server 2008
8 Comments2 Solutions330 ViewsLast Modified:
Hi,

I have rewritten an UDF from inline to multi-valued UDF and was able to reduce the query time from ~20seconds down to ~2seconds.  All the literature indicates that inline are generally faster than multi valued UDF's.  

The original inline query looked something like the following:

create function udf_inline
(
   @p1 int,
   @p2 datetime
)
returns table
as
return

select
    *
from
      (
            select
                         *
            from
                  (
                        select
                              *
                        from
                              tableA
                        where
                              col1 = @p1
                              and col2 = @p2
                  )
      )
            

I have rewritten the query to use multi lined UDF


CREATE FUNCTION dbo.MV_UDF
(
   @p1 INT,
   @p2 DATETIME
)


RETURNS @myTable TABLE
(
   colA varchar(50),
   colB varchar(50),
   etc...
)
AS
BEGIN


insert into @myTable
select
    *
from
      (
            select
                         *
            from
                  (
                        select
                              *
                        from
                              tableA
                        where
                              col1 = @p1
                              and col2 = @p2
                  )
      )
RETURN


Why the multi-valued UDF runs faster I do not know.  Anyone can provide some insight?

thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros