This solution can still be used for phpmaker, asp.netmaker and aspmaker as well:
INTORDUCTION:
NOTE: there is alot of reading here as I want to show were am coming from so one can understand and also be able to improve on this or correct me if am wrong.
Ive been using this solution but I feel it might not be so obvious to some users or some might have something better so i decided to post it here
Ive noticed some users have pointed out that XXXmaker generated list pages don't rate well on performance most of the time, not only that but am sure most of you have noticed on most of the projects some pages take sometime to load.
But most of us will brush it off coz we have been able to get cool lookup features, export features, parent child lookups and so many other XXXmaker features as we all know, so a performance lag becomes kind of minor cost if you think about it.
SO ive been trying to think of ways to still use the cool XXXmaker features and still be able to beat performance since you have alot of power to customize your code even the template files, if you put a little thought to it there is alot you can do to improve what you don't like about it
----------------------------------REASONING FOR POOR PERFORMANCE:--------------------------------------------
Lookup table functionality is one of the coolest features i find in these products but ive noticed that overusing it is what affects my performance most of the time especially on list pages.
HINT: always turn on debug mode after completing a given page so you can see how fast it loads and what its doing during the process
For example I have a table which need like 3-7 or more lookups, on the add/edit pages I use all the 7 lookups but on the list page am only showing fields that require 3 lookups.
SO I TURN ON DEBUG MODE:AM USING A WEAK MACHINE FOR THIS, also note the table has alot of fields as well
By default i load 10 record on the list page and i see he page takes around 3 - 6 seconds to load, but in the debug logs you can notice alot of sql calls to the db apart from the one for your main table.
CAUSES:
-XXXmaker (before v2019) has to query for each lookup display value for each row on the list page, so if I am showing 3 lookup fields, those will be 3 more queries, meaning to show 10 records it has to do extra 30 queries to the db.
-running the query for the list page of my table eg select * from my_table, i see that it takes about 0.0026 seconds in phpmyadmin, so I know that its the extra work on the list page that causes it to go to all that time.
-our goal is to view a list page with as few calls to the db as possible.
NOTE: This is not a problem of XXXmaker in my opinion infact i personally prefer it this way, sice they still give you the option to customize whatever you want about it
----------------------------------REASONING FOR POOR PERFORMANCE:--------------------------------------------
--------------------------------SOLUTION-------------------------------------------
For this solution you can pick a table of your choice on that has alot of lookups fields, also keep debug mode on all the time the you will be able to compare the result of this and your initial table.
BASIS:
I feel that it would be much better if instead of having to do a single query to get each lookup value, i would want to just join to the respective table at once and retrieve the values.
SO my idea is for every table to show on list page, I instead create a view out of it and join to the respective tables and return all display values i need to see on the list page, that way that view will never need to do lookups while on the list page.
for example I create a view and name all my lookup display values with str_xxxx to remind me that these are for lookup display values.
1.-create view vw_my_table as select my_table.*, c.name as str_country_name, ct.name as str_city_name, d.name as str_industry_name from my_table my left join country c on my.country_id = c.id left join city ct on ct.id = my.city_id join industry d on d.id = my.industry_id
NOTE: I am using a left join search that even record which don't have lookup values will still show up too, am also returning all rows in that table so i can still use it for add.edit.
FOr your own implementation join to as many lookup table as you want to show on your list page
-Aslo remember to check how long this view takes to load in phpmyadmin, mine now takes 0.003 seconds
Setup this view for list page (main purpose of all this).
-sync this view into phpmaker.
-in field settings tick all non lookup fields you want show on your list page, i mean those that dont have lookup but you want them on.
-in field settings tick the str_xxx filed you want to show on list page, (these will replace the initial lookup fields on the list page)
-in field settings untick original lookup fields from the list and export
-generate all files.Seeing the results for list page load time
-with your debug mode still enabled if you visit your new list page you will see it has around 1 sql query query and that it takes short time to show, foe example mine now takes 1-2 seconds to show 10 records. It should not be running sql queries for the default lookup fields if it still is then you have not disabled some lookup fields from list & export since we already have their display values in str_xxx it should not need any extra queries for list page.
NOTE:This is a huge improvement for me coming from 6 to 1-2 seconds. and it get better
- Proving that it works
My tale has over 500000 records so i tested how performance will be after this change.
I noticed that now it takes 4 seconds for this new list age to load 10,000 records, while it takes the previous list page the same time to load 10 record, I did not want to test it for the same record as i knew it might time out.
--------------------------------SOLUTION-------------------------------------------
--------------------------------Implementing add / edit on this view-------------------
For a view to e used for adding or editing it must have only fields for a specific table, preferably the main table (you can google edittable views to know more), so all we have to do to is to avoid considering all str_xxx fields during add & edit
-in field settings ensure all str_xxx fields are not enabled for add/edit, this is obvious since the felds we want are the real lookup fields
-in field settings ensure the original lookup fields are setup for add/edit with thier lookpu settings.
-regenerate all files.
-you should see that this view can aslo add and edit just like the previous original table
--------------------------------Implementing add / edit on this view-------------------
--------------------EXTRA HINTS----------------------------------------------
NOTE:You need to always experiment a little and also try to understand how their code works coz it will be key in knowing where you can tweak or where you can customize to improve your value for using it, so you can add more values to using XXXmaker
- you can also just use a custom view to impliment the above instead of putting it into the db.
- if you have used a custom view ensure to set $this->UpdateTable = "my_table"; during row_inserting or row_updating before return true;. This is coz a custom view is a stored sql query so i can not be used for insert but the above line of code can help you tell phpmaker to formulate its insert and update scripts using the actual table.
-If you have alot of deatil tables connected to the master table, ensure to minimise on using the table setting of "list page->Detail record count" as it also does an extra query for each record on the list page.
--------------------EXTRA HINTS----------------------------------------------
You are welcome to adding more tips on the same or advise where you feel this is lacking.