Grid application take too long time to load

Scriptcase 8 Discussion
#1

Hi There.
I have a situation and do not know exactly where the problem is.
I have two tables: client and client_addresses.
The client has about 5000 records, and every client has in client_address one or two addresses.
A client can have multiple addresses specified in the table client addresses, but one is the default.
If I create a grid application that take data only from table client it’s run ok (fast).
If I create a grid application that show the client and the default addresses it’s work very slow, it’s take about 20 sec.
I tested this in two way in SQL Select Statement.

  1. I use subselect query like
    SELECT
    id_client,
    name,
    (SELECT address FROM client_address WHERE id_client=cl.id_client AND default=1) AS address,
    email
    FROM
    client cl

  2. I use INNER JOIN like
    SELECT
    client.id_client,
    client.name,
    client_address.address,
    client.email
    FROM
    client
    LEFT OUTER JOIN client_address ON client.id_client = client_address.id_client AND client_address.default=1

The interesting think is that I run this two query in other context, the data is load and show very fast.
For example, I created in a blank application a simple table that is filled with data using the query mentioned above and it’s loading very fast.
So it’s not a problem with speed of server.
If I use exactly the same query but in grid application, the application it’s load very slow, also when navigated from a page to another or when I clicked the advanced search or quick search.
Can be this a bug in grid application ?
If not, why in grid application work sow slow and otherwise work fast ?

Thanks

#2

I think your problem is that there is no LIMIT being generated in your sql.

If that is true, then the query is not what is taking time, it is the entire 5000 records getting transferred to your browser.

Change your “Pagination” from Total to Partial, and set a number of lines.

If that is not the problem, then you need to check if your client.id_client, client_address.id_client, and client_address.default are all indexed.

But the fact that it runs fast in a blank application (which is probably only displaying one record) makes me think it is not the indexing, but the transfer that is taking the time.

Dave

#3

It is selected partial and the limit is 15 / page.
Like I say above, it’s working slow also when navigate.

#4

Can you give this a try:


SELECT[INDENT]c.id_client,
c.name,
d.address,
c.email[/INDENT]
FROM

[INDENT]client as c,
client_address as d
[/INDENT]
 WHERE[INDENT]c.id_client = d.id_client 
[/INDENT]
 AND[INDENT]d.default=1[/INDENT]

and I am assuming that client.id_client, client_address.id_client and client_address.default are all indexed?

Dave

#5

Yes are indexed

Using this

SELECT
c.id_client,
c.name,
d.address,
c.email
FROM
client as c,
client_address as d
WHERE
c.id_client = d.id_client
AND
d.default=1

Grid application is loading fast, also navigation is fast but advanced search it’s not working.
When i push the advanced search, it’s show that page is loading and nothing happen.
if I select in sql query fewer fields, advanced search it’s working but after a few tens of seconds.
So using this method, grid work ok but advanced search not work or is very slow, may be it’s a bug in scriptcase.

#6

Yes, it sounds like scriptcase bug.

I always use equi-joins (like the one that I gave you) simply because they seem to make more sense to me than inner/outer/left/right.

I can see no reason why the two queries would take dramatically different times to execute, if indeed phpmyadmin responds in approximately the same time for either query, then I have to assume that scriptcase is at fault.

Dave

#7

Dave,

Sir, thank you for the help

i’m using the latest 9.4.015 November 11, 2019, I have 2 billion+ records in detail records and using inner join is make my pc hangup or not responding, but using equi-joins is fast.

Hope scriptcase fix this.