I can't really mock this without knowing how the view is structured. It is not possible to mock a query plan that involves a view without knowing the components of the view. Can you give details of the makeup of the view
Can you post the plan for your queries, such as:
set showplan_text on
--set statistics profile on
--set statistics profile off
set showplan_text off
The showplan one gives you estimated, the profile one gives you actual plan. That will be very helpful in this.
When I executed queries (for generating estimated and actual plans), suddenly problematic one executed much faster.
Most probably, problem was about server's hardware performance, like insufficient RAM, at the moment of query execution.
My assumption is based on the fact that problematic query is using subquery, which by many views, is using more memory then query with temp table.
Anyway, I am sending you a link to XLS file with all statistics (of query with subquery) you asked, in case that something else could cause strange behavior and can be seen from this.
Thank you for your efforts
Without both to compare it is really impossible to say too confidently, but there are a couple of really large looking table scans (Clustered Index Scan = ordered table scan) followed by a hash match join. This kind of operation can be greatly affected by hardware performance/contention, as the hash match executes faster with more ram.
Comparing the plans might shed some light on it, but if it runs adequately now... :)
First of all, thank you all for your replies.
Rob, will you please tell me, what did you mean by "materialise table".
Did you mean that database would move view from memory to hard drive
And, if that is true, would db engine make that table within tempdb space (as temp tables are) or in original database space
ok.Thanks. Then it works as I expected.