> 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.
True, in the case where you use a view in query A and optimize the
whole statement with the knowledge of what's INSIDE the
view in query B, then yes, B will likely be faster.
FB
>
> 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
>
>
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
