FireDac: Best way to non-block, lazy fetch large BLOB & display image.

[Kyle Miller 1]

Given a table with a blob field where the value might take several seconds to pull down, what's the best way to lazy fetch the data w/o blocking the user interface?

 

The BLOB is an image displayed on a form. The blob is being pulled in a detail query as to not slow the master query down.

 

TFDFetchOptions allows delaying until BLOB needed. This results in a similar problem. When user clicks record, user has to wait till BLOB pulled down.

 

Also tried ASync as described in wiki under Asynchronous Open and Fetching. The problem is either the living binding errors with "couldn't find text" or an exception is raised when jumping back and forth between two records, one with a blob and one without. I even tried a TTask solution.

 

The goal is to lazy load the BLOB and display the image if it comes down. If the user clicks to another row, abandon the BLOB fetch and display the new row's image if present.

 

EDIT: Using Delphi 10.2 Enterprise.

Edited June 11, 2020 by Kyle Miller

[Guest]

For most DB and their DACs / client libs, you must use one "Connection" per thread.

So the loading thread needs to get the connection parameters from the main thread and open a connection.

Opening new connections is expensive resource-wise and that is where the connection pools come in.

I do not use FireDAC myselfe as i cannot afford the sources, it is quite a library, and maybe they have catered for this in some way, but i would not know that.

HTH, /Dany

[aehimself 396]

I don't use FireDAC, but a 3rd party library to connect to databases, Unfortunately though, they work in async mode only, so a huge query with lots of blob fields could take up to a couple of hours (!!!) to load through the Internet.

God bless those SELECT * FROM ... JOIN ... JOIN ... JOIN ... type queries.

 

My solution was to unhook all datasets from any datasources, and call the dataset's .Open method in a separate thread, Once the thread finishes it executes a callback routine to hook the datasets back to the datasources. This resulted a completely unblocked VCL thread, and I even could implement a button to execute the "Abort" command through the same connection if the user does not want to wait until the end of the query.

 

You can create a custom TThread descendant with a parameter of a dataset, but I chose to take an incoming Procedure Of Object parameter instead. This way, I can execute the same thread with a Dataset.open, Dataset.Post or Connection.Commit when I please, leaving the application as responsive as ever.

[Guest]

@aehimself, cool! IMHO you should do a small write-up using TDataSet compatible code.

I once did a rather extensive test project in that direction, but i think my ambitions was too high.

It worked but i also invested time writing a middle-tier,now i have a middle tier so most of these kind of problems are gone.

[aehimself 396]

@Dany Marmur I used to have one but I decided to discontinue my own website as it started to be a burden to maintain. The idea is to be as simple as possible:

 

Type
 TProcedureOfObject = Procedure Of Object;
 TErrorEvent = Procedure(Sender: TObject; Error: Exception) Of Object;

 TZMethodThread = Class(TThread)
 private
  _runmethod: TProcedureOfObject;
  _onerror: TErrorEvent;
 protected
  Procedure Execute; Override;
 public
  Constructor Create(inMethod: TProcedureOfObject; inErrorEvent: TErrorEvent; inFinishEvent: TNotifyEvent); ReIntroduce;
 End;

Constructor TZMethodThread.Create(inMethod: TProcedureOfObject; inErrorEvent: TErrorEvent; inFinishEvent: TNotifyEvent);
Begin
 inherited Create(False);
 _runmethod := inMethod;
 _onerror := inErrorEvent;
 Self.OnTerminate := inFinishEvent;
End;

Procedure TZMethodThread.Execute;
Begin
 Try
  If Assigned(_runmethod) Then _runmethod;
 Except
  On E:Exception Do If Assigned(_onerror) Then _onerror(Self, E);
 End;
End;

And then there is a "wrapper" around this, where you just pass a dataset and it is creating a TZMethodThread with Dataset.Open for example. I hope I can say this and it's not considered an advertisement; the latest version of the unit is included in Zeos 7.3 (look for ZMethodInThread.pas) which is although "specialized" can easily be ported back to use a standard dataset.

 

Or throw the wrapper out and just use the thread with any procedure of object - with the necessary caution to VCL synchronization, of course.

[Guest]

5 hours ago, aehimself said:

decided to discontinue my own website as it started to be a burden to maintain

Yeah. Conversation with professionals usually render such. I have some thing still "up" (accessible) but it's not something i ever tell any real-world (prospective)client to look at.

 

I'll have a good thorough read of the code above, and the link. But - as said - i am not in the need anymore. But i think that your/similar solutions somewhat needs to advertised and available to the community. The TDataSet idiom has sprouted loads of good code and libs. New arrivals may very much refrain from learning the idioms MVVP/MVP/MPM/Whatever. Just because some of the specific results does not look as "fluent/flowing" as alternatives.

[Kyle Miller 1]

On 6/14/2020 at 3:22 PM, aehimself said:

My solution was to unhook all datasets from any datasources, and call the dataset's .Open method in a separate thread, Once the thread finishes it executes a callback routine to hook the datasets back to the datasources. This resulted a completely unblocked VCL thread, and I even could implement a button to execute the "Abort" command through the same connection if the user does not want to wait until the end of the query.

This is exactly what I did using TTask and TThread. It was as close as I could get to a working solution. Problems arose when jumping between records quickly. Tried calling FireDac's AbortJob method to making aborting more graceful. It didn't go well.

 

I'll try using a connection per thread and possibly test out TZMethodThread.

[aehimself 396]

2 hours ago, Dany Marmur said:

I'll have a good thorough read of the code above, and the link.

You don't need much time - it's about 200 lines 🙂 As I said I tried to keep it as simple as possible. If someone needs more, the code is easy enough to be extended with someone's own needs.

2 hours ago, Dany Marmur said:

But i think that your/similar solutions somewhat needs to advertised and available to the community.

Completely agreed. That's one reason I chose to hand over the code to the Zeos team. I wrote a custom DB management app and I needed to keep the GUI responsive even if the user entered a hour-long query. That's when I came up with the idea of pushing it to a background thread. It does nothing though if you can not gracefully end it so aborting running operations was born.

I needed it to satisfy my colleagues needs using my tool and it felt good to finally give something back to the component I was using for long years now.

 

Effectively a win-win situation.

[aehimself 396]

1 minute ago, Kyle Miller said:

I'll try using a connection per thread and possibly test out TZMethodThread.

One connection per thread is like an unwritten rule. I experimented with one connection for 7 workers, but gave the idea up quickly. One thing I found strange is that if I called dataset.Open in a background thread, I had to call .FetchAll too, otherwise it still froze the VCL thread upon accessing .RecordCount.

But the jumping between records error still makes me believe that the connection is going to be the issue - if FireDAC works similar to the components I use.