Large file performance (520,000 records)

[Busman412]

I'm running dadabik on an xampp localhost. Small database files with approx 5,000 records are no problem, but I have 1 file that has approx. 520,000 records.

With a clean PC reboot and a new start of the xampp server the large dadabik file loads up quickly and the first couple of quick searches I do return a result in a couple of seconds. As soon as I try a "is not empty" or "is not null" filter I get spinning wheels and dadabik hangs. I have searched the forums and Google and tried theses suggestions: index all the fields I regularly search, take all the "Nulls" off the fields, but that doesn't fix the problem. Restarting the xammp server doesn't fix it either, so I have to re-boot the PC.

Looks like a memory problem but I can't figure out how to solve it. Any suggestions?

 

[Busman412]

Info for support:
You are using DaDaBIK version 10.1-Manarola pro, installed on 05-31-2020 (installation code: 156015ed382dfd6c8f), the latest version of DaDaBIK is 10.1-Manarola released on 03-09-2020
You are runnning the last release of DaDaBIK
PHP Version: 7.3.18
mysql version: 5.5.5-10.4.11-MariaDB
Web server: Apache/2.4.43 (Win64) OpenSSL/1.1.1g PHP/7.3.18
Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:77.0) Gecko/20100101 Firefox/77.0
URL installation: http://localhost:412/dadabik/

 

[eugenio]

Hello,
520,000 records shouldn't be a problem; does the table contain a lot of lookup fields?

It's not clear if you finally get a php error or not, but I would also try to increase this parameter in php.ini
https://www.php.net/manual/en/ini.core.php#ini.memory-limit

BEst,

 

[Busman412]

Thanks Eugenio.

Yes, I did have a lookup table for one field. All fixed now.

B

 

[eugenio]

One lookup field is not much is the linked table very big and/or you linked a field that is not primary key? This would be the reason of the problem.

Best,

 

[Busman412]

Thanks Eugenio. I looked again at the Help tips in the "Forms Configurator" > Master/Details view.

My data has a 1 to many relationship - 1 x Master record > many documents

My tblMaster table has a list of names (520,000) with a unique id (fldNameID). Each name has some documents associated with it, eg. certificates, photos, books, PDF's etc. These are all listed in my tblDocuments table with their own unique ID (fldBarcode), the foreign key (fldNameID) and other details.

In the "Forms Configurator" for my "tblMaster" form > fldNameID I have these settings in the "Master/Details view" section:
Items table name: tblDocuments
Items table foreign key field name: fldNameID

It works well and search results are quick, and the list of associated "Documents" are displayed at the bottom of the details pages for each record.

Thanks again for your info and tips.
B

 

[eugenio]

Hello,
I think I am still not getting the reason of the problem (master/details views shouldn't affect search performances) and how you fixed it but if everything is working as expected that's fine.

Best,

 

[Busman412]

Hi Eugenio,
Sorry, but I don't know what the reason for the problem was. To fix it I re-installed dadabik, checked my table structures, primary keys and master/details view and now everything is working as expected.
At least I know what to look for if I have that problem in the future......and I will read the help information next time!
B