Poor performance in comparision to FIBPlus.

[Wodzu]

Hi guys,

My company thinks about switching from FIBPlus to IBDAC. The thing is that performance achieved by IBDAC is not so good comparing to FIBPlus.

My tests shows that:

a) IBDAC is slower in selecting data by 4-6% percent.
b) IBDAC is slower about 290% when inserting data.

My results for records per second are:
IBDAC FIBPLUS
SELECT 11500 12000
INSERT 5900 17000

I've created a test application for inserting and selecting tests. So you could check this on your own and give me some advice how to speed up IBDAC. This forum does not have an opption for attaching files so how could I send you the application?

Application includes 2 databases. Its zipped size is around 200 MB. It would be nice if you could take a look at it. Performance is crucial for our type of application and we switch to IBDAC only if it matches the speed of FIBPlus.

I think that the problem is that we do not need the overhead which TIBCQuery brings by using dataset design.

We do not want to load data to grid and allow user to edit this data and then save it back. For such solutions dataset works nicely but we have two different scenerios:

1. We want to load as quickly as possible all data from database to our own memory structures or to a detached dataset (if you offer something like that). For example ADOQuery allows to detach from database after loading data.

2. We want to save a lot of data by performing direct inserts to database.

I think that TpFIBQuery component has the advantage over TIBCQuery because it does not inherit after some dataset class. Thanks to this it is more lightweight.

Anyway, I have a test application and I would like to give it to you so you could tweak the settings for it and improve its performance.

If performance will be at par with FIBPlus we will buy a few licenses for the component (we've already bought dbExpress component a few years ago from you).

Thanks for you time.

[ViktorV]

Please upload your sample to any file sharing service suitable for you and send us a link to it to viktorv*devart*com.
We are investigating the performance issue and will inform you about the results.

[Wodzu]

Thank you Victor,

I've sent you an e-mail with link.

Regards.

[ViktorV]

Unfortunately, we haven't received your letter.
Please try to resend the mail to viktorv*devart*com

[Wodzu]

Unfortunately, we haven't received your letter.
Please try to resend the mail to viktorv*devart*com

Probably your mail server categorized my e-mail as spam? Because mail contained link to website. I am resending the mail now.

Please let me know if this mail also fails, in the meantime I will also send you a private message.

Edit: ok, this forum does not allow sending private messages:|

[ViktorV]

We have received your sample. We will investigate it and inform you about the results.

[Wodzu]

Guys, 10 days passed since you've got my example. I haven't got any information from you.

So... should I still wait for your help or not?

[ViktorV]

1. You can use the TCustomDAConnection.Options.DisconnectedMode property, that allows DataSet to work without an active connection.
More details can be found at http://www.devart.com/ibdac/docs/work_d ... ctmode.htm
2. For fast data loading to the database, you can use our TIBCLoader component.
More details can be found at http://www.devart.com/ibdac/docs/devart ... loader.htm

We have conducted performance tests for TIBCQuery and summarized the following results:
- Set the TIBCQuery.Options.AutoPrepare property to True: then the query will be prepared once, not every execution.
- We will add a property TIBCQuery.Options.QueryRowsAffected in the next build. It will be set to True by default. But for cases when only INSERT queries are executed with TIBCQuery, it can be set to False, that increases performance significantly.
Currently, to test performance of TIBCQuery, you can insert the following lines at the beginning of the TfmeInsertTest.InsertData method:

Code: Select all

AQuery.Options.AutoPrepare := True;
AQuery2.Options.AutoPrepare := True;
TDBAccessUtils.GetICommand(AQuery).SetProp(prQueryRowsAffected, False);
TDBAccessUtils.GetICommand(AQuery2).SetProp(prQueryRowsAffected, False);

Note that to run this code, you should add the IBCCLasses unit to the USES clause of your unit.

[Wodzu]

Thank you,

now performance is much better. Two additional questions:

1. Regarding Select test (fmeSelectTest) there is nothing more I can do to speed up selecting?

2. Not a question but an opinion. Currently TIBCLoader is very hard to use because its design requires to use global variables. Like variable Count on Loader demo in IbDacDemo.exe.
It would be much better (again just my opinion) to do something like this:

Loader.PutColumnData()
Loader.PutColumnData()
Loader.PutColumnData()
Loader.NextRow; //This generates internal insert statement
Loader.PutColumnData()
Loader.PutColumnData()
Loader.PutColumnData()
Loader.NextRow; //Another insert statement added to the internal buffer
//...
Loader.Load; //Send buffer to Firebird.

By requiring from developer to provide all the data to insert within OnPutData event we need to create some global objects visible outside the event.

Unless as in the Loader demo we want to provide a random values... but in most cases we want to insert a real values. In my opinion design of this component should allow to insert data in the same way like in TIBCQuery.

[ViktorV]

The following options can affect the performance of data fetching in IBDAC:
- TIBCQuery.Options.DeferredBlobRead and TIBCQuery.Options.DeferredArrayRead
- TIBCQuery.ReadOnly
- TIBCQuery.FetchAll
- TIBCQuery.FetchRows
More details about this property you can find in the IBDAC documentation: http://www.devart.com/ibdac/docs
Also, take a look at the SmartFetch option (http://www.devart.com/ibdac/docs/devart ... tfetch.htm). It can be useful when navigating through large recordsets.

[Wodzu]

Thank you Victor.

Regarding my comments about TIBCLoader, do you find them valid? Do you understand why I think it is hard to use in its current event-driven form?

Regards.

[ViktorV]

Unfortunately, at the moment TIBCLoader doesn't support this functionality.
If you want us to implement this functionality, please post it at our user voice forum: http://devart.uservoice.com/forums/1046 ... e-firebird
If the suggestion gets a lot of votes, we will consider the possibility to implement it.

[Wodzu]

Ok, thanks.

I will try to provide some example.

[ViktorV]

Feel free to contact us if you have any further questions.