Recent

Author Topic: Problems with DBGrid choking on larger files  (Read 1689 times)

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Problems with DBGrid choking on larger files
« on: September 30, 2020, 06:28:02 pm »
DBGrid chokes on large DB files; e.g. 100,000 rows - about 100 characters of data in each row. The main issue seems to center around the Last function. When the user slides the scrollbar to the bottom of the DBGrid, the Last function is triggered and my program crashes with a memory overload. Right-clicking on the scrollbar and selecting the Bottom option results in the seem fate.

Using the Locate function - while helpful for smaller DBs - won't work on larger ones. Same memory overload problem with the same Last function.

This is an issue that's been discussed by many over the years. I am using SQLite3 DBs. Can this problem be solved by using a different database?

I've always wondered if the solution might be centered around the dgThumbTracking option. It seems that the concept around dgThumbTracking is that the user can slide the scrollbar to a desired spot in the file - but no data is read and no data is displayed until a mouse-up on the scrollbar.

With a smaller file, say under 50,000 records, the user can use the scrollbar-bottom feature to get to the last record AND proportionately adjust the height of the scrollbar. Now, he can profitably use the dgThumbTracking feature. It "feels" like only the visible rows have been read from the DB. Clearly such a small number of rows will not choke memory.

Any good solutions out there?
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Problems with DBGrid choking on larger files
« Reply #1 on: October 01, 2020, 12:50:36 pm »
This is an issue that's been discussed by many over the years. I am using SQLite3 DBs. Can this problem be solved by using a different database?
I don't think the problem is the database (or it's components).
I think the problem is TDBGrid itself.

What database and what components are you using?

I tried TIBQuery/TIBDatabase with 100.000 records and it works fine with TDBGrid.
Using 4.000.000 records it chokes with a heap overflow error followed by an Out of memory.
« Last Edit: October 01, 2020, 01:07:02 pm by rvk »

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: Problems with DBGrid choking on larger files
« Reply #2 on: October 01, 2020, 05:44:36 pm »
I'm using SQLite.  100,000 "skinny" records with only a few columns is not a problem. My DBGrid has 13 columns of data per line and it chokes at about 90,000 records. I only have 8GB of RAM.

I'm hoping that a TeeGrid component from Steema might solve the problem.
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Problems with DBGrid choking on larger files
« Reply #3 on: October 01, 2020, 05:49:10 pm »
I'm hoping that a TeeGrid component from Steema might solve the problem.
If just doing TSQLQuery.Last is also a problem WITHOUT connecting a TDBGrid, then your problem lies in TSQLQuery.

Does one of you columns have a BLOB?
100.000 * 100 characters is 10.000.000 bytes/characters. That's just over 9 MB. Not even near your 8 GB  :P

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: Problems with DBGrid choking on larger files
« Reply #4 on: October 01, 2020, 06:00:46 pm »
One of the fields in the DB has a blob - but I don't include that field/column in my DBGrid. 

Yes - perhaps the problem lies with TSQLQuery because it is always the TSQLQuery.Last function that precedes the RAM overload/crash.
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Problems with DBGrid choking on larger files
« Reply #5 on: October 01, 2020, 06:10:52 pm »
One of the fields in the DB has a blob - but I don't include that field/column in my DBGrid. 
Why is it in the query then?
Try removing all fields you don't use in the grid to see if it helps.

Yes - perhaps the problem lies with TSQLQuery because it is always the TSQLQuery.Last function that precedes the RAM overload/crash.
Even if you do .last with the query, the connected tdbgrid will always follow. So testing with .last should be done without a connected tdbgrid (but I expect it still crashes out of memory because the tsqlquery has buffering).

Sieben

  • Sr. Member
  • ****
  • Posts: 310
Re: Problems with DBGrid choking on larger files
« Reply #6 on: October 01, 2020, 06:12:05 pm »
Quote
...but I don't include that field/column in my DBGrid.

