Firebird goto last and big comboboxes with Delphi

[Midsoft UK]

Having recently started using Firebird and moving from tables to mainly queries, I have a couple of questions just to see how people generally handle them in Delphi.

We are testing with remote data and local applications to give us the worst speed case, especially via really slow connections.  So far speed is great, even on £10 per month hosted linux servers.

1. Opening a large table into a DGGrid, starts off ok as it loads 25 or so records, then as you scroll, loads in more. Thats all good. However, if there are say 5,000 records and you issue a .Last then it has to load everything in, same as scrolling nearly or to the bottom of the grid.

This naturally takes ages.

How do you guys deal with this ?

2. Similar but with LookupComboboxes. As the data is live, on large underlying datasets they can take ages to fill and often cause forms to open very slowly.

So far we have tried a DBEdit that has a button to the side to look like a combo and only fills in as you type (performs a query). This works ok but would like to hear others experiences.

Thanks

Andy

[DougC]

LookupComboBoxes are inappropriate GUI widget for long lists. Horrible user interfaces have been built misusing them in this way. Find a different approach!

[Dimitry Sibiryakov]

22.01.2021 15:38, Midsoft UK wrote:
> 1. Opening a large table into a DGGrid, starts off ok as it loads 25 or so records, then
> as you scroll, loads in more. Thats all good. However, if there are say 5,000 records and
> you issue a .Last then it has to load everything in, same as scrolling nearly or to the
> bottom of the grid.
> This naturally takes ages.
> How do you guys deal with this ?

I prefetch records in background thread instead of "on demand". Of course standard DB
access components are not clever enough for that.

> 2. Similar but with LookupComboboxes. As the data is live, on large underlying datasets they can take ages to fill and often cause forms to open very slowly.

Use autocomplete instead of lookup and send query only after user entered enough
letters to cut list down to a reasonable number of options so your approach is viable but
TDBEdit is not a good choice for that.

--
WBR, SD.

[Midsoft UK]

Thanks for  reply sd.

Pre fetching might also be slow. We tested on 9 million records and was impossible plus the underlying data could change a lot and have to be pre fetched again.  We are going to try to link the scroll to next 50 or so and also Trap any .Last to disallow it if possible.

The autocomplete looks interesting. A bit like what we are trying. What would you use instead of DBEdit ?

Cheers

Andy

[Dimitry Sibiryakov]

22.01.2021 17:42, Midsoft UK wrote:
> Pre fetching might also be slow. We tested on 9 million records and was impossible plus
> the underlying data could change a lot and have to be pre fetched again.

No sane application developer ever show 9 million records in a grid.

> The autocomplete looks interesting. A bit like what we are trying. What would you use
> instead of DBEdit ?

TComboBox.

--
WBR, SD.

[Midsoft UK]

Yeah I agree :). Was just to get our worst cases.

but a few thousand maybe. But its how to prevent that happening.  We always like to display an easily searchable grid. But its possible to display only the first few by default and offer a search. However as the user scrolls it automatically loads in more data. If the user scrolls to near the end, then thats a lot of data to load. Fast on a local connection but over a slow one like 3G then not so.

So with your combobox you populate the items form the query of the underlying data ?

I wonder if that will work with a DB Combo. Will give it a try.

[Jason (PN)]

It all depends on the data / use-case / resources available (to develop and to execute).

 

All in a DBGrid and using locate / filter = cheap to dev, OK on local data, bad on client server.

Something generic like with IBObjects: they have some tricks, like getting all PK’s required and so pulling back data as you need to see it.  Still cheap to develop, but works for a limited range of problems.

or even TTable in BDE: This had a trick of when you hit end, it would get that data directly by reversing the order of the request, nearly instant.

Custom solution for your situation: Know the data is date driven / alphabet driven, then have a coarse navigator, like click on first letter or year.

 

Nobody wants to find a record in 5K records.  It really depends on whether they need to see adjacent record when they find the one they want.

Incremental searches work well when looking for a specific record:

• Make them type 3 letters into an edit, then do a select “containing” on the important fields or starting with (maybe a checkbox for “contains”)
• Deal with case issues.
• Next keypress, wait till no more keypresses for 0.25s then decide if they just added more letters (then you can filter) or changed the text (close query, change params, re-open).

Works nicely, but you have to keep an eye on performance and server load if it is the primary way of navigating data.

 

I don’t think there is a one-solution-fits-all.  We use caching as well for lots of data and load on first use or when system not doing anything / background thread.

 

Jason Chapman

JAC2 Consultancy Limited

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/153a21b7-1710-4493-afc3-a76a95faa702n%40googlegroups.com.

