Sometimes it is good to use an index, sometimes not. You have to figure
out how the indexes are used, not just look at indexed reads being used.
Moreover, if you run several slow queries simultaneously, it will affect
other queries as well. One possible 'solution', is in some cases to use
Classic Server rather than SuperServer, but that only helps in that
simultaneous queries from different users may use different processors
rather than compete for the same resources.

Most of us do not think Firebird is slow, although I have no problems
admitting that the PLAN Firebird chooses sometimes isn't good so that
some tweaking is needed. Other than that, well, I can only think of
issues like having .gdb filename extension (formerly, that was the
normal extension for InterBase databases) on certain Windows versions
makes the operating system take a backup upon connect. Of course, it is
not difficult to write lousy queries that takes forever, but your
particular query doesn't seem too bad (the only thing I noticed was that
you used indexed reads to access all records in PARTIDA_NAL), although
the PLAN is a key point that you haven't told us anything about.

Set

mdjorov wrote:

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