The PLAN of my query is
PLAN SORT (JOIN (SERIAL_NUM NATURAL, PARTIDA_NAL INDEX (FK_PARTIDA_NAL_ELEMENT_ID), IN_EL INDEX (PK_IN_EL_ID), OPR INDEX (PK_OPR_ID)))

I tried your and the results are better
The PLAN is :
PLAN SORT (JOIN (O INDEX (FK_OPR_SKLAD_ID), IE INDEX (FK_IN_EL_OPR_ID), PN INDEX (FK_PARTIDA_NAL_IN_EL__ID), SN INDEX (FK_SERIAL_NUM__PARTIDA_EL_ID)))

All reads are indexed
Table READS

PARTIDA_NAL 55410
SERIAL_NUM 1
OPR 19055
IN_EL 45599

but the main problem is still here. This query was executed for 8.9 s.
The CPU was used between 0 and 4 %. It's like that 3%, 0%,0%, 4%,0%,1%.

The second time was like 14%,25% and it took 0.5.s.
And this is a simple query. When I have a more complex with more joins and for bigger tables the time is much longer up to few minutes when normally is under a minute.

In Firebird 2.0 queries that I didn't change were more faster then now with 2.1. If Firebird use the CPU on max the queries work fine, but now every time the first execution is very, very slow.
The Firebird process stops for a second or two, then runs for a second with 2-3% of the CPU, after that a new pause for a second etc.
But when I run the query for a second time the process uses 100% and the times are much, much better.
I have executable stored procedures. They make calculations and changes in the database. It's the same for them.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer <svein.erling.tysvaer@...> wrote:
>
> What's the reported PLAN?
>
> My guess is that an index for PARTIDA_NAL.KOL is used and that all
> records contains a value greater than 0 (making the index slow down the
> query execution). Try the following query:
>
> SELECT SN.SN, PN.ELEMENT_ID, IE.SIMPLE_ID, PN.IN_EL_ID
> FROM OPR O
> JOIN IN_EL IE ON O.ID+0 = IE.OPR_ID
> JOIN PARTIDA_NAL PN ON IE.ID = PN.IN_EL_ID
> JOIN SERIAL_NUM SN ON PN.ELEMENT_ID = SN.PARTIDA_EL_ID
> WHERE PN.KOL+0 > 0.00
> AND O.SKLAD_ID = :SKLAD
> ORDER BY 3
>
> and report back the PLAN and time for this query as well. The first +0 I
> added to persuade the optimizer to use OPR first in the plan (it may
> or may not be necessary), the second +0 is to avoid using an index on a
> term that (almost) all rows match anyway.
>
> Answering your second question about a slow, stored procedure is
> impossible without a lot more information, both about the stored
> procedure (need the code and preferably the plan used for queries within
> the stored procedure) and the query calling the stored procedure.
>
> Why things are quicker on the second than the first run, may be due to
> things already being in cache, but others have more knowledge about this
> question than I have.
>
> HTH,
> Set
>
> mdjorov wrote:
> > I have a query:
> >
> > SELECT
> > SERIAL_NUM.SN,
> > PARTIDA_NAL.ELEMENT_ID,
> > IN_EL.SIMPLE_ID,
> > PARTIDA_NAL.IN_EL_ID
> > FROM
> > OPR INNER JOIN IN_EL ON (OPR.SKLAD_ID = :SKLAD AND IN_EL.OPR_ID = OPR.ID)
> > INNER JOIN PARTIDA_NAL ON IN_EL.ID = PARTIDA_NAL.IN_EL_ID
> > INNER JOIN SERIAL_NUM ON SERIAL_NUM.PARTIDA_EL_ID = PARTIDA_NAL.ELEMENT_ID
> > WHERE
> > PARTIDA_NAL.KOL > 0.00
> > ORDER BY 3
> >
> > The results:
> > 1 row is fetched and it takes 31 seconds for the first execution of this query.
> >
> > CPU usage is between 0 and 4% with pauses with no CPU usage at all.
> > The joins are through foreign keys. OPR.SKLAD_ID is a foreign key and it has an index.
> >
> > TABLES TOTAL ROWS READ
> > SERIAL_NUM 39687 39687 NON INDEXED READ
> > PARTIDA_NAL 440419 440419 INDEXED READ
> > OPR 322804 4979 INDEXED READ
> > IN_EL 367627 4979 INDEXED READ
> >
> > 1.171 s for the second execution.
> >
> > And this is not a single event. I have a query that selects data from a selectable stored procedure.
> > In normal execution it takes about 40s with nearly 100% usage of the CPU's core.(some of the tables have over 1 mil. records)
> >
> > But the first execution usually takes 5 to 10 minutes on my machine, again with very little CPU usage and the entire system works very slow until firebird return the results.
> >
> > I moved the database to another machine, the results are similar.
> >
> > What can be the problem? Is it something in Firebird or the problem is elsewhere?
> >
> > My machine is:
> > Windows XP SP3
> > 2GB RAM
> > AMD Athlon 64 X2 Dual 4000+ (2.1 GHz)
> > Firebird 2.1.2 Super server (with Classic server it is the same)
>