FireDac Firebird and scroll to bottom or press End on DBGrid

[adyble 0]

Hi

Using FireDac Firebird to query a dataset of a few thousand records and scroll to bottom or press End on DBGrid it is really slow, maybe 10 seconds. After that it scrolls fine.

 

I've tried all the fetch options I can but seems to issue a FetchAll whenever this happens.

 

Advantage database server just skips instantly to the end and I've just been testing MySql and that is also instant. Is Firebird not capable of this or I have just got some settings wrong ?

 

Now, I know some people will say why do you need to display thousands of records in a grid, but they just do and we can't take it away from them 🙂

 

Delphi 10.3 FB 3

 

Thanks

Andy

 

[haentschman 92]

Hi...

 

how about:

Grid.DisableControls;
try

finally
  Grid.EnableControls;
end;

[adyble 0]

Hi

Which part are you wrapping this around ? The ctlr-end keys or drag scrollbar down ?

[Kyle Miller 1]

Here is a user experience answer to your technical question. Why pull in a few thousand records into a grid? "The user is looking for a record." If the user is finding a needle in a haystack, why make the haystack so large? Provide a search facility to narrow down the results. No user is going to read thousands of records in a single pull.

 

On the technical question, narrow down if it's the grid or dataset. Disconnect the grid. Perform navigations in code, i.e. First, Last, Next, etc. Time it using TStopWatch. Reasonable speed? How does the speed change using a local vs TCP/IP connection?

 

Verify setup using this info... https://docwiki.embarcadero.com/RADStudio/Sydney/en/Connect_to_Firebird_(FireDAC) 

 

[Guest]

The data-aware grid in my experience does not know that the dataset have "FetchAll" set to true.

So it will scroll from top when loading anyway. This will not help performance at all.

So in order to get all the records into the grid at first activation the grid needs to scroll through the whole dataset.

I do not know what grid you use and i have not used the vanilla one for ages.

So activate the dataset. /Then/ connect the grid to the dataset. When connecting the grid, wrap it with Dataset.Disable/EnableControls.

Test if FetchAll gives any performance benefits, this is absolutely not a given (esp. with blobs).

If it is a master of a detail, also make sure that the details are not scrolled during loading of the master.

 

The merits of uploading any number of records are many, look at DevExpress grids and what they can do if GridMode is set to false. And only then.

"ServerMode" entails "translating" the user actions to refine the SQL and refresh it. In local mode a datacontroller can do a lot. The amount of work to present a rich ui for analyses is incomparable.

 

Sure; the best practice in these cases is to not load all the data, but that is just generally best practice. If you adhere to all of those you will not get anywhere, IMHO. I make this decision for ever single grid/pivot/list that should be implemented.

 

 

[adyble 0]

In the end, we changed to use MySql, it is so much faster and more forgiving for our purposes. Sure, it costs a bit but we are a commercial enterprise so that's fair. 

[haentschman 92]

Hi...

Quote

In the end, we changed to use MySql

...why? Why do you want to possibly fall into the license trap?

 

https://searchitchannel.techtarget.com/feature/Using-MySQL-licensing-Open-source-license-vs-commercial-license

https://www.mysql.com/de/products/

 

or

 

MyDAC / UniDAC without loading libmysql.dll (the dll is the problem)

 

info in german: https://entwickler-ecke.de/topic_libmysqldll+lizenz_68186,0.html

 

totally free databases: Firebird, PostgreSQL ...