views with joins – how do they work/perform? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

views with joins – how do they work/perform?

If I have a view (vwTest) defined as:<br /><br />select a.fld2a as fOne, b.fld2b as fTwo from table1 a inner join table2 b on a.fld1 = b.fld1<br /><br />…and the result of this view is hundreds of thousands of rows,<br /><br />when i execute the view using:<br /><br />select * from vwTest where fOne = x<br /><br />…does sql server have to build the whole view with the hundreds of thousands of rows before filtering down to the ones that have fOne = x?<br /><br />I know I could make this into a stored proc that takes x as a parameter and filters the original select statement, but in my case, my views are simply denormalizing my OLTP tables, and the filter criteria could vary.<br /><br />Also, in my situation, this question applies to how various clients execute this kind of statement (Query Analyzer, ADO, Crystal Reports’ OLEDB driver), but I understand if that’s beyond the scope of this forum.<br /><br />I’m just a little unclear about this. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Thanks!<br />Gary
Yes simple views are materialised at run time whereas indexed view are already materialized hence the perf difference.