I'll try what you suggested. But the problem with the slow execution is not only for these two queries. I have queries only with inner joins (the foreign keys are integer fields). All reads are indexed but again the Firebird works very slow.
And this is after backup/restore.
Is there anything that I can do to make the server faster?

--- 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)
>