Order of query execution on where and view in SQL server

See more:

SQL

SQL-Server-2014

Hi All,

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

Select * 
  from ComplexView 
  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.

ComplexView:

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.