FireBird speed

I have a program that use a FireBird database. All work perfect when navigate, but when insert new records I must spend to much time.

For just 30 records with 15 fields I must wait 30-40 sec. Is normal ? In BDE is instantly.
LVL 9
ginsonicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
1. Where is the database in relation to the application?
2. What indexes are defined on these 15 columns?
3. Does this performance degrade over time or are all inserts slow?
4. How fragmented is the hard drive?

=================================
From http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_tip_perf:

"To minimize the performance hit during INSERT, consider temporarily disabling indices during high-volume INSERTs. This will "turn off" the indices, making them unavailable to help speed up queries, but also making them not be updated by data INSERTs. Then re-enable the indices after INSERTing data. This will update and rebalance the indices once for all the inserted data."

NOTE: There are also some other good tips on that page.
ginsonicAuthor Commented:
I use for select:

SELECT * FROM MyDB ORDER BY DATE DESC

Maybe because is ordered I send so much time?
aikimarkCommented:
your question is about INSERT performance.  Is this SELECT statement part of the INSERT SQL?

* Yes. Performance WILL be slower with an Order By clause.
* Since you can Order By when you retrieve the rows, inserting them this way doesn't make sense.
* if there isn't an index on the DATE column in the MyDB table, this process WILL be slower
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

ginsonicAuthor Commented:
insert sql:

insert into "MyDB"
  (VAR1, ... VAR 31)
values
  (:VAR1, ... :VAR31)

I don't have any index on by gdb . I use ORDER just for SELECT sql.
aikimarkCommented:
For the time being, forget the SELECT statement.  It isn't relevant to this INSERT problem.

Did you read the text at the link I posted?  They've got some good suggestions.  One which may help the most is to start a transaction before you begin inserting records.  Once the records have been added, comit the transaction.  What you are doing now is causing a comit after every INSERT statement.

Aside from suppressing index updating until you have finished inserting, you might be experiencing delays caused by triggers.  There isn't much you can do about them, but you might improve their performance (code tuning and database/table/index tuning).
ginsonicAuthor Commented:
Can you teach me more?
I use FIBPlus library to access and I have assigned a transaction to my FibDatabase. Is opened all the time.
aikimarkCommented:
<<Is opened all the time>>

That might be a problem.  Database transactions are meant to be started and either commited or rolledback.

I don't use the Devrace components and don't have a great deal of Firebird experience.  Most of my recommendations in this post are from my general relational database experience (two decade's long) and relevant web articles about Firebird Insert performance.  My mentoring ability is limited in this instance.
TheRealLokiSenior DeveloperCommented:
no, such a speed delay is not normal. It may mean your tables need further indexes
I assume the FIBPlus components mean you basically have a "dataset" with select, refresh, insert, update, delete queries
I am also assuming that when you do an insert, the FIBPlus is then doing the insert statement, then a select to retrieve the rows again (thus taking the time) ?
Please correct me if I am wrong.
If this is the case, then check the select and refresh queries (if they exist)
make sure they are doing an indexed select (i.e. using an index)
this will make things much faster.

e.g.
mytable
=====
firstname (keyfield1)
surname  (keyfield2)
address1 (normalfield1)
address2 (normalfield2)
city (normalfield3)

an example of an indexed select would look something like this :-
select * from mytable order by firstname, surname

If you have a where clause, or need to order in a different way, and it is not part of an index, consider adding another index
e.g.
select * from mytable where city = 'townsville' order by firstname, surname
in this case, you may want to add an index on "city" in your database, to speed things up

hth, Loki

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ginsonicAuthor Commented:
sorry for delay, was gone for a time. I will test and come back.
Thanks for suport!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.