executing a query using 2 DB views - inner joined takes long
I have 2 DB views. Executing first DB view on its own takes 15 seconds. Executing 2nd DB view takes 12 seconds. But when I write a query inner joining these 2 views on a key and where class on a date range, it takes 1:50 mins. If I omit where clause it takes 40 seconds. I tried creating index on start date as well as end date on the source table. It did not help. What can I do to investigate the problem? I am using SQL Server 2008 R2. thanks in advance.
Without knowing details about the views its hard to tell you what exactly causes the longer execution time. Firs you should take a look on the query plan and start from here to optimize the query. Another source of problems can be out of date statistics.
In general, joining one view to another, or nesting views inside of each other, is a bad practice for TSQL statements. It leads to very bad execution plans. The optimizer goes through three basic steps (there's actually more to it than what I'm listing) that are involved with how much time and effort it's going to allocate to itself to try to get a good execution plan. Joining one view to another impacts all of these. First, it has to break down the views into their component parts and identify every table involved in both views, whether you're referencing anything on that table or not. Then, it goes through a process of simplification where it tries to identify which parts of the views you're not using and eliminates them from the query. But because you've now joined one view to another, this step has suddenly gotten much harder and it's likely that it won't be able to achieve a good simplification. Then, finally it tries to come up with a good execution plan. But, because it wasn't able to remove objects during simplification, you're going to be attempting to optimize a much bigger set of tables and it's much more likely that the optimizer will just timeout. The best thing you can do is to rewrite the query to just bring back what you need without reference the views. Then, if you still have performance problems, you look at the execution plan to figure out what's going wrong.
Thanks for the replies. I don't have a choice but to create DB views for reporting purposes. Views are created accross databases ( min. 2 and max 3 DBs). So indexed views ae not the option. I have already tried reading the complex execution plan.