Major latency issue with one table

[max4880]

You are using DaDaBIK version 8.3-Lerici enterprise, installed on 05-15-2018 (installation code: 133425a7c786a0c929), the latest version of DaDaBIK is

Found
The document has moved here.

released on --
You are not running the last release of DaDaBIK, the release you are running might have bugs and security holes, see the official change log for further information. You can upgrade DaDaBIK here.

PHP Version: 5.3.29

mysql version: 5.6.39-cll-lve

Web server: Apache

Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.181 Safari/537.36

--------

Hello,

I am having a latency issue with one section of my application. Here’s how the database is structured:

Table Appointments_and_Jobs contains appointment details and has a Customer field, which is a select single with Lookup table pointing to Customers and linked fields First_Name, Last_Name, and Telephone.

There are also tables Job_Details, Job_Visits, and Job_Documents. Each one has Job as a select single that points to Appointments_and_Jobs.

Currently there are about 25000 customers, but only 90 or so appointments so far. I can view the tables Customers, Job_Details, Job_Visits, and Job_Documents with no problem. However, when I go to Appointments_and_Jobs there is a huge latency issue. The application takes a very long time to display anything. As in 30 seconds or more each time. This happens when I just click on the link to the table in the menu, try to view a specific record, try to edit a record, or try to add a new item.

If I manually run queries on the MySQL database, the results are instant. I also optimized all the tables in the database, but this had no visible impact.

Please help.

Thanks,
Max

 

[eugenio]

Hello,
the Appointments_and_Jobs table has just 90 records? Have I well understood?

Best,

 

[max4880]

Eugenio,

Yes. There are currently between 90 to 100 appointments, but over 25 thousand clients (I imported data from an old database).

The screen loads up until it displays the "Customer" field and then freezes. Then eventually it displays the rest of the record. The same when I click on the table link in the menu. I took a screenshot of it frozen and of what it looks like when loaded. They are in my Google drive, here:

https://drive.google.com/drive/folders/14Egiy24PfEdA4EHtZ9H85meiEfWmPvn8?usp=sharing

As an experiment, I created a test user and in permissions for the Appointments_and_Jobs table, I denied the user permission on the Customer field. The latency disappeared, but, of course, the user could no longer see which customer was associated with the appointment/job.

It seems to me that the application freezes because it may be trying to load information on all the records in the Customers table (?) and this takes long because there are lots of customers. If I am correct, then is there a way to avoid that?

Thanks,
Max

 

[max4880]

Eugenio,

If I look at the "page source" in Chrome, meaning what the PHP code echoes out as HTML, I see that it creates an object:

"<div class="save_buttons_container"></div><span class="form_fields_set"><div class="form_row"><label for="Customer">Customer<span id="Customer_req" style="color:red">*</span></label><span class="search_operator"></span><span id="Customer" class="form_input_element"><div><div class="select_element"><select data-placeholder=" " disabled name="Customer" onchange="javascript:show_hide_text_other('select_single');" class="searchable_select">"

with every single record from the Customers table in it. Is there a way to prevent that? The only way new appointments are added in my program is as children in the Customers table. Meaning that the user selects a desired customer, goes to the Appointments_and_Jobs section and clicks "Create new item."

I included screenshots to make my question easier to decipher. On my Google Drive here: https://drive.google.com/drive/folders/14Egiy24PfEdA4EHtZ9H85meiEfWmPvn8?usp=sharing

"Customers Table - Create new Appointments_and_Jobs Item.jpg" is the view of the Customers item where the user clicks to create a new appointment.
"Create New Appointment.jpg" is the insert new item into Appointments_and_Jobs screen that comes up.
"Page Source 1.jpg" is the HTML page source of that screen. Note that every single item in the Customers table appears to have been read from the database and put into a Select Single element. I believe that the process of reading 25 thousand records and making the HTML element is what is taking so long.

What I'd like to do is prevent that from happening. I'd be content with not even seeing the customer information in that window. I attempted to achieve that by denying the user permissions to the Customer field (see "Permissions.jpg") however, this resulted in the field not being set at all, so the appointment had a Customer ID of 0.

Thanks,
Max

 

[eugenio]

Hello Max,
such behaviour is normal, it's the browser that is freezing (not DaDaBIK) while trying to load the list of options. You can avoid it by setting
User-friendly searchable? YES
Use ajax to load options? YES
in form configurator for that select_single field.

Best,

 

[max4880]

Thanks! This worked!

 

[eugenio]

Perfect!