2

We have a monster view that contains joins to many (many) tables in order to return the data that is required. In some cases, to get a column of information, we've have to join onto 4 tables, just to get to the value we need (the database structure isn't great, and we can't change it).

In order to improve the performance, I am extracting certain components and constructing them into their own View, in order to simplify the execution plan, and hopefully optimise the queries.

If I write a query that joins these views together, do I lose the benefit of the query optimisation for each view? i.e. do I end up back where I started by combining the views into a single select, or will it inherently perform better because they are acting atomically from one another?

Improve this question
2
  • 1
    Sounds like a real "it depends"! Have you tried CTEs - i.e. using the WITH clause? I think you'll just have to test on your own hardware and structures. Actually posting the structures (joining fields only) and record counts might just get you a better answer from someone more experienced with SQL Server than I am. p.s. not sure if SQL Server 2000 even has CTEs? Why are you using a 15 year old unsupported version of your server?
    – Vérace
    Commented Sep 15, 2015 at 9:07
  • @Vérace Dont ask. There's a legacy app kicking around that requires it to still be on 2000 (its on a 2k8R2 database but in 2k mode). We're in the process of killing it, but until it goes we can't upgrade (and there's legacy syntax that needs to be eliminated too).
    – Obsidian Phoenix
    Commented Sep 15, 2015 at 9:09

2 Answers 2

Reset to default
5

Views are just stored queries: you don't execute views, but you SELECT data out of them. When you combine multiple views joining them, you are doing nothing different from inlining the view definition in the query. In fact, that's what the optimizer does: it's called view expansion.

If your views will never be used in isolation, but always joined together instead, I suggest that you stop tuning them individually, but you concentrate on the overall query that will use them. The optimizer will expand the views and generate a plan that will have nothing to do with the plan used when selecting from the individual views.

On the other hand, what makes your individual views faster (well-thought T-SQL code, indexes, statistics) won't probably hurt the performance of the overall query. Just don't expect the resulting plan to be a simple merge of the individual views' plans.

Improve this answer
2
  • our other option was to query View1, and then (in code) call View2 to get the extra data. Athough we'd probably need to do that in blocks as it can potentially be a lot of data we're pulling out.
    – Obsidian Phoenix
    Commented Sep 15, 2015 at 9:19
  • Temporary tables could help you here probably.
    – spaghettidba
    Commented Sep 15, 2015 at 9:21
0

I posted a related question about views versus queries yesterday. The advice offered was to compare the query plans of linking to a view to linking to linking to the actual tables. The result - the plans were identical - so based on that I don't see a performance gain from using a view.

You may be able to gain performance if you're able to use an indexed view, but there are a few restrictions on what you can make an indexed view on so it may or may not help you out, could be worth you investigating it though.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.