This has been a regular occurrence for years (but has only now prompted me to make a thread), but any self-hosted app that I own that has more than 200 screens literally takes *60 seconds* or longer to load up the page for use. Some of my clients have started complaining, but unsure of how to fix this. I'm thinking of limiting the page from loading 100, rather, 25 each, or something like that, but was hoping for a better solution. Any help would be appreciated. Cheers! David https://btmods.com/chat

Internally there is a 1.5 second delay for every screen you have over a quantity of 10. Oh wait, that fools day ready passed. :-)

It feels like ten years.

MrDavid, Is this self hosted? Or in an app?

Self Hosted - the Screens page (where it loads up all the screens for whatever particular app your working on). :-( David https://btmods.com/chat

MrDavid, I'll take a look to see if you would be able to optimize the screens.php file... but my guess is that you are running too many queries which might be overloading your MYSQL server. If you are using shared hosting on a web host like Hostmonster, Bluehost, HostGator, etc they will often slow down the number queries you can perform on your databases so as not to hog down the other users on the server. Loading 100s of items on the screens.php page actually means you are making 100s of requests to your database tables which can hog your server down. For example, if you think of the different variables requested from the database (eg: Screen Name, URL, item ID, Plugin, JSON, etc) per item the quest is easily in the 1000's. I would check with your host to see if they are throttling any requests to your server. You may want to limit the items, or upgrade your hosting plan - as needed. -Cake

MrDavid - sent you a PM on a slightly off topic subject. Kind of related.

Which host are you using? My self hosted on hostgator loads the page pretty much instantly... Never longer than 3 seconds tops

My server is fast - pages load instantly, it's just for the apps that have tons of screens that the screens page will take forever to load up on. I don't -think- it's the host but it could very well be. I'll do some more testing.. Cheers, David https://btmods.com/chat

Maybe try a test installation after grabbing the latest download. I know the BT team updates the self hosted files to squash small bugs, and don't update the version number. For example, version 3.00 from today could have different contents from 3.00 downloaded a month ago. Found this out the hard way last December and confirmed it to be the case when I had an issue with mine... Turns out even though the version number had not been updated, they released an updated version that resolved my issue at the time... I simply had to redownload and "update"... Even though I was technically already running 3.00 Not sure why the wouldn't follow the convention of minor update revision numbering... 3.01, 3.02 etc

Trust me it's not that, it's always been like this, on every server and every version, if an app gets hundreds of screens the screens page will take forever to load up, which means it could take hours to finish an app when you constantly have to reload the page. :-( Cheers, David https://btmods.com/chat

MrDavid, I still have a feeling the problem is with your SQL database(s). Your Buzztouch Chat (https://btmods.com/chat) is pretty slow and the only reason this would take so long to process is beacause of the number of queries to your database is so high. You might want to check your database integrity, and optimize, repair and check your table in a program like phpMyAdmin.

I notice a delay as well with huge apps, but only a few seconds at most for me.

Yeah MrDavid, something's not right. Remember that universal studios app I just finished? That had over 1,000 screens, and it never took over 3 seconds to load the screens page.

Hmm, maybe it IS my server, crazy, but if your app loads up fast then what-the-heck! I'll do some quick testing.. thanks for the responses guys. David https://btmods.com/chat

Easy thing to check if the Server is the problem. Use Chris1’s App Copy tool to clone the app onto Angry's awesome server. Zip the Files and send that to Angry (unless the Copy tool already takes care of Files). What say ye Lords of Buzztouch? -- Niraj

Fine with me. Shoot me over the files if you like

I suspect your SQL Database could use some tuning… Remember some of those tables get annoyingly HUGE as well… especially the data logging records… Gotta account for some of it… Cheers! -- Smug

Funny, I was actually waiting for the screen page to load and thought, I'll check the forum while I'm waiting. I'm with David here. The screen page takes FOREVER to load and it's only the screen page. Sometimes I have two screens running to go back and forth between the loads. It's frustrating.

Perhaps Becky, a dose of Nirvana can help to move it along! :-)

The state of Nirvana would work wonders. How you get there, I have no clue!

Say, the other thing to consider is a more efficient way to create the JSON for your app. Entering that many screens isn't the most efficient way to add that much content, with or without the lag you are experiencing. A good option is the JSON snippet keeper. Another option is putting the content on a dynamic dataURL.

@Susan gotcha, but these apps are for clients that know nothing about this sort and don't do development, they just login to the control panel and add screens as needed. What SHOULD be done is make the screens.php file more efficient so when there are hundreds of screens it doesn't attempt to load them all up at once, perhaps only 25 per page, I suspect it's actually trying to load them all per refresh. I cleared my database @Smug and that didn't help, I counted up to 37 seconds for the page to load. Id rather not send the files since these are my "clients" apps. Super frustrating as I spent a good amount of time looking at screens.php with no luck, I just don't know PHP well enough. David https://btmods.com/chat

Any fix on this? I have the same issue.

To diagnose, copy your app onto another hosting provider. It's a pain, yet worth a try. Especially if Susan and Angry Ninja had reported success with many screens. -- Niraj

@MrDavid. We have had this conversation. You and I. A few times. Please believe me and the others. THIS CAUSED BY THE WAY YIUR SQL SERVER IS TUNED. "It cannot be my server or web host" because I have "fast pages" demonstrates your lack of understanding. I get it, it's frustrating for sure. However, queries on a large dataset that are untuned (for that machine) do take awhile. No two ways around it. Being direct and literal here so you can move ahead with a fix instead of moving ahead wondering and debugging. The that baby, haul ass, click away like a boss!

Ok so I need to tune my SQL like a boss? David good to see you chime in! So lets see I should mess around with MYSQL in my sever?

ill try this http://support.hostgator.com/articles/specialized-help/technical/how-to-optimize-a-mysql-database-using-phpmyadmin

Ok so I went to phpmyadmin and checked all databases for the control panel then I clicked the option optimize all databases. All pages in my control panel are loading faster but when i click on screens it tends to still take about 30 seconds. Has the screens.php file been changed in a while? Should I copy and paste a new screens.php from the self hosted package?

One additional step usually helps on database performance: make new indexes. Bummer that I've not taken the time to learn the BuzzTouch database, otherwise I might be able to tell you what to newly index. ------------ Ask Angry Ninja about his database setup and also ask him his hosting provider. Perhaps as a quick test, you can try your self-host on his hosting provider.

Typically, the BT_items table is the one you'll want to focus on. That holds all of your screens, which is usually the larger part of a BT configuration. But Niraj, 'how' would you index them? Cheers! -- Smug

You probably already know Smug, for the others ... phpMyAdmin allows one to establish additional indexes on a table. Any more details would have to come from David Book. In the past, I have heard him mention having to create indexes on the Rackspace servers for BuzzTouch.com when the Control Panel was responding slowly. -- Niraj

Trying to follow along here. Some insights... NOTE: All of the commands / tweaks / adjustments talked about here can be done on the command line (SSL to your mySQL server) or by way of phpMyAdmin. All phpMyAdmin does is put a UI layer in front of you so you don't have to know all the SQL :-) a) Limiting the number of "records per page" has zero to do with this. Think about it this way. The delay is the amount of time it takes to "create" the page, not return it (send the response) and this is a php / mySQL thing. Apache's ability to return the page to the requested browser is not affected here. Limiting the number of records per page would not change anything. It could, slightly. It's only going to change it to the extent that the actual HTML returned to the browser gets smaller / lighter. Returning 50 rows (and all that HTML) is not much different than returning 10 rows (and all that HTML). In the old days of super slow internet connections that could have helped in a noticeable way, not so much today. b) "Optimizing Tables" using phpMyAdmin doesn't do anything to help this issue. It's not bad to do it from time to time but it's an entirely different idea. This is an instruction to mySQL to remove, clean, re-organize some data that's non-persisted. Think "artifacts left behind after zillions of queries." Cleaning this out with "Optimize Table" or "Repair Table" (similiar command but different) is good won't change this issue. c) This slow-down is absolutely, 100% about INDEXING the proper columns on the proper tables in the proper order. This is non-trivial. It's easy (super easy) to issue the "create index" command (especially with phpMyAdmin) but knowing what to index can get tricky. Also, creating an index can take sometimes (many minutes / hours on a large datasets) and DOES create additional storage requirements on your server. Can your server handle it? Are you on a cheapo-host and close to your accounts limitations already? Think storage space, mySQL gets exponentially larger with boatloads of unecessary indexes. So, "Create index" is easy, how, where, and what happens is complex. Additionally, an OVERPOWERED server can easily mask an improperly tuned / indexed dataset, for awhile. Eventually all but the most insanely powerful machines will slow down. So where do we start with this stuff? Start with the ORDER BY statement in the query that's bogging everythnig down. It's string creating dynamically in screens.php. It starts on about line 361 and ends around line 369. You can see the variable name I used: $strSql Just so we're clear; The first line of this query contains the word SELECT I.guid. The last line of this query contains the word LIMIT. This is a relatively simple query and selects data from ONE TABLE but does a JOIN to reference another table. tbl_items holds all the "screens" for an app. tbl_plugins holds information about each plugin. Because the screens.php page displays the type of plugin for each screen in an app, we need to JOIN that table in order to include some info from tbl_plugins in the result set. To see the literal query that runs you can use the echo command in .php. Paste echo $strSql; exit(); under the query, say on line 370 or so. Reload the page in your browser. You'll see the actual query (not a varible, but the query itself) printed to the browser and nothing else (we exited after echo). This could be useful if you're running the query over and over again in phpMyAdmin or whatever. Copy and paste that baby somewhere while you work this out. Start by removing the ORDER BY clause. In php you can comment the last line where $strSql gets the ORDER BY clause appended. In the command window (or in phpMyAdmin) just remove everything after ORDER BY including the words ORDER BY. THIS WILL SPEED IT UP IMMENSLY. That one simple thing (not ordering the results) will speed it up about 100 times on most servers. But, this instroduces new problems, how will you order the list in the control panel? There are some other issues it will create too and my keyboard doesn't have enough ink to explain them all. You'll want to order the list, trust me on this. So, no ORDER BY makes it fast but I need an order by? Hmm...this is where it gets tricky and the appropriate "fix" will depend on a few things I can't know about your machine. Generally speaking, it's about creating indexes on the right columns in the right tables (see above). The reason that the fix isn't the same is complicated. I'm repeating here. If you're wondering why "the columsn aren't indexes properly already", as in why I didn't just create indexes in the first place, it's also complicated. LOL. I could have, but didn't intentionally. Anyway, I'm sure there is a better way to do this (than the default way I have it setup) that would cover / fix a very large number of servers but I'm not yet convinced that the fix (the one you come up with on your machine) should get included in the download. Again, for lots of reasons that are beyond the scope of this discussion to try to understand. Bottom line, the tradeoffs are not helpful for 99% of our users. Blah blah blah. It's my opinion that the ORDER BY logic should be implemented permanently (by default) in a very different way. Creating INDEXES is only one way to solve a slow server condition / slow running query. It's the low hanging fruit and lots of articles online assume you have a poorly tuned database because you're indexes are wrong. I couldn't diagree more. It's only one approach, there are always many. I'm not a fan of assuming INDEXES are the best approach simply because somebody says: "Slow queries, oh, you need more indexes" I'm giggling a bit. Anyway, start with indexes if your machine can "afford" them. If it can't we can start the next conversation about some other ways to tackle this problem. There are LOTS! If you need additional help with this I MAY be able to log into your machine and start tinkering. It won't be as soon as you email me, the pile is large and it's hard to burn hours working on a far away server. If you really want me to, send me an email with some candy or beer attached and I'll get you on the list :-) Cheers (I'm out of ink)

Hey im still trying over here. I commented out. then i deleted this line in bt_screens.php $strSql .= " ORDER BY " . $sortColumn . " " . $sortUpDown; The screens are still loading slow. Should I look into the indexing?

@Feraco: To be clear, you're sure the "ORDER BY" clause is not included in the SQL and the script is still slow...hmm..surprises me. You sent me connection info to your machine a number of weeks ago...I never found time to look into it... moved up the list...stay tuned (I don't believe you, LOL)....I'll get a look at your box in the next 24 hours... d.

@David I feel like im so close here. Thanks for chiming in. Im in myphpadmin im looking at the SQL for bt_items I see a list of columns. Should the orderIndex be in this list or should I delete it?

Hi again We are not on the same page here. Removing the order by clause something that needs to be done in the PHP script for the screens.php page. This is not something you do in the PHP my admin tool, it's an adjustment you make it to the screens to PHP script that I wrote. Shouldn't be too long before I get a look :-)