Nested views

When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?

When you say "4 levels deep" do you mean 4 joins? you might want to test it out. There will be an overhead of creating a temp table, inserting data into it, followed by a SELECT from the temp table. If your report will be run by thousands of users simultaneously you might even see a degradation in performance due to tempdb contention.

|||

By 4 levels deep I mean View1 is a query of 6 tables and several joins, then view2 use view1 with some more tables and joins or calculations etc etc.

Basically the end result in view4 is too complex to write in 1 query or 1 view so you go as far as you can in view1, then expand that result using view2 etc.

So any ideas on performance??

|||

Off the top of the head, since its not a simple query, I cant think of any, so your best bet is to test it out quickly. You can fire up profiler or use SET STATS IO ON and some others like TIME etc and see if there is any improvement/degradation in performance.