2. PHPRunner General questions
  3. [SOLVED] Speeding up large and complex table views
This topic is locked
[SOLVED]

Speeding up large and complex table views

 		12/8/2023 5:53:34 AM
PHPRunner General questions
mbintex author

I am thinking of ways to speed up my applications where there are tables with much data and many fields, for example address tables.

In this case you always have the situation, that in list view you show only a part of the fields you will have for add, edit and view. But as to my knowledge PHPRunner is always querying all fields that are in the base query of this view.

If I benchmark a query showing all or most fields of a table this is quite slower than the same query fetching only let´s say five fields for the list.

So I ask myself, if I could speed up the solution, if I change the SQL query for list page with the Before SQL Query event and the $strSQL variable?

Furthermore - if I have grouping and sorting in the list view (in my case grouping by initial and sorting by shortname), how can I speed up with indexes? I already have an index on initial and shortname fields. Would it make sense to have a combined index on both fields together or to create a generated column of both and index that?

Further idea: Would it make sense, to have a view only for list page purposes with a slender query consisting only of some fields and for add, edit, view purposes link to the normal view pages with all fields?

Last idea: Has anybody ever used partitioning for example on users, since you normally only need the data of user A or B?
HJB 12/8/2023

https://xlinesoft.com/phprunner/docs/how_to_work_with_tab_2.htm

... in older PHPR versions there was a functionality called "Step-by-Step" or say,
one could create TABS and assign those very fields to appear in each tab.

Not knowing if this would really speed up things, yet as things like that are only
calling a portion of the whole fields under step-by-step, I think, it should/would do ...
admin 12/8/2023

PHPRunner executes SQL query the way it is on SQL Query screen. However, PHPRunner only retrieves as amny records as it is required for the current page and it is hard to believe that retrieving 20 records can cause this kind of issue, no matter how many fields you select.

Groupping and sorting can definitely be an issue with can be solved with the help of indexes. It sounds though that you are trying to guess which indexes will help and this is not how it works. Instead of guessing you need to print exact SQL queries, run them manually and see which one is slow. The following article is still extremely relevant:
https://xlinesoft.com/blog/2011/04/08/improving-perfomance-of-phprunnerasprunnerpro-applications/
mbintex author 12/9/2023

there is no current "issue". I only want to be prepared.

And of course you can first enlarge the resulting dataset to more than twenty records plus all filters and where-tabs require the whole data set to be scanned, no matter if in the end only 20 records are shown.
admin 12/10/2023

Not sure if you are arguing or agreeing. The point is that slowness 99% of the time comes from slow database queries and there is a clear and defined way to fix that.