The Basics of iTVFs

  • Jeff Moden

    SSC Guru

    Points: 994266

    The Dixie Flatline - Friday, January 11, 2019 8:31 PM

    Jeff,  you can write an ITVF that reads from a view, including an indexed view, not just tables.   In fact, if the function references multiple tables and a suitable indexed view exists, the ITVF may use it automatically.     

    The point is that sometimes indexed views across multiple tables can save time and i/o by eliminating joins altogether, and that inline table functions can take advantage of indexed views.    ITVFs and indexed views aren't mutually exclusive.   They play nicely together, with the indexed view providing the structure and the ITVF providing control through parameters.      I hope this is a better explanation.

    Oh, I get all that. Still, an indexed view is a materialization of data and, unless the view is doing data reduction through aggregates, it's not much better than using coving indexes because that's what an indexed view kind of is.  As with all else in SQL Server, "It Depends".

    The only thing that I don't agree with is the part where a function may automatically pick to use an indexed view instead of the tables that have been cited in the code.  I've heard of that "feature" before (and the DTA even has such an option built into it) and I suppose that optimizer could chose to use an index from a materialized view, but I've never seen anyone be able to prove it, especially where the view provides materialized aggregation.  Do you have an example or a URL that has such an example handy?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • The Dixie Flatline

    SSC Guru

    Points: 53197

    Jeff Moden - Saturday, January 12, 2019 7:13 AM

    The only thing that I don't agree with is the part where a function may automatically pick to use an indexed view instead of the tables that have been cited in the code.  I've heard of that "feature" before (and the DTA even has such an option built into it) and I suppose that optimizer could chose to use an index from a materialized view, but I've never seen anyone be able to prove it, especially where the view provides materialized aggregation.  Do you have an example or a URL that has such an example handy?

    No, but it does on my desktop, which is running SQL 2016.   I changed my text to say a function may use it because at first I thought an ITVF would have to reference the view explicitly.  I guess it shouldn't be surprising:  an indexed view is just another table structure.  I'll test it at work next week on 2012 and get back to you.

    One thing to be clear on.   All of our indexed views involve multiple tables.   They aren't aggregations.   We pay the price to avoid some expensive joins in big searches but fortunately the rows in the tables involved are fairly static once they're inserted.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • n.dreyer

    Grasshopper

    Points: 24

    Thank you for all the replies,

    So to summarize, I should have a look at my databases and identify the most expensive views. Irrespective if they are indexed or not. These will more likely be the ones where the source tables have a lot of insert / update /delete activity on them. Then do a test run to see if an iTVF is not a better and cleaner solution and substantially less expensive on resources. Also, if you do not trust your developers and limit them a bit more, sell them the idea of using parameters rather than a view with a where clause 😉
    I must admit, what I have found with views in the past is exactly as Jeff says, in some instances it uses unnecessary space where a "stored parameterized query" aka iTVF would have been a better solution. Then @The Dixie Flatline's approach might even be a good migration from an indexed view to an iTVF to eliminate major optimization path changes as a first iteration. Then one by one start removing the view from the iTVF's code and replace it with the original JOINs that made up the view, using only the required columns for that function's expected result and test the resource utilization.

    I do have some new projects lined up and I am definitely adding iTVF to my list for implementation. I am 100% convinced that there will be instances where an iTVF makes more sense. Honestly, don't quote me on this, but I am considering aligning this with my C# function models and controllers as I see a synergy between iTVFs and the functions in the code calling it. Interesting.

  • The Dixie Flatline

    SSC Guru

    Points: 53197

    n.dreyer - Monday, January 14, 2019 2:24 AM

    Thank you for all the replies,

    So to summarize, I should have a look at my databases and identify the most expensive views. Irrespective if they are indexed or not. These will more likely be the ones where the source tables have a lot of insert / update /delete activity on them. Then do a test run to see if an iTVF is not a better and cleaner solution and substantially less expensive on resources. Also, if you do not trust your developers and limit them a bit more, sell them the idea of using parameters rather than a view with a where clause 😉
    I must admit, what I have found with views in the past is exactly as Jeff says, in some instances it uses unnecessary space where a "stored parameterized query" aka iTVF would have been a better solution. Then @The Dixie Flatline's approach might even be a good migration from an indexed view to an iTVF to eliminate major optimization path changes as a first iteration. Then one by one start removing the view from the iTVF's code and replace it with the original JOINs that made up the view, using only the required columns for that function's expected result and test the resource utilization.

    I do have some new projects lined up and I am definitely adding iTVF to my list for implementation. I am 100% convinced that there will be instances where an iTVF makes more sense. Honestly, don't quote me on this, but I am considering aligning this with my C# function models and controllers as I see a synergy between iTVFs and the functions in the code calling it. Interesting.

    Nico, 

    Couple of points to clarify, from my posts:     
    (1) ITVFs are treated exactly like views in that they are just predefined queries incorporated into the query execution plan.   With parameters of course.
    (2) ITVFs cannot be indexed, they can only take advantage of existing indexes. 
    (3) We did NOT migrate from an indexed view,  our ITVFs just take advantage of the index structure underlying the indexed view.    Kill the index and you kill performance.

    Best of luck with your changes.

    Bob

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 4 posts - 16 through 19 (of 19 total)

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