Firebird embedded database is extremely slow
lazarus / 16-Databases

----------------------------------------------------------------------
samto · 2012-09-07 06:24

Hi all,

I have one another problem (and I hope the last one) with firebird
embedded database. I have a simple table (about 10 string fields,
130000 records) and all processes as a access or filtering are
extremely slow (I am using Zeos components). For example opening db is
about 30 sec (when I open it in Firebird Maestro, it takes about 2-3
sec).

Do you have some idea, where is a problem?

Thanks and have a nice day.

----------------------------------------------------------------------
ludob · 2012-09-07 07:33

There is not very much info to go by.
Are you loading the complete table in memory? Is Maestro loading the
complete table in memory?
Do you use a sort order when opening the table? Where is the data
sorted? Client or server?
Is the filtering done client side or server side?

130000 records with 10 string fields is not a small table. You don't
want to load that in memory unless you have a very good reason. That
is bad usage of a sql database. For a single table loaded in memory
you could better use an in memory dataset loaded from a plain file and
forget about sql. SQL databases are optimised for searching and
sorting data. Use that to get only the data you need.

As the thread title suggests, have you tried with Firebird server or
any other database server and was that much faster with the same
table?

----------------------------------------------------------------------
Zoran · 2012-09-07 09:47

Try setting ZQuery1.FetchRow property to 200 or some other resonable
ammount. Test and see if that solves your problem.

----------------------------------------------------------------------
samto · 2012-09-07 10:31

ludob:
To your questions:
database is loaded from single *.fdb file (43MB) whole to the memory
(without sorting)
I need to sort and filter database - it s a reason why I am using sql
db

You are right - Firebird Maestro loaded only 1000 records.

Zoran:
I think, it should help, but ... it doesnt work. Is there any settings
as e.g. sql statement?

----------------------------------------------------------------------
ludob · 2012-09-07 11:08

What are you trying to achieve?

In general, let the database do the sorting and filtering and limit
the number of rows returned to what you want. Use a TZQuery and create
an select sql statement that gives the sorting and filtering you need.
Use the "rows" clause to limit the number of rows returned: http://www
.firebirdsql.org/refdocs/langrefupd20-select.html#langrefupd20-select-
rows.

----------------------------------------------------------------------
Zoran · 2012-09-07 11:40

Quote from: samto on September 07, 2012, 12:31:20 pmZoran:
I think, it should help, but ... it doesnt work. Is there any settings
as e.g. sql statement?

It works here... When FetchRow is set to 0 it means unlimited, but if
you set it to 200 it should (and it works for me) return 200 rows at a
time. When you browse through the records, the zquery fetches the next
set of rows.

----------------------------------------------------------------------
samto · 2012-09-10 10:06

I had to set Table.Filtered to true and it works as you mentioned.
Thanks for your help.

But filtering by sql querry is still very slow (if i am searching
fields, which are at the top of database, it works fast, but if I want
to filter records from bottom, it is slow). Do you have some idea, how
to solve it?

----------------------------------------------------------------------
ludob · 2012-09-10 10:15

QuoteBut filtering by sql querry is still very slowAre you using an
index? Searching text without an index is indeed much slower because
Firebird needs to access all rows.