January 12, 2019 at 7:13 am
The Dixie Flatline - Friday, January 11, 2019 8:31 PMJeff, 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
Change is inevitable... Change for the better is not.
January 12, 2019 at 10:54 pm
Jeff Moden - Saturday, January 12, 2019 7:13 AMThe 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
January 14, 2019 at 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.
January 14, 2019 at 9:56 am
n.dreyer - Monday, January 14, 2019 2:24 AMThank 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
June 27, 2023 at 2:28 am
I would see a materialized view being handy in a data warehouse environment where the data is pre-made and not expected to change during some amount of time. When it comes to a high transaction OLTP environment, an iTVF is to me a good option if you have complicated calculations that depend on a variable input. This is after all still a 'function' than can be used as a 'off label' substitute for a view.
I do wonder, I think the INNER JOIN applicaiton (one fixed set) would provide faster results as opposed a APPLY operator where you have to run the calculation for every row (dynamic output). So I wonder here of performance benefit if we use APPLY.
----------------------------------------------------
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply