Page 1 of 1

Page execution time

Posted: Fri Aug 12, 2005 2:47 pm
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>
<? } ?>

Posted: Fri Aug 12, 2005 2:59 pm
by feyd
tsk tsk for double posting!

Your secondary queries (in the loop) are likely the time sinks.

Posted: Fri Aug 12, 2005 3:02 pm
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?

Posted: Fri Aug 12, 2005 3:28 pm
by feyd
I have not tested that.

Posted: Fri Aug 12, 2005 3:46 pm
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?

Posted: Fri Aug 12, 2005 4:04 pm
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.

Posted: Fri Aug 12, 2005 4:43 pm
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.

Posted: Fri Aug 12, 2005 4:56 pm
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";

Posted: Fri Aug 12, 2005 5:07 pm
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.

Posted: Fri Aug 12, 2005 6:21 pm
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.

Posted: Fri Aug 12, 2005 11:15 pm
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

Posted: Mon Aug 15, 2005 7:35 am
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.