Long Load times for queries

QuestionsLong Load times for queries
Rheal asked 5 years ago

It is taking upto 45 seconds for a very small table to load in my grid.

2 Answers
Rheal answered 5 years ago

Actually it taking 1 min 30 secs to load the page.
There are 6 tables and 2 charts on the page.
mysql and oracle DB connections.
The phpgrid layout is in a separate file and is an include in the main page.
The 2 charts load very quickly and their data is from mysql
The first table loads fairly quickly.
all the rest of the tables seem to load in sequence, each taking 20 seconds or more to load.
the next table will not load until the previous one is done.
These are very small queries the largest containing 87 records.
The queries run very fast when pulled directly from mysql or Oracle 0.007 seconds to complete.
But when pulled from phpgrid it can take over 25 seconds to pull.
Can anyone point me in the right direction?

Abu Ghufran answered 5 years ago

If your table uses mysql indexes, one known issue is grid uses subquery to fetch count of rows.
e.g. SELECT count(*) from ( SELECT …… )
Now the actual query goes in subquery, which in mysql restricts use of mysql indexes.

Solution could be to use custom e.g.

// you can provide custom SQL query to display data
$g->select_command = "SELECT i.id, invdate , c.name,
i.note, i.total, i.closed FROM invheader i
INNER JOIN clients c ON c.client_id = i.client_id";

// you can provide custom SQL count query to display data, remember to alias as 'c'
$g->select_count = "SELECT count(*) as c FROM invheader i
INNER JOIN clients c ON c.client_id = i.client_id";

The data is loaded with ajax calls and uses session information. The delayed loading of grids (one after another) could be due to session file locking, causing wait time for second http ajax request.

In case issue is not resolved, you can email (db sql + grid code) to our support team at [email protected] for further analysis.

Your Answer

1 + 6 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!