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!

Poul

> -----Oprindelig meddelelse-----
> Fra: firebird-support@yahoogroups.com [mailto:firebird-
> support@yahoogroups.com] På vegne af Alexandre Benson Smith
> Sendt: 25. juli 2013 20:03
> Til: firebird-support@yahoogroups.com
> Emne: Re: [firebird-support] Query optimization help
>
> Em 25/7/2013 10:53, Kevin Donn escreveu:
> > On Thu, Jul 25, 2013 at 8:03 AM, Alexandre Benson Smith <
> > iblist@...> wrote:
> >
> >> Em 25/7/2013 07:54, fabianoaspro@... escreveu:
> >>> Just remove any view you use in your statement and try again.
> >>> Fb is very slow when resolving queryes that use views joining other
> >> tables
> >> Can you give a real life example of that ????
> >>
> > Alexandre, if my situation qualifies as a "real life example" I'd be
> > happy to send you a development database to look at. Contact me
> directly.
> >
> > kd
> >
> >
>
> The fact that your query is slow using views doent mean that it will be fast
> without the views....
>
> It could be possible that your query will be fastar if you use direct tables, but
> if you just translate the views into a single select, I don't think it will be any
> faster.... I completely rewrite of it using tables is another thing....
>
> see you !
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item on the main
> (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ++++++++
> Yahoo! Groups Links
>
>
>