On Fri, 26 Jul 2013 08:29:10 +0000, Poul Dige <pd@...> wrote:

> We had similar performance problems with an "order by" in a view, which

is

> quite silly if the view is optimized isolated from the whole query.
> Particularly when you want to select one single record from a view, it
> seems that it selects everything, orders it, at then returns what you

are

> looking for.
>
> We simply removed the order by in the view (which completely makes sense

-

> why should it be there at all?) and put it in the query using the view
> instead. Much snappier!

A view is not optimized in isolation as far as I am aware, the problem is
more likely that the ORDER BY forces the optimizer to ignore some of the
possible optimizations (although I can't discount the possibility that
using the view adds additional restrictions for the optimizer). There is a
reason that for example SQL Server forces you to add a TOP-clause if you
define an ORDER BY in a view. Ordering a view usually only makes sense if
you want the view itself to return a limited number of rows, and in other
cases it will usually degrade performance, because intermediate sorts are
bad for performance.

Potentially the performance is similar if the query would be constructed
by inlining the view.

Mark