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

[n9yty]

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?

[n9yty]

[offtopic, removed from this thread by OP]

[n9yty]

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.

[kev1n]

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 

[n9yty]

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?

[kev1n]

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?

[n9yty]

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]

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?

[apmuthu]

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).

[n9yty]

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.