But it is included in your query...? Apart from this: why fetch such a number of rows in the first place? What are the users supposed to do with it?
Lazarus 2.2.0, FPC 3.2.2, .deb install on Ubuntu Xenial 32 / Gtk2 / Unity7

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: Problems with DBGrid choking on larger files
« Reply #7 on: October 01, 2020, 06:35:53 pm »
Only the fields shown in the DBGrid are included in the Query.

Users often sort the data on a column (works really fast) and then want to scroll to an area of the file with results they are interested in. It's that scrolling that leads to memory problems with Query.Last.

Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Problems with DBGrid choking on larger files
« Reply #8 on: October 01, 2020, 06:43:59 pm »
Only the fields shown in the DBGrid are included in the Query.

Users often sort the data on a column (works really fast) and then want to scroll to an area of the file with results they are interested in. It's that scrolling that leads to memory problems with Query.Last.
100.000 normal records without blob shouldn't cause any memory problems with TSqlQuery.

Did you test SQLQuery1.Last yet without any connected TDBGrid???

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: Problems with DBGrid choking on larger files
« Reply #9 on: October 01, 2020, 07:07:53 pm »
Whether or not a DBGrid is connected, I have the same problem when using the Query.Last function whenever the query contains several fields (but no blobs) within a large DB. It seems it's a function of the amount of data. Skinny DBs can have more rows....wide DBs can only accept a smaller number of rows.

For example:
    Grid.DataSource.DataSet.Locate('IDNo', '100000', []);   does not work while

    Grid.DataSource.DataSet.Locate('IDNo', '50000', []);   does work in the same file

To me, it seems the solution is for the DBGrid Dataset to only include the ID # (just one column). Then when there is a change in row number - Select all the desired data for that ID and populate each cell in that row of the grid with the GridDrawColumnCell event. Is that approach workable?
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

Sieben

  • Sr. Member
  • ****
  • Posts: 310
Re: Problems with DBGrid choking on larger files
« Reply #10 on: October 01, 2020, 07:31:57 pm »
How about introducing sort of a header control for that grid where the user types the beginning of the section he or she is interested in and then fetching say top 500 or so sorted by that column? It might after some time of getting used to it even be more convenient than fumbling with that scrollbar to get there.
Lazarus 2.2.0, FPC 3.2.2, .deb install on Ubuntu Xenial 32 / Gtk2 / Unity7

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: Problems with DBGrid choking on larger files
« Reply #11 on: October 01, 2020, 08:00:46 pm »
This seems to be working
1. include only the ID # in the DBGrid Query - thus a very skinny dataset
2. SELECT just the data for a row on a change in ID
3. fill each cell with the GridDrawColumnCell event from the data saved in step 2.
Some more testing is needed but it looks promising - if not kludgy.
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Problems with DBGrid choking on larger files
« Reply #12 on: October 01, 2020, 08:20:07 pm »
I'm still very puzzled as to why 100.000 just won't work for you.

I made a small sample-project which creates a database with 250.000 records with 10 fields (which 9 of them hold 15 to 25 characters).

Unpack, put a sqlite.dll into there and open the project.
Run it, let is sit for a minute while it creates the database.
Then in the main screen select First/Last and it will go to the last record with 250.000 as recordcount.

So what is different in your project????

Note: using a 'selection screen' for the user is always advisable... but 250.000 shouldn't really be a problem if you don't do anything weird.

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: Problems with DBGrid choking on larger files
« Reply #13 on: October 01, 2020, 09:03:38 pm »
I'll give your program a test.  Thanks.
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: Problems with DBGrid choking on larger files
« Reply #14 on: October 01, 2020, 09:30:15 pm »
Your test program worked perfectly, so my problem can't be any limitation of SQLite or DBGrid. When (and if) I get to the bottom of my problem I'll report back.
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

 

TinyPortal © 2005-2018