I have a complex view which is having 6 tables inner joins and max function on it.
Out of 6 tables,only one table is having 100 millions of plain data (no LOBs).
I am running one query which is like
where id in (Select id from AnotherView where dt between 'somedate' to 'someotherdate')
I verified indexes and stats for all the base tables which is used in ComplexView and all of them are less than 10% avg fragmentation.
When I run this query, taking more than 2hrs. But if I rebuild the indexes and update the stats,
its giving the result in 10 secs. Some how,after some time (in prod) its again taking to 2 hrs.
Select id,max(case )
from tab1....tab6 (all r inner joins)
group by id
By seeing the execution plan,am getting confused whether first its executing where condition or view logic.
In Generally,Where clause will considered first or view execution first?
Can any one please suggest what might be causing the perf prob..With out altering the view and with out rebuilding the indexes for every 2hrs.??
Thanks in advance
What I have tried:
For the testing purpose,I just tool the result set of
Select id from AnotherView where dt between 'somedate' to 'someotherdate'
into temp table and run the below query
Select * from ComplexView where id in (select id from #tmp)
this was running almost 30 Sec.