[Midsoft UK]

Cheers Jason

That's how we have been doing the lookups. Sorts it. We have been substituting a DBLookupcombobox for anDBEdit and Button with down arrow to give the appearance.

For invoices, orders, jobs in grids it's easy  as you have a date range, so we show this month, next month etc.

It's Customers, products etc, where the user doesn't always know what they are looking for a scrolls. Users will always do this (I do to :)).

The alphabetical is a nice option to try. 

We have been coming from local and local server setups where opening 5k records into a grid is quick but converting all our apps to use remote data should it be needed and it will more and more is taking some planning.

We have found that a £10 month VM running linux works perfectly for small clients even with that test 9 million records! Pretty impressed with FireBird so far.

[Midsoft UK]

We've come back to this after customers not happy they can't just scroll their data like they used to do without waiting a while for it to load first :)

I did a test on a remote MySql database today and that was instant with the same data. Looks like its only FireBird that does this automatic Fetchall. I've also noticed that it does a fetchall when closing the table too.

Is there any way to report this to the developers in case they have a workaround or future improvement ?

Thanks

[Jason (PN)]

Hi,

 

It really is (I think) down to the component set and then specific component you use in your Delphi Application.  What is that?

For both the mySQL and the Firebird test?

 

What the old BDE TTable did was notice you wanted .last and it would internally close the query and issue the same query, but with the order the opposite way.   It stitched the data together from memory, so it was seamless to the user.

 

So it is looking for what they want and then working out how to get it to them.   You could certainly implement the same kind of logic yourself OR give them the ability to start typing and it issues a lazy query (background thread) for the lookup to do a “starting with” against the product code. 

 

OR they hit the dropdown and you show next to it a grid of 26 letters and they click on the letter and it goes straight that section, again, but opening “starting with” query.

 

OR as I mentioned earlier in the thread, you could cache the 5K results and have some kind of “invalidation” mechanism whereby either by polling or some other communication, have a watcher that knows when the stock names change / get added / deleted and re-get them.

 

Aside: I actually have a search screen where the dropdown has completely different content, based on previous decisions.  So the dropdown has between 2 and 500 rows.  It has a dropdown AND a button.  The button brings up a small selection form (and you can make these look like a popup).  This can use filtering and other search criteria.  So now the user decides, dropdown and scroll or button and filter / search.

 

A lot of info, but there are a lot of options.  Let me know the component sets as I would be interested in how the mySQL remote server connection actually works.

 

Cheers,

 

Jason Chapman

JAC2 Consultancy Limited

Mob: 07966211959

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/cb73a28c-6127-4dc5-b07a-a4bd893c978bn%40googlegroups.com.

[Midsoft UK]

Hi Jason

Its a delphi TDBGrid and dev express Grid.  For some of the combos, we pre-load at program startup.  Its just a shame it works like this, everything else is good. It's only actually in certain circumstances, like browsing products or customers, where they are not sure of the code or description.

We have it ourselves, customers save with their initials and not their name,  some having the word "The" at the start and not realising. Things like jobs, orders, invoicing is easy to handle as you can query by year/month by default. It's rare anyone you  want to look at every invoice on a system.

The Mysql test was the same DBGrid - or the one next to it so I could do a side by side test. Mysql would be the answer but its too expensive for commercial use as each customer has to pay $5000 even if they only have 2 PC's which is bonkers.

Cheers

[Midsoft UK]

I did consider using RecsSkip and MaxRecs, for example skip 7500 records and display 800 but can't get the grid to show anythng.

[liviuslivius]

Are there more then 7500 records in the table?

And check what are you doing in code and in fetch settings, as noone have such problems with speed. Also if you must show to the user more then 1000 records this is for sure design problem. E.g 1000 records and even 100 records per screen then user must press pgup/pgdown 10 times, with mouse wheel it is 1 minute scrolling data wihout even analising them ;-) i cannot think about somethink larger like 10000 or more to show to the user.

We have database with 940 kk records, it will be funny to show them all to the user :)

Refards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Midsoft UK <midsof...@gmail.com>

Data: 24.08.2021 10:21 (GMT+01:00)

Do: firebird-support <firebird...@googlegroups.com>

Temat: Re: [firebird-support] Firebird goto last and big comboboxes with Delphi

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4b2b6c0f-5674-4745-b3cf-d85fbf4aad7cn%40googlegroups.com.

[Midsoft UK]

I wouldn't call it a problem with design but with user expectation. With other databases, even if there 940k records, it would show them all even doing a straight select query,  it would show the initial fetch count, its when you press "End" or drag the scrollbar to the bottom quickly on a DBGrid thats different.  

Advantage database and MySql jump straight to the last few records (in what ever ORDER by form the query). We have one with 9.7 million records that we use to speed test. If the fetch count is 100 it loads in the first 100, then when either drag the scrollbar to the bottom or press End, it jumps to the last 100 and scrolling up just loads in as needed.  It's almost instant even on 9 million records. Firebird takes a minute ! It's about the on thing wrong from our angle.

Cheers

[Omacht András]

Hi!

 

Firebird support this, just use first and skip keywords in select.

 

Maybe you should write your own component to support this feature.

 

I think this is not Firebird but Deplhi combobox problem.

 

András

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3efee14c-5560-41ea-87c3-8d698c381409n%40googlegroups.com.

[Midsoft UK]

I tried SKip, it worked a  little. For example if 8000 records, max recs = 500 and SKip 7500. But how to track when user has dragged scrollbar to bottom ?

Also, as you scroll backwards up the grid, there are only 500 records, so need to find the first record and query again to get the next set.

I tried 3 different DBGrids - delphi, Devexpress and TMS, all do the same. I think Mysql and Advantage have something built in. I also tried Firedac and Devart components to access the data.

It would be nice to ask the developer but I don't know how to contact them.

Andy

[liviuslivius]

I will do not compare to Advantage but mysql test is interesting comparision.

But it can be Firebird or dbcomponents used or settings used. 

Also it depend of db settings e.g. Firebird superserver or classic?

DB cache size settings? Already warm cache or first read?

-------- Oryginalna wiadomość --------

Od: Midsoft UK <midsof...@gmail.com>

Data: 25.08.2021 15:52 (GMT+01:00)

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3efee14c-5560-41ea-87c3-8d698c381409n%40googlegroups.com.

[Pepe Pérez]

Have you tried creating an additional index in descending order to speed up the process of going to the last record?

[Dimitry Sibiryakov]

Pepe Pérez wrote 25.08.2021 17:16:
> Have you tried creating an additional index in descending order to speed up the
> process of going to the last record?

It cannot help.

--
WBR, SD.

[Midsoft UK]

its the same FireDac compnent for Firebird and Mysql.

Thanks

[Midsoft UK]

Hi

Yes, we've tried with and without index, makes no difference.

Thanks

[Tim Crawford]

Mini rant....

I am really getting tired of seeing these postings to he firebird support mailing list
regarding poorly designed application components, but more accurately poorly
designed applications, that really have absolutely nothing to do with Firebird

It should NEVER be necessary to provide entire list of values from a large
database table, be it in a combo box or db grid when there are
more than maybe 100 values maximum. In what universe do you want
to pull thousands or millions of rows from the database when at most
the user will be able to see at best a few dozen at a time?

I concede that some of these problems might (probably?) be due do
bad database design, and if so questions relating to that might be appropriate.

Okay I'm done. Thanks for listening.

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/06343a39-e3d0-4285-a5c0-5519f01398aen%40googlegroups.com.

[Midsoft UK]

Hello

If you have nothing beneficial to offer, wouldn't it be better to keep quiet? FireDac badly designed ?  I doubt it. You obviously have no idea of users requirements. It is only in a rare circumstances it is required. I am not complaining about FireBird, I'm impressed with it, I just want to make sure we know how everything works and trying to understand why it can be done in Mysql but not FireBird. Our software badly designed ? I doubt that also, otherwise we wouldn't have thousands of happy users.

I agree there is often a play off between user interaction and design best practices but sometimes there has to be a compromise.

Can I assume you are an inhouse developer for a company rather that providing off the shelf systems ?

Have you never scrolled through a spreadsheet that has thousands of records while checking things ? There are many other examples why you'd need to scroll large lists.

Next time you don't like something just look the other way :)

[Dimitry Sibiryakov]

Midsoft UK wrote 25.08.2021 19:02:
> FireDac badly designed ?  I doubt it.

I have no doubts. It derived the design from Delphi 1 DB VCL which was made
for BDE (flat local database) and doesn't suit client-server paradigm well.

> Our software badly designed ? I doubt that also

I have no doubts. Your software was developed for Advantage Database (flat
local database) and doesn't cope with sequentional result sets from client-server.

--
WBR, SD.

[Svein Erling Tysvær]

Unfortunately, the benefits of Firebird prevents it from shining when you insist on it working as if all data were in memory. Are all the data shown of interest? If not, then only load those that are of interest. Sometimes huge datasets are useful, but then it will typically be for processing each individual record and the user expect it to be time consuming. The way you are allowing your users to use Firebird is a bit like giving them an old physical phone directory and when they complain that it takes a long time to get to the last record of the phone directory since they only turn one page at a time rather than go straight to the end, then you try to find a way to turn the pages quicker rather than showing them how it ought to be done.

