Post reply

Understanding Inline TVFs

  • December 6, 2012 at 1:03 pm

    #271576

    I have only recently stumbled upon inline table valued functions. From all my reading, they are considered more efficient than multi line table valued functions (in general). There is one thing I do not understand...

    If you use CROSS APPLY with an itvf, how often is the logic in the function actually executed?

    For example, if there are 200,000 rows in the left table does the code in the itvf get executed for each row (rbar)? Or is the data cached in some way that would save execution?

    I ask because I have a query that conatined a scalar function. Query execution took over 1 hour. I have tried to rewrite it as an itvf and performance isn't appearing to be much better (30 minutes and counting). I'm starting to think a multi line tvf may be the solution?

  • December 6, 2012 at 1:17 pm

    #1566333

    itvfs are applied once to the set, and not against each row.

    an inline TVF will always out perform a multi line TVF;

    ITVF's are really just a special JOIN type in my opinion...they are quick.

    a lot of us LOVE to play with ITVF's ; if you care to post it, we could offer advice.

    if something using an ITVF is running slow, it might not be the ITVF that is the problem, but look at the joins...is there an implicit conversion going on? maybe something like NVARCHAR <--> VARCHAR, int <--> varchar, datetime <--> varchar or something that is the real cause of the performance hit?

    the actual execution plan , if you can post the xml as a .sqlplan, would give us everything we need to help you identify the performance pain points on your query.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 6, 2012 at 2:06 pm

    #1566350

    This is a pretty crazy proc (new job and I was given a list of long-running queries and told to see if I could speed any up). I saw pretty quickly that a scalar function (called 4 times) was the likely culprit...tested and confirmed. Without the scalar functions, it returns around 150,000 rows in about 2:30 (acceptable for this particular query).

    I had to use CTEs to make the table function inline, but it runs fairly quickly (less than 15 seconds...I was told this is ok) when testing with parameters that return roughly 14,000 rows. When I join it to the temp table in the proc (150,000 rows), though, it runs forever (cut it off at around 35 minutes).

    I'll see if I can get an execution plan tomorrow...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply