Problem with Calculated fields in a TFDQuerry

[Squall_FF8 1]

Hey guys,
I have a table in MS SQL that holds in one column the file names of existing files in a local storage.
When I show this table with TFDQuerry, I would like to add visually extra info - the file size.
So what I did is to add extra calculated field Size. OnAfterOpen I get the file sizes in a Dictionary<ID, FileSize>, and OnCalcFields I simply fetch the file size from the Dictionary.

The problem is - OnCalcFields is called before OnAfterOpen, actually the order by debugging is: Calc, AfterOpen, Calc.

The result on screen is - some rows have Size 0 (no value in the Dictionary). After a scroll in  the display Grid, all works fine.

Could you help me resolve this? Or if you have a better way for a task like this? 

Edited May 22 by Squall_FF8

[Uwe Raabe 2146]

I would use a caching approach. In OnCalcFields try finding the ID in the dictionary like you already do. If not found, get the file size as you currently do in OnAfterOpen and add it to the dictionary.

[Squall_FF8 1]

10 minutes ago, Uwe Raabe said:

If not found, get the file size as you currently do in OnAfterOpen and add it to the dictionary.

Thank you!
So kind of FetchOnDemand approach, I like that. It will be even less code

 

P.S. Does anybody knows why we have such weird execution order: Calc, AfterOpen, Calc?
Is there anyway to control this? Or at least to "force" Grid to display the fetched values in the second Calc?

[Uwe Raabe 2146]

16 minutes ago, Squall_FF8 said:

Does anybody knows why we have such weird execution order: Calc, AfterOpen, Calc?

Calculating fields is done on each record at several occasions. Any change to the current record, be it by navigating or switch to and from edit mode. AfterOpen is dataset based and when it is called, at least the first record is already loaded and all fields contain their values, including the calculated ones.

[Die Holländer 84]

Just for interest.. 

>>Dictionary<ID, FileSize>

How did you fill this Dictionary and why are you using it during the OnCalcFields and not using the extra calculated field from the resultset itself for displaying?

[Squall_FF8 1]

5 hours ago, Die Holländer said:

How did you fill this Dictionary

  if not Dic.TryGetValue(QryID.AsInteger, n) then begin
    var info: TWin32FileAttributeData;
    GetFileAttributesEx(PChar(QryFileНаме.AsString)), GetFileExInfoStandard, @info);
    Dic.Add(QryID.AsInteger, info.nFileSizeLow);
  end;

GetFileSize is probably faster, but require more code.  Also the result is limited to 4GB. If you need larger sizes you need to account for nFileSizeHigh too.

[Die Holländer 84]

And... why do you use a Dictionary and not the calculated field?

Edited yesterday at 05:57 AM by Die Holländer

[Uwe Raabe 2146]

The value of calculated fields are only stored for the current record. As soon as you move to another one, the field content is lost. Therefore you need to calculate the field value several times, which can slow down performance when the calculation needs some time. A solution is to cache these values.

[Die Holländer 84]

25 minutes ago, Uwe Raabe said:

A solution is to cache these values.

But a dataset like DBGrid or client dataset is caching the calculated field values, right?

[Lajos Juhász 319]

4 minutes ago, Die Holländer said:

But a dataset like DBGrid or client dataset is caching the calculated field values, right?

 

First of all, DBGrid is not a dataset, it is a component that displays data. How should a component know if and when the field value must be calculated again and cannot use the cached value?

 

On the other hand you can use fkInternalCalc  (caches) stores the calculated values https://docwiki.embarcadero.com/Libraries/Athens/en/Data.DB.TFieldKind.
 

[Squall_FF8 1]

5 hours ago, Die Holländer said:

And... why do you use a Dictionary and not the calculated field?

I dont understand your question ....
If you read carefully my initial post, you will see that I use both.  As @Uwe Raabe explained well, the Dictionary simply play the role of a cache, specifically - a disk-cache. In Fact the actual FileSize reading is done just once per file/record (on-demand).

[Die Holländer 84]

1 hour ago, Squall_FF8 said:

I dont understand your question ....

I was just wondering why you use a cache list. I use a DBGrid in one of my projects and added the calculated fields as extra columns in the DBGrid and to the FieldDefs of the query. Also in my case there is a file read calculated field and I hardly see any speed difference while displaying the resultset of the databse including the calculated fields. So, I never have used a cache list and I was just wondering why you need one, but as Uwe explained that sometime you need a cache list if the process is too slow and probably if you don't use a DBGrid.

Edited yesterday at 01:46 PM by Die Holländer

[Squall_FF8 1]

2 hours ago, Die Holländer said:

I hardly see any speed difference

That is a common misconception among developers and clients.
The reason is - contemporary computers are way to fast to objectively judge what is slow and what is fast.  Usually developers have fast computers with fast disks/memories.  But what happens when you deploy to clients? (files are on the VPN network in my case).

I would suggest to put a counter in your CalcField  - to simply counts  how many times your  disk code was used. Then use your app as normal, especially scrolling records/view of the table. And check the counter, right before you destroy the form. 
You might get a surprise. But whatever number you get, its always a comparison of 1 to many (1 for cashed).