Copy LIST Page - Designer - want lightweight page for guest users

You're not saying a very important thing: how many rows do you intent to show your guest users? - infinte scroll, lazy load with all data, a predefined number of records?

The best solution from my POV would be to use an external library like this one applied to a copy of a list page and redirect guests to it.

Anyway you should be aware that you can't display that many DOM elements without "blocking" the browser - you have to listen for scroll events and display subsets on-demand for current scroll position - if you intent to show lots of data at once....

There are few things you can do here. None of them are ideal.

1. Limit the amount of data that GUEST users can see. That will definitely make GUEST page load faster and not much to change in the application itself.
2. Create an additional list page copy and remove features like filters. Maintaining two copies of the same page means extra work but this is the only option you have if you need to load the page faster.
3. Look into database optimization and into switching to a better database. SQLite is a file based database and definitely slower than MySQL or SQL Sever or Postgre. Also, it is less reliable as well and having a database this huge in SQLite is a disaster waiting to happen.

FYI -- Consider that the default rows shown/paged predefined 20 for either GUEST or a logged in user. The problem I have observed (using debug) is that the SQL costs for the filter setup and the ALL DATA counts/totals make the page load more painful. These SQL queries scan the total set of rows for the Counts/Amounts totals. Extra fyi -- the 8.2 million rows is actually a summary table of a bigger 182 million rows table.

1. This database is READ ONLY.
2. Existing performance considering the size issues is truly amazingly good.
3. At least 60% of that 110GB filesize is all indexes.

Sergey - I like your replies. Note again, it is the filter and the ALL Data counts/amounts at the bottom of the grid that make page loads slow. Without them - loads are comfortable. Debating the DB used is not really useful but all my research shows that for pure read-only purposes sqlite can be amazingly fast and it can grow (but it does not grow - in a production environment) to incredible sizes. The DB is create/loaded offline and VACUUMed etc. LIVE DB is static. In this use case, you might be a little impressed. It has 68 tables totaling 347,980,000 rows.

Cristi - An interesting reply and suggestion. The pages never let the user show so many rows at a time to overload resources.

I will explore the COPY of the list page. I asked this question originally to try to validate my theory that this was indeed the best path forward. But I also do/did not know how to know which page to show based on the user login status. I'll research this. Meanwhile I have removed the grid filter as it was not all that essential.

MANY MANY Thanks for your replies.

Just wanted to say that if the database is readonly, meaning that data is static or do not change often, you can calculate those totals once (recalculate when needed) and speed up the page both all types of users. I don't know if this is going to work in your scenario but this is definitely an option.

We recently had DevClub webinar dedicated to this topic.

I need to revisit this a bit. I have created a COPY of my list page, called list1.

list - shows all data totals (at a performance hit)
list1 - intended to be lightweight - page totals only

I want to display list1 to NON logged in users (guest) -- so they get the fastest performance, (not totaling 8 million rows). I want the standard list page (with all data totals) to be seen only by logged in users - so they get the benefit of the full data totals accepting the performance hit.

I created menu items for these two pages and I see them in the secutity pages, I select them for admin and for guest accordingly and yet regardless, all users logged in or not, see both pages, both menu items.

Do I need some event code that checks the logged in state and picks the (list or list1) page to show? I was thinking I can jst define the pages with the security editor. Seems like this one piece of the puzzel is what I am missing.

@KeithS

Let's BASICS give a deeper look first:

A.) SQlite vs. MySQL

MySQL is generally faster for data access, especially when dealing with larger databases and more complex queries. SQLite is lightweight and great for smaller, simpler applications, but it doesn't scale as well as MySQL. So, if speed is a big concern and you're handling a lot of data, MySQL is usually the better choice.

B.) Content Display to "Guests"

