I'm thinking that the load time is coming from the SQL conversation as it's requesting the data from a different server, but none-the-less, on my test machine (local, work computer running Apache/MySQL) these page renders took less than a second...usually less than a tenth of a second.

http://www.sspl.org/history/research/
^ ...btw, this isn't yet live, even though it's the live server. This is the only page on the net that's currently linking to this location...the administration portion of it is basically the same file with a couple additions. (inline edits/deletes/adds)


Yeah, it's ugly...but it does its job.
The staff member in charge of this did NOT want pagination.

I'm going to try using the localhost MySQL server tomorrow and see if there's a change, but... Seriously, should it take this long for the load times? I am doing a SELECT * FROM and then removing what shouldn't be there (ID field and a couple other things) but to make it work with ANY database table, I couldn't think of a better, more modular way. Could that really take up that much more time?

-edit-
The estimated load time is listed at the bottom of the page in the source code, commented out via HTML.

"This page took "0.01920" seconds to render"... Seems pretty quick to me. Is the remote SQL server part of the same local network or is it remote (connecting via the internet)? If its remote the problem could be explained by simple traffic congestion..

The network is local, but the network is already known to be FUBAR...we have two separate local networks, both "owned" by two people. The specific network this is on, is "owned/maintained" by the same person maintaining the server.

Also, check the actual subpages from that page linked...the linked page is just getting the tables from the database and displaying anything that has been given a description; anything without a description is pending administrative approval.

The subpages are the slow ones. But thanks for looking at the first page.

Oh right .. Looking at it, and this is my opinion, for the amount of data being displayed and processed, I wouldn't really class that as a huge loading time.. Can you give us one of the SQL queries you are using?

Like I said, it's just a SELECT * query. I know they're not all that quick. However, like I also said, the staff member in charge of this did not want pagination. An example of the table data (Mc Clellan) is as follows:

However, the Scrapbook table is:

...as I said, I wanted to make sure that no matter how the table was created, it would work just fine. The next step is to figure out how to allow a VIEW or SELECT query work just the same way (for anything that can be rationalized out further, these could not due to personal attachment of the said staff member to the actual data). Historians are so silly.

I know there's a lot of text, is there any way I can speed this up, or would you have to see my code (which I'm sure you don't have time to look over)? I figured text wouldn't take that long to display, even if it is a query.

I just moved it all over to the localhost machine's database. It's actually slower without going over the network. The webserver is about 12 years old... I just wish I knew why it would run so blazingly fast on my test machine, but so uber slow on the production server, which although has a higher load overall, really shouldn't slow it down up to 4-5 seconds more.

Test Server:
This page took "0.36881" seconds to render.

Production Server (after cached):
This page took "5.33447" seconds to render.

It's really hard to try to figure this out from the outside....
But a few pointers:

- MySQL query caching enabled?
- MySQL query load? (check the stats and see how busy your mysql server is)
- DNS configuration correct? (try 127.0.0.1 instead of localhost etc.)
- Hard drive problem? (compare 'hdparm -tT' on both machines if they're linux)

etc.

With that little data, I'd really expect this server to pump out pages pretty fast.... Even ~0.4secs seems slow...

How portable is your setup (code + data)?
Do you want to test it on a different server for kicks?

PS: With this kind of page setup, it should be rather trivial to code up a static cache.

I'm not very knowledgable about caching itself, I just know that MySQL's queries are internally cached somehow, as a reload of the same page/queries will actually take less time if in succession. That is what I meant, so I apologize if I sounded a bit more inclined than I am.

I would be willing to share the code/data for others to see/test-out and tear apart. It was hacked together and it is procedural, so be warned. Oddly enough, my session information isn't being propogated as expected from the test machine to the live server either... It's causing odd problems. I also need to rework the login for the administration...I could technically just remove a security check and still be perfectly fine as there are no other systems integrated with the access system yet.

Using MySQL Administrator:
MAX Usage: 3%
MAX Traffic: 77,276 bytes (Ave: 3,130 bytes)
MAX Queries: 5
MAX Hitrate: 100% (Ave: 2%)
Key Buffer Usage: 1,088,512s ...

My Hitrate jumps to 100% twice on one page load...that might be a problem; though not necessarily the main one (though I don't think I'm using two select queries...).

I'm not entirely sure where else to check for SQL status as I'm not much of a power shell user (at all).

My test machine is Windows XP setup for dev-work, not for server workloads. I do have a hosted server that I can try, however...and probably will just to see.

The following is on the live server:

To see if the mysql cache is working, you can use phpmyadmin. Log in, click on "Show MySQL system variables" and look for "query cache".

I'd be willing to test it for you on my server.
If the data isn't sensitive to your client, do a mysqldump and zip up the directory.

My email is andre at ironcreek dot net.


That's pretty darn slow.

On my server (regular IDE hard drive) it looks more like this:


Post the result of "hdparm -i /dev/hda"

The exact phrase "query cache" was not found. Query and Cache respectively had hits though, which I'm sure is to be expected.

I've uploaded a zipped RAR'd group of stuff. Apparently I can't upload RARs. I use a hacked together templating scheme which would throw a whole slew of errors if it wasn't included in the process...the necessary functions are also included in the protected folder (which must be below web_root...or, at least in my example which you could easily change).

I have no qualms about the data, there's nothing there that can't be added. User creation is not currently added to the administration so, you can use my username/pass combo:

user: bkozlowski
pass: password

If an error occurs, use the back button and revisit the page by clicking on the link. This is (I think) a separate issue that I must work out with session variables. I'll email you a copy as well.


Results of "hdparm -i /dev/hda"

Alright, it's installed here:
http://ironcreek.net/test/srdb

Looks like the McClellan page (seems to be the largest) takes about 0.7 seconds on my server. I still think that's pretty long. But at least nowhere near your production server.

It looks like your production server's HD isn't running UDMA. Is it old hardware?

I'll look at the source now and see if I can make any recommendations.

It is in fact old hardware. There's been a high-profile Mac OS X server collecting dust for about 2 years that the new boss has finally said "get it working now"...of course "now" means whenever it's feasible since no one else has access to it other than the person that bought it... I know how to setup a server in X Windows (or debian) on Linux, but not Unix or Mac OS X. My boss can do it on Windows Servers and nothing else...

We have to deal with this box for the time being. It's an old Cobalt RAQ.

On your server I got the following results:

There's obviously something wrong with my code. I just wished my local development server would have given me the same problem.

P.S. - Sorry for discluding the CSS, though you could get it from the live server easily. There's only one CSS file for the entire site.

Try it again on my server.
I got the McClellan page down to consistently be around 0.25secs, which I think is pretty good all things considered.

I've sent a PM with my code suggestions.

Cheers,
Andre

Attempt #1:

Attempt #2 (after some refreshes of the same page):

I wonder why it takes longer from my browser/PC than it does from yours. Either way, it's still an improvement and I'll definately be taking a look at your code suggestions. Thank you very, very much! Hopefully I'll learn something too.

-edit-
I've read over some of your ideas and feel a bit embarrassed. I don't have the luxery of having anyone else to look over my code and say, "what in the world were you thinking?!". I do thank you for pointing out some obvious and dubiously poor choices on my part. I was changing semi-colons to HTML equivalents due to SQL injection. I tried thinking of any and all possibilities that could cause a problem and tried to address it.

I will take a much closer look at all this tomorrow when I get back at work. I unfortunately don't have remote access to the server or would do it now. As such, it seems to have sped up my own local install of the files I uploaded (and installed on my home laptop) by about half (approximately the same results you're getting).

BTW, got a paypal account?

Good. Let us know tomorrow how it goes.


I do! But I also am older than most people on this board (not counting Matt ) and earn a six figure salary as a programmer . So thanks for the offer but I'm taken care of.

I just like to help (well, help those who can ask for help; not the "Something broke! Fix it!" crowd ) and enjoy a good "puzzle" to break up my day a little .

Glad you offered though - shows you got a good head on your shoulders!

Cheers,
Andre

Results after audit:
First load:
Second Load:

...well I did see a 3.7 somewhere in there as well. However, it's still better than 5+ seconds. I'm going to reconfigure my development server to use PHP5 and install George Schlossnagle's PECL extension APD to see where some more slowdown could be caused.

Thanks again for your help, if you're interested I'll update this with any more findings (if any).