A search narrowing the result set is preferable over scrolling entire tables. If that is not feasible, then you could at least train your users to change the ordering of the grid so that the last record is showing on top rather than at the bottom instead of going to the last record (Firebird works nicely if you have a descending index and issue a query with ORDER BY <field> DESC). If the number of records should be of interest, then have separate queries for counting rather than make them scroll through a lot of records.

So tell your users that yes, scrolling to the end is time consuming. It is possible to locate that last record a lot quicker, but trying to do so by scrolling through lots of records will always be slow.

I partly agree and partly disagree with Tim. It sounds as the main problem is a poorly designed application (or the use of it), but even though I agree that it is not Firebirds fault, Firebird does what it is asked to do without trying to hide that it is struggling. I think (but don't know) that this could be related to Firebird being a database based on versioning and not locking like many other databases and that unidirectional queries only also could be a reason.

A possible workaround to allow the poor design to look more appealing? Well, you could have queries behind the scenes that when users hit the 'go to last', first would issue a SELECT OrderByRow FROM <table> ORDER BY OrderByRow DESC rows 250 to 250 and if that query returned a result, then you could modify your original query to SELECT <whateveryouwant> FROM <table> WHERE OrderByRow >= :ValueOfRow250 before going to the last record of that new query. That way, the users would feel they were just going to the last record, but what you in reality did was to issue a new query which only loaded the last 250 rows (if the query totaled less than 250, then you would of course only use the original query). Hopefully, the users after clicking "go to last", never click "previous page" more than ten times (assuming 25 rows on each page)...

Set

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/03214c14-deaf-7315-47de-1e2b4c2fa67e%40ckmHealthcare.com.

[Karol Bieniaszewski]

Please answer the questions:

 

1. Firebird exact version and bitness?

2. Firebird superserver or classic?

3. Database Page Size?

4. DB cache size settings? (consider that settings have priorites like: inside db first, aliases config second, DefaultDbCachePages in firebird.conf has lowest priority here).

 

Regards,

Karol Bieniaszewski

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4GvqDG3JrKz23jS%40smtp.poczta.onet.pl.

 

[Mark Rotteveel]

MODERATOR NOTICE:

I request everyone participating in this thread from posting unnecessary
inflammatory statements like this and the previous post.

If this continues, I will not hesitate to apply active moderation on
particpants, and post that are not constructive and/or flaming will be
rejected.

As an aside, this thread might be more suitable for a list or forum with
a focus on Firebird & Delphi. Firebird-support is more intended for
general usage of Firebird (administration, SQL dialect, etc), and not
for support with language specific components.

Mark, moderator

>> [1].

>
> --
> You received this message because you are subscribed to the Google
> Groups "firebird-support" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to firebird-suppo...@googlegroups.com.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/firebird-support/81abe815-9aa3-4d7c-a24c-1ed8aeac8c56n%40googlegroups.com
> [2].
>
>
> Links:
> ------
> [1]
> https://groups.google.com/d/msgid/firebird-support/06343a39-e3d0-4285-a5c0-5519f01398aen%40googlegroups.com?utm_medium=email&amp;utm_source=footer
> [2]
> https://groups.google.com/d/msgid/firebird-support/81abe815-9aa3-4d7c-a24c-1ed8aeac8c56n%40googlegroups.com?utm_medium=email&utm_source=footer

[Midsoft UK]

Hi set

Your mobile phone analogy is 100% correct. My issue is that MySql handles it as does ADS. It's not a deal breaker we just have to be more creative. It's the same as opening a large spreadsheet and wanting to quickly get to the last few rows. People want to do it often.

At the moment we warn the user that it could take some time (we don't list all records by default anyway we give them a "show all" checkbox) when clicking this we warn the about the time but imagine users getting used to 20 years of scrolling as they need almost instantly and then after a database upgrade no they can't.  So imagine potentially 10,000 users suddenly calling our helplines complaining the system is slower than the old version :)

Customers don't understand or care about poor database design unfortunately.

Handling the End button/key is quite easy using the method above, even the skip command partially helps, we just need to detect the fact that if they keep pressing up arrow and reach the first record, we can query again.

The biggest issue is the scrollbar. If they suddenly drag right to the bottom of the grid, that's more difficult to catch.

We could simply replace the scrollbar with some buttons that skip a certain number of records etc. 

I have seen many computer users never type anything into a search box or start typing into a combobox and simply just scroll forever with the mouse. Everyone on this group understand the proper way to do it but most computer users don't.

How many of you make the ENTER key work like the TAB key on data entry ? It's wrong but because you can do this in excel etc. customers expect it. 

I think people here think I am having a go at Firebird, far from it, its just a technical discussion which not only good for our own purpose but maybe someone reading this in 5 years that is having the same questions.

Thanks for the constructive answer.

Andy

[Midsoft UK]

Hi Mark

Do you know of a group for Firebird and delphi ? Every time I search it just points to this one. 

I agree, why can't we just keep the discussions positive and technical without criticising peoples bad design etc.

Cheers

Andy

[Midsoft UK]

Hi  Karol 

I've never considered any of these I will investigate them. Thanks

Andy

[Mark Rotteveel]

On 2021-08-26 11:40, Midsoft UK wrote:
> Hi Mark
>
> Do you know of a group for Firebird and delphi ? Every time I search
> it just points to this one.

I don't program in Delphi myself, so I can't provide advice on this
point.

The reason I'm suggesting to post on a more Delphi-specific forum, is
because they are more likely familiar with the component you're using,
and - I hope - able to ask more specific questions to get to the bottom
of the problem. Scanning back through this thread, I think it lacks
information and details, but at the same time, I don't know what to ask
for.

As a general advice, you could try to use the trace facility to see
which queries are fired when scrolling, and maybe use that to try and
find the bottleneck.

Mark

[Marsupilami79]

Hello Andy,

My issue is that MySql handles it

I seem to remember, that MySQL loads the results into memory. If this happens in a kinda threaded way, they could be serving the first records to the application while still loading records in the background. As far as I know, Firebird doesn't work this way - which is good.

I have seen many computer users never type anything into a search box or start typing into a combobox and simply just scroll forever with the mouse.

How about changing the application design? Have an TEdit field and a button next to it that opens a search window where users have to enter some part of what they are looking for before you even start searching?

I saw your question about asking Delphi+Firebird related questions: How about asking them on en.delphipraxis.net (or delphipraxis.net if you understand german)? I am sure there are developers over there that have dealt with similar situations in the past.

Best regards,

Jan

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/7b2c7799-0b9e-4798-b324-ec4c603304a8n%40googlegroups.com.

[Norbert Saint Georges]

Hi,

 

Personally,

 

I will work with the provided API (firebird.pas) to only read the visible lines of your grid.

 

Norbert

 

[Midsoft UK]

Hi Jan

Yes I did try that. We have a search box on every summary form so most of the time the result set is small and this problem goes away, however there are many instances where users want to see a lot more data.  I have plenty of ideas now to go at.

Thanks

[Karol Bieniaszewski]

If you use default settings, especially about database cache, than the behaviour you get is more then possible.

 

Regards,

Karol Bieniaszewski

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/44943088-612a-4392-95d8-ab9a8114552an%40googlegroups.com.

 

[Jason (PN)]

+1 for en.delphipraxis.net

OR / AND join the UKDevGroup (it does cost) and because it is a smaller group, people tend to bring things to the meeting or even do screen shares 1:1 (I am biased as I run it).

My job used to be split 1/3 training, 1/3 consulting for exactly these kind of issues and 1/3 developing bespoke software for companies.  Now it is 90% developing Software, but always up for a screen share and chat, especially if I makes for a good talk (given by you) at the group.

 

I thought I had asked for (but I can’t find it), your test app, so I can actually look at your use case rather than hypothetically.

 

 

Jason Chapman

JAC2 Consultancy Limited

Mob: 07966211959

 

From: firebird...@googlegroups.com <firebird...@googlegroups.com> On Behalf Of Midsoft UK
Sent: 26 August 2021 14:21
To: firebird-support <firebird...@googlegroups.com>
Subject: Re: [firebird-support] Firebird goto last and big comboboxes with Delphi

 

Hi Jan

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3c20334f-239b-4916-a919-26eff3f49c8fn%40googlegroups.com.

[Jason (PN)]

“I thought I had asked for (but I can’t find it), your test app, so I can actually look at your use case rather than hypothetically.”

 

Doh – I meant the .pas / .dfm for full source for the test project.  I haven’t used MySQL in anger and would be interested what “tricks” happen and where they are implemented (FireDAC / Driver).

I am pretty sure a .last in FD for FB would be a kind of “while not eof do, read row into memory, next”.

 

Jason Chapman

JAC2 Consultancy Limited

Mob: 07966211959

------------------------------------

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/2c4301d79aa3%24129b8dd0%2437d2a970%24%40jac2.co.uk.

[Midsoft UK]

Hi Jason

I think I joined the group already. I'll check, I certainly meant to . I'm always happy to share what we've learned.   FireDAC is actually really good.