Page execution time

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Page execution time

Post by s.dot »

I inserted some microtime()s in my forums, and I figured out where the main loading problem is.

As I figured, it's in my main loop. Each iteration through the loop is taking from .8 - 1.1 seconds :|

There are in total, 25 iterations of the loop per page load, and current page load time ranges from 18 - 25 seconds.

Perhaps someone could tell me where the time consuming part is.

This part generates the query:

Code: Select all

$sql_text = ("SELECT id, topicname, threadstatus, author FROM forumtopicmain WHERE forumid = '$forumid' AND type != 'sticky' ORDER BY lastreply DESC");
if(!$page)
{
	$page = 1;
}
$prev_page = $page - 1;
$next_page = $page + 1;
$page_start = (25 * $page) - 25;
$query = mysql_query($sql_text);
$num_rows = mysql_num_rows($query);
if($num_rows <= 25)
{
	$num_pages = 1;
} else if
(($num_rows % 25) == 0)
{
	$num_pages = ($num_rows / 25);
} else
{
	$num_pages = ($num_rows / 25) + 1;
}
$num_pages = (int) $num_pages;
if(($page > $num_pages) || ($page < 0))
{
	die("You have specified an invalid page number");
}
$sql_text = $sql_text . " LIMIT $page_start, 25"; 
$query = mysql_query($sql_text);

// final result query looks like
// SELECT id, topicname, threadstatus, author FROM forumtopicmain WHERE forumid = '$forumid' AND type != 'sticky' ORDER BY lastreply DESC LIMIT 0, 25
?>
Then here is my loop

Code: Select all

while($array = mysql_fetch_assoc($query))
{
	$replies = mysql_num_rows(mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'")) - 1;
	$lastpostarray = mysql_fetch_assoc(mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."' ORDER BY time2 DESC LIMIT 1"));

	// Show the number of pages for each individual topic
	$query2 = mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'");
	$numberofpages = (int)(((mysql_num_rows($query2) - 1)/25) + 2); ?>
	<tr>
		<td bgcolor="#E9EBFC" class="main" width="5%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;">
		<? if($array['threadstatus'] == "open"){ echo "<img src=\"images/thread_open.gif\" alt=\"Thread Open\">"; } ELSE { echo "<img src=\"images/thread_locked.gif\" alt=\"Thread Locked\">"; } ?>
		</td>
		<td bgcolor="#E9EBFC" class="main" width="45%" align="left" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><a href="showthread.php?threadid=<? echo $array['id']; ?>&page=1"><? echo stripslashes($array['topicname']); ?></a>
		<? if($numberofpages > 2)
		{
			echo "<BR><font size=1>Page: </font>";
			for ($j=1;$j<$numberofpages;$j++)
			{
				echo "<a href=\"showthread.php?threadid=".$array['id']."&page=$j\">$j</a> ";
			}
		} ?>
		</td>
		<td bgcolor="#E9EBFC" class="main" width="10%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><B><? echo $replies; ?></B>
		</td>
		<td bgcolor="#E9EBFC" class="main" width="15%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><a href="showme.php?u=<? echo $array['author']; ?>"><? echo $array['author']; ?></a>
		</td>
		<td bgcolor="#E9EBFC" class="main" width="25%" align="left" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000; border-right: solid 1px #000000;"><? echo $lastpostarray['time']; ?><BR>by <a href="showme.php?u=<? echo $lastpostarray['author']; ?>"><? echo $lastpostarray['author']; ?></a>
		</td>
	</tr>
<? } ?>
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

tsk tsk for double posting!

Your secondary queries (in the loop) are likely the time sinks.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Sorry about the double post. The posting.php page was slow to load, so I clicked submit twice.

Is it faster to echo all the HTML... or escape the PHP to write HTML then go back to PHP to close the loop?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I have not tested that.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Hmm doing some quick testing using microtime() and a loop that iterates 1000 times.

It appears echo "text"; is considerably faster than echo 'text';

and it appears to be better to echo out the HTML if the html code contains multiple calls to echo(); This makes sense. Less functions to do.. less time.

would using ob_flush() and flush() make my code faster by freeing up ram?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the ram usage is mostly up to php and your web server to discuss. flush() merely asks the server to send the data, but will increase page time, last I checked.. albeit very little.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

What's the difference between:

Code: Select all

$string = "some text $var here";

// and

$string = "sometext ".$var." here";
The second example appears to be faster than the first.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

I'm guessing the second one calculates the value of $var first before the string. As if you were running a function in a string like:

Code: Select all

$var2 = "Scrotaye\'s"
$str = "I have a ".stripslashes($var2)."  string to print";
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I see.

I eliminated 2 queries in each iteration of the loop, did all my functions before my first echo, then only used one echo per loop.... cut my page time down from 18-22 seconds to 5-7 seconds :-D

Still bad.. but much... much... better.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Well, database queries are a natural bottleneck for applications. Consider optimizing your database so that you can grab all the info you need in one query.

About string interpolation "This $a" versus concatenation "This ".$a, if you're using an older version of PHP4, there was a major difference between the two, but later versions improved the interpolator and made it a lot faster, so that the difference was negligble. Same with the echoes. It's your query.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I stored the replies for each topic, and the last post & last post time in the database, so I could do one query. I really did not want to do it this way, as it requires more queries when you post a topic, delete topic, etc. But it made the load time go down from 22 seconds, to .5 seconds... so it was totally worth it :-D
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

scrotaye wrote: I stored the replies for each topic, and the last post & last post time in the database, so I could do one query. I really did not want to do it this way, as it requires more queries when you post a topic, delete topic, etc. But it made the load time go down from 22 seconds, to .5 seconds... so it was totally worth it :-D
Good job!

In the future, you may also want to consider caching. As long as no new posts have been added to the forum, just use the cached content. When someone does post, delete the old cached content and rebuild it with good info.

Another interesting way of doing this would be to cache just the id's for the items you are concerned about above and store them in a tree then serialize that tree to a file. It would work the same way as above, the only difference being that you are dealing with a tree that needs to be iterated over to build your content.

Not sure what would be faster, but testing helps.
Post Reply