Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Unanswered: Embedded Select vs. UDF

    Why is using an embedded SELECT statement faster than using an embedded UDF that has the same exact SQL code within?

    Example (not syntax corrected)
    Ex1:
    SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)

    is much faster than something like

    Ex2:
    SELECT myValue1, myValue2 FROM myTable WHERE aValue = (dbo.FN_myUDF(@vmyIndex))

    Given that dbo.FN_myUDF has the same code as the embedded select in the first example.

    TIA,

    KB

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually I'm getting quite an opposite result:

    select * from authors where au_id = (select au_id from authors where au_lname = 'Dull')

    vs.

    create function dbo.fn_get_au_id (
    @lname varchar(50) ) returns char(11)
    as begin
    declare @au_id char(11)
    select @au_id = au_id from authors where au_lname = @lname
    return @au_id
    end
    go

    +

    select * from authors where au_id = dbo.fn_get_au_id('Dull')

    The first results in: Table 'authors'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0.

    The second yields: Table 'authors'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps because SQL Server is able to take your poorly written code:

    SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)

    ...and transform it into the much more efficient:

    SELECT myValue1, myValue2
    FROM myTable
    INNER JOIN someTable on myTable.aValue = someTable.Value
    WHERE someTable.myIndex = 800

    ...prior to executing it. The optimizer will streamline your statement whenever it can, and thus make use of any indexes on the tables and only make one pass through the subtable. Strictly following your code logic would result in a pass through someTable for every record in myTable, which is also what occurs when you call the UDF. The compiler wants none of that nonsense and fixes your code before executing it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, I just used the same sample code as you (Mr. Lindman) provided and converted it to my previously posted comparative case...and...hmmmmm...It is actually WORSE than the other two:

    select * from authors a
    inner join authors b
    on a.au_id = b.au_id
    where b.au_lname = 'Dull'

    results in: Table 'authors'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.

    In fact, even JOIN hints don't make any difference. Ironic, isn't it?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My bad. I was thinking he had the function call in his select clause. In the WHERE clause the UDF is only executed once. I get faster results with the UDF as well, though with a larger dataset I get identical results and execution plans using either the subquery method or the join method.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •