Well, I mean that all of them are correctly joined, I just didn't type it out in full. The "where clause" was meant to imply joins, however the tables are indeed joined in all cases via JOIN clauses in the select - including the tableD and Web_User.
The second view is the one where I mean that if you select from a view and filter it, it will be very slow. But to me, the second one is a query utilizing a view, and I am correct in saying that they can be very slow. Dino -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Frans Bouma Sent: Friday, 11 August 2006 22:32 To: [email protected] Subject: Re: [ADVANCED-DOTNET] SQL Server 2005 Xml question... > No. yes, read on :) > I had a view called "Web_User": > > Select fields from tableA, tableB, tableC where clause > > I had another view: > > Select fields from tableD, web_user where field_in_web_user = > something > > And it was extremely slow. However, doing this: > > Select fields from tableD, tableA, tableB, tableC where clause and > field_in_tableB = something > > Was vastly quicker. duh :) You use a join in the latter, and a subquery in the former. This COULD lead to different execution plans. The first query will become: SELECT fields FROM TableD, (SELECT fields from table a, tableb, tablec where clause) Web_user WHERE field_in_web_user = something your second query is completely different, as you optimized it. It will be even faster if you move the the AND clause to an INNER JOIN ON's clause I think. That doesn't mean what I said was wrong, you just compared apples with oranges (sorry) FB > > -----Original Message----- > From: Discussion of advanced .NET topics. > [mailto:[EMAIL PROTECTED] On Behalf Of Frans Bouma > Sent: Friday, 11 August 2006 22:13 > To: [email protected] > Subject: Re: [ADVANCED-DOTNET] SQL Server 2005 Xml question... > > > In my experience, that's not correct. > > so, if I have a view, CustomersView SELECT * FROM Customers > > and I have 2 queries: > A: > SELECT * FROM CustomersView > WHERE Country='USA' > > B: > SELECT * FROM (SELECT * FROM Customers) CustomersView WHERE > CustomersView.Country = 'USA' > > You're saying A is slower than B? > > Check the execution plans. Most modern RDBMS's will merge the > SQL of the view into the query first, then calculate the execution > plan and then execute it. So a view is more or less just a code > snippet. > > It will be different with indexed views which are stored > results and act as tables, there the view usage is much faster, as the > query of the view is bypassed as the results are used directly > > FB > > > > > > -----Original Message----- > > From: Discussion of advanced .NET topics. > > [mailto:[EMAIL PROTECTED] On Behalf Of > Frans Bouma > > Sent: Friday, 11 August 2006 19:13 > > To: [email protected] > > Subject: Re: [ADVANCED-DOTNET] SQL Server 2005 Xml question... > > > > > > A view is just a query, so a query utilizing a view isn't > > going to be slower than a query with the view's sql inside it. > > > > =================================== > > This list is hosted by DevelopMentor. http://www.develop.com > > > > View archives and manage your subscription(s) at > > http://discuss.develop.com > > > > > > =================================== > This list is hosted by DevelopMentor(r) http://www.develop.com > > View archives and manage your subscription(s) at > http://discuss.develop.com > > =================================== > This list is hosted by DevelopMentor. http://www.develop.com > > View archives and manage your subscription(s) at > http://discuss.develop.com > > =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
