Delphi FireDAC .Post on Firebird table

[adyble 0]

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

 

 

[haentschman 92]

Hi...

Quote

I created a test app

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

 

Quote

There are no indexes, PK

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

[adyble 0]

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.

[Guest]

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 May 23, 2021 by Guest
Typos typos...

[adyble 0]

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

[Lars Fosdal 1792]

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

[Hans J. Ellingsgaard 21]

A primary key is a ground rule of relational daatabases. 

[adyble 0]

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

[Joseph MItzen 251]

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 September 10, 2021 by Joseph MItzen