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)