Improving speed

I have a question about improving page rendering speed.

For instance, according to mysql the following query takes a mere 0.28
secs:

SELECT * FROM table1
ORDER BY field1;

924 rows in set...

however rendering the page displaying all that data can take over a
minute which, without modifications to the php.ini file, can
occasionally cause those timeout errors

I know it's difficult to say too much without providing the script and
hardware info but I was just wondering if anyone had any general tips
about improving the speed at which the page displays.

Cheers

Re: Improving speed

strawberry wrote:

> I know it's difficult to say too much without providing the script and
> hardware info but I was just wondering if anyone had any general tips
> about improving the speed at which the page displays.

Don't use echo/print for all HTML output, if you have large amount of HTML
then do it outside the PHP.

Run web-server and sql-server on the same machine.

Connect to the sql-server in the beginning of the script, keep the connection
alive until you finished all SQL queries.

Avoid fetching a lot data from the sql-server, do as much "sorting" in your
SQL queries and limit the number of rows fetched to the amount you need.

Don't use banners from a lot of external sites.

Don't connect form a host that has a bad ping-time to the web-server.



//Aho

Re: Improving speed

Thanks for the input. From what you say, it seems like I'm doing things
OK. I tried removing all the 'echoes' but couldn't detect any
difference to performance - no better, no worse.

Unfortunately, I'm coming to the conclusion that my machine is simply
rather slow. :-(

I've just tried accessing the same page on my colleague's shiny new PC
and it manages to render the page within a few seconds.

One thing though; you're post implies that you actively close the
connection - something that my script doesn't do (I thought it closed
automatically upon execution of the script).

Does is make a difference if I close it or not?

Re: Improving speed

*your post (excuse my grammar)

Re: Improving speed

strawberry wrote:
> Thanks for the input. From what you say, it seems like I'm doing things
> OK. I tried removing all the 'echoes' but couldn't detect any
> difference to performance - no better, no worse.

On a somewhat normal machine you won't detect the difference, it's somewhere
near 0.03s for a 1000 line of HTML, if you go and use an i286 then you may
notice it a lot more.


> Unfortunately, I'm coming to the conclusion that my machine is simply
> rather slow. :-(

What spec does you machine have?
Do you run the sql server on the same machine?
Do you use a load of joins in your sql-statements?
Do you have network related troubles on your machine if you use say links,
ncftp, micq on it?


> One thing though; you're post implies that you actively close the
> connection - something that my script doesn't do (I thought it closed
> automatically upon execution of the script).
> Does is make a difference if I close it or not?

If you use mysql_connect() and you don't close the connection, the connection
will be automatically ended when the script ends, but it's good habit to do it
yourself, this lessens the risk that you ignore to close filestreams.


//Aho

Re: Improving speed

strawberry wrote:
> SELECT * FROM table1
> ORDER BY field1;
>
> 924 rows in set...
>
> however rendering the page displaying all that data can take over a
> minute which, without modifications to the php.ini file, can
> occasionally cause those timeout errors

Hmm.. is it sensible to display this much information on one page? Could
you implement pagination with the LIMIT clause?

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org

Re: Improving speed

Hello,

on 03/27/2006 09:30 AM strawberry said the following:
> I have a question about improving page rendering speed.
>
> For instance, according to mysql the following query takes a mere 0.28
> secs:
>
> SELECT * FROM table1
> ORDER BY field1;
>
> 924 rows in set...
>
> however rendering the page displaying all that data can take over a
> minute which, without modifications to the php.ini file, can
> occasionally cause those timeout errors
>
> I know it's difficult to say too much without providing the script and
> hardware info but I was just wondering if anyone had any general tips
> about improving the speed at which the page displays.

The best tip to optimize what you want to do is to use content caching,
ie. in the first access generate the page and store it in a cache file,
for instance, and serve the page from the cache in the following accesses.

Here is a more detailed FAQ on database performance tricks and tips:

http://www.meta-language.net/metabase-faq.html#performance

--

Regards,
Manuel Lemos

Metastorage - Data object relational mapping layer generator
http://www.metastorage.net/

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/

Re: Improving speed

"strawberry" wrote in message
news:1143462615.500455.201670@t31g2000cwb.googlegroups.com.. .
>I have a question about improving page rendering speed.
>
> For instance, according to mysql the following query takes a mere 0.28
> secs:
>
> SELECT * FROM table1
> ORDER BY field1;
>
> 924 rows in set...

Specify the columns and avoid the use of *.
Use LIMIT and use the paging for the results.
Cache the results when the data is updated
(except if you update the records in the db each minute)
and send the cached results to the client.

Regards.


--
Leonardo Armando Iarrusso - J2Be
www: http://www.J2be.com - e-mail: info[at]J2Be.com

Re: Improving speed

Spec as follows:

Intel Pentium 4 CPU 1700 MHz
1.69 GHz, 256MB RAM

I'm using xampp on WinXP Pro 2002 SP2

While the project's in development, I'm running it on my own machine.
This allows me to test things like the automatic creation of files and
folders without buggering up the file server. If/when we're happy that
the project provides a real benefit to our work then the plan is to
transfer everything across to the same machine as our dedicated file
server, and have it accessible from anywhere.

Some of the queries do use as many as four JOIN statements but the
example I cited originally really is pretty much as straightforward as
I described.

I'm not aware of any network-related troubles. I think everything works
in that regard.

With regards to Nicholas's post, I'm not sure that I give much thought
to the sensibility or otherwise of these sorts of things before I get
into them. I don't know if it's 'sensible' but it sure is nice to have
all that info neatly presented on a single page. :-).

If I could think of a satisfactory way to implement it then pagination
might be the answer - but I think I'll avoid it until the result set
gets a bit bigger. After all, although it takes a few seconds to
render, even a thousand entries doesn't really take that long to scroll
through.

With regards to J2Be's post, I'm perfectly happy to specify the columns
if it demonstrably speeds things up but the only problem (as I see it)
with this approach is just that if I add a field at a later date I have
to remember to modify the SELECT statement to reflect this change.

A couple of people have talked about caching. Thanks, I'll look into
that - and thanks everyone for all your input.

Cheers.