Re: [firebird-support] Re: order by takes too long

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

> Hello,
> 
> ok, I guess I need to create a DESC index on TIMESTAMP of MY_TABLE.
> I could verify this speeds it up from 1 minute to 1 second.
> This works as long as MY_TABLE is a real table.
> But now MY_TABLE is actually a View.
> 
> Can I created a index on a view with firebird?

No, but an index on the underlying table should be used by the optimizer.

Check the execution plan of your query accessing the view.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

[firebird-support] Re: order by takes too long

[Matthias Winkler spmm...@gmail.com [firebird-support]]

Hello,

ok, I guess I need to create a DESC index on TIMESTAMP of MY_TABLE.
I could verify this speeds it up from 1 minute to 1 second.
This works as long as MY_TABLE is a real table.
But now MY_TABLE is actually a View.

Can I created a index on a view with firebird?

BR

Matthias





On Mon, Mar 2, 2020 at 5:32 PM Matthias Winkler  wrote:

> Hello,
>
> I guess this is another beginners question:
>
> I have a table with 300`000 entries.
> Each row has a separate field TIMESTAMP  with its creation data.
> Now, I query the latest 100 hundred using:
>
> select first(100) X, Y, TIMESTAMP
> from MY_TABLE
> order by TIMESTAMP DESC  -- order by takes all the time
>
> This query takes over a minute.
> Is there a way to speed this up?
>
> Background:  Infact, the next one hundred are queried remotely by the
> server and due to the long processing time my connections times out 
>
> Any ideas?
>
> BR
>
> Matthias
>
>
>