Sure, may sound totally crazy here on the issue, yet questions remains for reasons WHY e.g. Google succeeded on it's single-lined entry page since decades.
In other words, whether CREATE A COPY PAGE or else other solution to somehow to come near to your needs, the question remains: Is it useful to confront
guests with an even limited bunch of data (e.g. 10 lines per page or so) to struggle through all the massive content (110 GB, wow) until the "wished info" is going
to be found? What I would like to say is: Why not trying to emulate the success story of Google by single-lined entry field for SEARCH mission(s) to your guests?
I mean, to make maybe good use of the FIELDS > SEARCH AND FILTER SETTINGS > HIDE DATA UNTIL SEARCH feature inside PHPRunner?
Sure, it would require to somehow explain to GUESTS why doing so, yet this way seems to own to provide a kind of solution by very simple built-in functionality
inside PHPRunner to simply get activated by only one mouse click. Well, we as INSIDERS know what to do when a true bunch of data is hitting our screen, yet
if an "ordinary web user" is required to first run a learning curve on how to find that very information he is looking for, the risk is high that such user simply jumps off,
by means of simply having no motivation to do so PLUS not really having the time to do so.
I fuly agree with you, such single-lined search field on page load certainly owns a kind of being to simple, yet again, question remains WHY Google succeeded this
way to first let the user swing to into action for what specific "keyword" or "text string string" he wants to see a search result(s) oriented listing later on.

EASY IS FIRST, still in these days ..., and web visitors would definitely honour it ..., under simple "Time is Money" user aspects.

Just my two little cents on the issue.

Thanks HJB for the reply.

But again, the SQLite DB will remain. This website is public: https://smurfsearch.electionwatch.info and the page in question is the 1st menu item "FEC Stats By Smurf". I'll add here some well deserved accolades that phpRunner has been fantastic and very seriously instrumental in making the project possible!!! I have actually been with phpRunner since V2 (but do not consider myself an expert - its evolution has been fantastic - anxious for V11 but my interest there is to wait for the LINUX VERSION - I've managed a bit to tolerate Windozs since its beginnings). The ONLY Windows machine I have is my Win10 phpRunner VM because I need it. But -- Many Many VERY SERIOUS GRATEFUL Thanks to Sergey et al (btw - AG's, Congressmen, Senators, CIC/POTUS love the Charts!!!). The SQLite DB file is currently 117GB file size (wow). For the grand total of tables and the approx. half billion+ rows collectively within the file (not including all the indexes), it's performing fantasitically well. This site has been built 100% for free over 18 long months; There is zero financial or anything motivation to change it. It is static and 99.99999% READ-ONLY. Further development of the site is nearing the end (however the subject matter is not). I actually do favor MySQL in many ways... I did attempt to move the DB and abandoned it deciding the work was not worth it and I actually started to believe the performance would drop for certain indexing reasons (collation types) I implemented in the beginning. Also, the physical storage of the data I believe would have swelled to a much larger dimension than what exists today. It takes a pretty big sized hosting account to run this thing. Long story... In finality, changing Databases is out of the question. This is NOT the usual businees kind of app with database adds, updates, inserts. The more you might get to know about what it is and whats inside it, the more you might be surprised at how very well SQLite is doing the job. I'm a programmer of 50 years of something of everything. I'm amazed myself.

Again, as to the the matter of showing "page" or "all data" totals at the bottom of a grid. Page totals are fast indeed. "All data" totals are slow when showing the page means that a query must occur that totals 8+ million rows (which is already a summary table itself of 182 million rows). For GUEST access, I want to show a page that has simple page totals. As guests visit the site and try to casually view and search things I want minimal impact to the wite and reasonably fast results for the guest. Researchers (the only people that login) need all data totals and these results are meaningful to them.

I have made a copy of the list page (list1). I just need insights as to how or where to put some (event?) code in, that allows the site to show the list1 page to a GUEST and the LIST page to a logged user. I see the security events API, IfLoggedIn etc kinds of things. Just missing some key tiny piece to enable me to clean up this one area of slowness. For someone who knows this (probably) simple answer, total Thanks in advance!

Congratulations @KeithS,

You surely have the world's largest public database in SQLITE.

The response time, in general, is very good.

Greetings,
fernando