[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

FireBird speed

Posted on 2006-03-22
9
Medium Priority
?
972 Views
Last Modified: 2010-05-18
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
Comment
Question by:ginsonic
  • 4
  • 4
9 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 16261466
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
 
LVL 9

Author Comment

by:ginsonic
ID: 16286913
I use for select:

SELECT * FROM MyDB ORDER BY DATE DESC

Maybe because is ordered I send so much time?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 16288676
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 9

Author Comment

by:ginsonic
ID: 16293382
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
 
LVL 46

Expert Comment

by:aikimark
ID: 16293644
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
 
LVL 9

Author Comment

by:ginsonic
ID: 16300584
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
 
LVL 46

Expert Comment

by:aikimark
ID: 16301792
<<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
 
LVL 17

Accepted Solution

by:
TheRealLoki earned 1000 total points
ID: 16413676
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
 
LVL 9

Author Comment

by:ginsonic
ID: 16454602
sorry for delay, was gone for a time. I will test and come back.
Thanks for suport!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question