• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 977
  • Last Modified:

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.
0
ginsonic
Asked:
ginsonic
  • 4
  • 4
1 Solution
 
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.
0
 
ginsonicAuthor Commented:
I use for select:

SELECT * FROM MyDB ORDER BY DATE DESC

Maybe because is ordered I send so much time?
0
 
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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.
0
 
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).
0
 
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.
0
 
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.
0
 
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
0
 
ginsonicAuthor Commented:
sorry for delay, was gone for a time. I will test and come back.
Thanks for suport!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now