Copied install to new host, very slow to open first form

Questions related to installing, setting up and configuring nuBuilder Forte

Copied install to new host, very slow to open first form

Unread postby n9yty » Tue Feb 23, 2021 12:17 pm

I don't know how to really phrase this property... I had developed this for a non-profit back in October, but things got in the way and they never launched anything until now. It is a read-only repository of information from a legacy database system. Also I lost a lot of the short-term nuBuilder knowledge I had gained while building it. :)

It runs great on my Mac, the opening form has one button which takes them to a browse list where they can search for and pull up details on the entries.

When they click on the button it pauses for like 15 seconds, no CPU task is running on the host it just sits there, and then BANG it pops up the list. Navigating that list, searching, pulling up details all go very quickly. Just that initial opening is slow.

I see no errors in the apache error log, so I'm kind of at a loss as to where to look. Almost like some piece of config is still tied to the old host name or something and it is timing out?

Open to any thoughts/ideas.

UPDATE: I do see MySQL running in the background, but at <10% during this time. The initial browse form is opening with the DB contents, can I make it open blank or create some index to speed up the opening?
Last edited by n9yty on Tue Feb 23, 2021 1:12 pm, edited 1 time in total.
n9yty
 
Posts: 46
Joined: Fri Oct 16, 2020 4:13 am

Re: Copied install to new host, very slow to open first form

Unread postby n9yty » Tue Feb 23, 2021 1:04 pm

[offtopic, removed from this thread by OP]
Last edited by n9yty on Tue Feb 23, 2021 3:00 pm, edited 1 time in total.
n9yty
 
Posts: 46
Joined: Fri Oct 16, 2020 4:13 am

Re: Copied install to new host, very slow to open first form

Unread postby n9yty » Tue Feb 23, 2021 1:59 pm

As to the slow form load... It is a simple query:

SELECT id_code, title, first_name, last_name, suffix, city, state, zip, phone_number, name
FROM names
ORDER BY id_code ASC

I don't know how to speed that up, it has an index on id_code already, and an explain shows a select_type of SIMPLE, and NULL for keys/etc. Extra is "Using filesort". There are 195,946 rows though, so if there is a way to speed up this initial page load I would love to know how, obviously the VM is much slower than my computer because I hadn't seen that locally, it is under a second to give me the data.

Hmmmm... If I run this query from Sequel-Ace on my Mac, connected to the remote database over SSH, the query runs in a few seconds, first row in 132ms. So it doesn't seem like a MySQL (MariaDB) issue... Something with the PHP MySQL connection??

I also grabbed a sample PHP script to connect and run the query and it returns quickly (a few seconds), so I don't think that is a general problem, but yet when nuBuilder runs that form mysql runs at low percentage until the very end when the results are pushed through.
n9yty
 
Posts: 46
Joined: Fri Oct 16, 2020 4:13 am

Re: Copied install to new host, very slow to open first form

Unread postby kev1n » Tue Feb 23, 2021 4:10 pm

Hi,

Do the other forms (eg. Object Form) open at normal speed?

If you add a LIMIT 100, will it load faster?

Code: Select all
SELECT * FROM (
SELECT id_code, title, first_name, last_name, suffix, city, state, zip, phone_number, name
FROM names
ORDER BY id_code ASC
LIMIT 100
) T
If you like nuBuilder, please leave a review on SourceForge

nuBuilder Code Library (85)
Installation Guides
Updating nuBuilder

Join Discord to talk about nuBuilder!
kev1n
nuBuilder Team
 
Posts: 1357
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Copied install to new host, very slow to open first form

Unread postby n9yty » Tue Feb 23, 2021 4:39 pm

Using LIMIT 100 makes it instantly appear... But since I can run the query elsewhere rapidly without that, it seems some bottleneck in the data transfer rather than the query speed?
n9yty
 
Posts: 46
Joined: Fri Oct 16, 2020 4:13 am

Re: Copied install to new host, very slow to open first form

Unread postby kev1n » Tue Feb 23, 2021 4:45 pm

Is just the initial loading (when the form is first opened) slow or also when the form is open and you click on the search button without entering a search term?
If you like nuBuilder, please leave a review on SourceForge

nuBuilder Code Library (85)
Installation Guides
Updating nuBuilder

Join Discord to talk about nuBuilder!
kev1n
nuBuilder Team
 
Posts: 1357
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Copied install to new host, very slow to open first form

Unread postby n9yty » Tue Feb 23, 2021 4:51 pm

Now this is bizarre... I kept bumping up the limit and it wasn't really slowing down (just a little). I removed the LIMIT entirely and it was still very fast.

But you had me wrap the query in an outer query... If I remove that it goes back to being very slow, but if I put it back, it is almost instantaneous.

What does that say? :)

The search was quick even when the initial load was slow, but trying to move between pages after the initial load was as slow as the initial load itself. Having that outer query makes it pretty responsive all around.
n9yty
 
Posts: 46
Joined: Fri Oct 16, 2020 4:13 am

Re: Copied install to new host, very slow to open first form

Unread postby n9yty » Thu Feb 25, 2021 1:44 am

Just bumping to try to understand why wrapping the query in an outer select makes this not take the extra 20 seconds or so... Doesn't seem like a database issue, as I can't duplicate this when running queries directly. Why is this such a different performance experience in nuBuilder, and how/can this be used in other places to improve performance?
n9yty
 
Posts: 46
Joined: Fri Oct 16, 2020 4:13 am

Re: Copied install to new host, very slow to open first form

Unread postby apmuthu » Thu Feb 25, 2021 10:05 am

This kind of archive lookup is best done in AjaxCRUD and not in nuBuilder. The LIMIT can be in the base query itself like:

Code: Select all
SELECT id_code, title, first_name, last_name, suffix, city, state, zip, phone_number, name
FROM names
ORDER BY id_code ASC
LIMIT 0, 100;

Hopefully the id_code is the primary key and keeping it an UNSIGNED INT if an integer may be okay too.

Do an optimise / diagnose on the database and see if that fixes it.
Check if the engine is set to MyISAM (if no collations are needed).
apmuthu
 
Posts: 224
Joined: Sun Dec 06, 2020 3:20 pm
Location: Chennai, India, Singapore

Re: Copied install to new host, very slow to open first form

Unread postby n9yty » Tue Mar 02, 2021 1:50 pm

AjaxCRUD? Don't know anything about that... The outer wrapper, for whatever magic reason, made it completely responsive, so I have left it as that. It was a brand new database restore so I don't know that optimization/etc is an issue, but who can say. It is working as is for them now and is only legacy lookup, infrequent use, so it isn't worth a huge time investment now.

Edit: I followed your link, I see AjaxCRUD is a whole separate thing, I don't know that helps to build a browse/view in nuBuilder, but then again, as I say, unfortunately not worth the time investment for this project. Ironically this is the approach I was told to use when I first built this system last fall and was seeking advise here. :)

UPDATE: I also see this appears to be a shill post from an AjaxCRUD advocate. Hey, no harm no foul, glad it works for you, but this data is far more than the simple table I presented, there are MANY tables of interconnected data that nuBuilder allowed to be beautifully crafted into forms and subforms, all interlinked. The system is fabulous, and lookiing at the AjaxCRUD stuff I found online it could come nowhere close to giving what nuBuilder did, even for read-only views.
n9yty
 
Posts: 46
Joined: Fri Oct 16, 2020 4:13 am

Next

Return to Installation

Who is online

Users browsing this forum: No registered users and 1 guest

cron