Jump to content
adyble

Delphi FireDAC .Post on Firebird table

Recommended Posts

Hi all

 

I have a strange on. We are new to FireDAC and Firebird. We have a simple Table, 20k records opened with an FDquery.

 

RAD studio 10.3, Firebird 3

 

select * from jobs

 

fetches 50 records so very quick.

 

we use FDQuery.Insert , fill in the data and then FDQuery.Post

 

The first time , its really slow, nearly 10 seconds, every other insert and post after that is instant. It only goes slow again after a program restart.

I am guessing there are so many options on the FDQuery that something will need tweaking. This is any table, any number of records from 10 to 20k

There are no indexes, PK etc . just to keep it simple to test.

I created a test app if anyone has any ideas.

 

Thanks

Andy

 

 

Share this post


Link to post

Hi...:classic_cool:

Quote

I created a test app

...then show us this thing...incl. database :classic_wink:

 

Quote

There are no indexes, PK

...bad idea. You should always have a PK! :classic_huh:

  • Like 2

Share this post


Link to post

I didn't show it at first in case anyone had seen before.

 

It was simply that after the first .Post it does a fetchAll, but after that all records are fetched so doesn;t need to do it again.

Changing fetch option  - AutoFetchAll to Truncate solved it but I am not sure what Truncate does, so will have to see what effect it has.

We manage the PK another way to not get duplicates and maintain sequences. We have more control our way. Never used one in 30 years.

Share this post


Link to post
Posted (edited)

This is about the database and the different ways it allows for the DAC (and thus you) to reduce the use of server resources.

I'll not go into details. IBObjects has a very good tutorial on FB transactions you can dive into. There's a lot about OAT and more.

 

The database design is another ballpark but related because of the way you can access you data when you have it normalized correctly.

 

IMHO all you database tables should have a primary key.

 

IMHO autogenerate all keys, either using a sequence or a GUID, do not try to build a key out of "real world data", use indexes for that so that the columns and relations can be changed in production; if for example sizes of garment cannot be expressed with 5 characters any more. XXXXXL might popup up in the future. You would need to change the type of that column. Much trickier and costly if it is a primary key. The value will never have to be displayed to a user, but when you build a REST server you will be so happy you can do idempotent apis by just storing that ID and doing a simple DSQL update.

 

HTH, /D

Edited by Dany Marmur
Typos typos...
  • Like 5

Share this post


Link to post

In the end it was just setting the FDQuery - Fetchoptions - Autofetchall to afTruncate and it works perfectly. 

Share this post


Link to post

/off-topic: My mind boggles at the thought of a table without a primary key...

  • Confused 1

Share this post


Link to post
On 8/24/2021 at 7:54 AM, Lars Fosdal said:

/off-topic: My mind boggles at the thought of a table without a primary key...

Hi Lars

We performed a lot of tests. 9.7 million records, via remote TCP to a Linux server in a datacentre. Most queries were "Like" on a large field. 5 users performing queries at the same time. There was virtually no difference is speed or load on the server. Maybe our apps aren't very data hungry.  We since changed from Firebird to MySql and they are even faster.  The data was returned in Msecs every time.  We don't have a PK index but we do use a GUID for every record as an additional identifier.

I was very surprised (and pleased) with these results. It's very rare for our tables to have more than 100,000 records and 5 users.

Andy

Share this post


Link to post
On 5/22/2021 at 5:33 AM, adyble said:

We manage the PK another way to not get duplicates and maintain sequences. We have more control our way. Never used one in 30 years.

That's... not normal. This is as strange to me as the Delphi user on the old forum who said he never uses Delphi strings, only Pchars. You're not supposed to have control; the database is supposed to have control. You declare what you want and the RDMS query optimizer decides the best way to retrieve it. I can guarantee you there's no query optimizer designed with your "another way" in mind.

Edited by Joseph MItzen
  • Like 4

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×