Ariel,

Based on some of your comments and responses, I would recommend you
spend 30 mins reading up on transactions etc if you are using a
database such as Firebird.

Firebird requires transactions to function. You can not run a query
without a transaction, and your transaction must be committed for the
data to be visible to any other transaction. If your data access
components don't work this way, then I highly recommend switching them
for ones that do. (The ones that don't have the concept of a
transaction that you must start and stop automatically start a
transaction when you execute a query, and commit it immediately after,
but that is not a good approach at all.

Firebird is ACID compliant.
http://en.wikipedia.org/wiki/ACID
(not the best description but enough)

If you are paying some money, then a couple of things must happen. The
amount must be taken from your account, and the same amount must be
added to the other account.

So if 100 something (dollars / euros / whatever) was to be transfered
from account 1 to account 2

update account set balance = balance - 100 where id = 1;
update account set balance = balance + 100 where id = 2;

That is pretty simple, but if something happens between these two
statements (like a server crash), then there may be trouble. Account 1
has lost $100, and 2 never got it, and there will probably be big
fights over it.

A transaction allows you to treat these as one.

StartTransaction
try
update account set balance = balance - 100 where id = 1;
update account set balance = balance + 100 where id = 2;
commit
except
RollBack
end

So if something happens before it commits, then account 1 doesn't lose
the 100, and everyone is happy (except account 2 who still wants his
money, but at least they can transfer it again).

> 1) I have a table that may grow to quite a large number of rows. After
> inserting a few thousands of rows to that table I try to do a query on
> the table and it takes a very long time.

It sounds to me like you don't have useful indexes that the optimiser
can use to help the query. But you need to provide your table
structure, any PK or FK in the table, and any other index, then
provide the select statement that is slow.

> 2) If I take the same table (after inserting all the rows), and I close
> the connection to the DB and open it again, the same query takes 1-2
> seconds (much faster).

Isn't caching great, but 1000s is not that many, so I suspect a poor
plan is being used because there isn't an appropriate index to speed
it up.

> How can the fact that I just inserted the rows to the database affect
> the time it take to query the table?

There is an undo log that is maintained for ~5000 queries, that if you
rollback gracefully is executed. This reduces garbage buildup, but
also Firebird loads pages into a cache, and if you execute the same
query, it will run quicker the second time.

If you restart the firebird service and execute the query (without
inserts), I bet it is slow again.

But if you want more help, you will need to post some SQL that we can
take a look at.

Adam