Should templates be text based?

When I began development of the site I'm currently working on, I decided to store templates in mysql to make things a little easier to manage. But just a few days ago I decided to see what sort of performance change there would be if I moved the templates over to text files. Boy was I suprised. Page execution changed from about .12 to .16 down to .06, a complete 2/3's drop on some pages!

I figure vB already taxes mysql a great deal, why not alleviate some of that by transfering the templates over to text files. Anyone care to try this? I've written a script that'll transfer all the templates into text files and a quick change to the gettemplate function. Remember to have a backup of global.php, and to create a directory to write the templates too.

The script to create the text files:
--------------------------------------------------------------------------------------
<?php

require ("global.php");

$templates=$DB_site->query("SELECT title,template FROM template");
$dirtowrite = "/path/to/templates/dir";

while ($template=$DB_site->fetch_array($templates)) {
$file = fopen ("$dirtowrite/$template[title].txt", "w");
fwrite($file,$template[template]);
fclose($file);

print "$template[title]: created<br>\n";
}

?>
--------------------------------------------------------------------------------------

Now in global.php find:

function gettemplate(...

And replace the entire function with this one:

function gettemplate($templatename,$escape=1) {
// grab a template

$temp_file = "/dir/to/templates/$templatename.txt";
$fd = fopen ($temp_file, "r");
$template = fread ($fd, filesize ($temp_file));
fclose($fd);

return $template;
}

I'm anxious to hear if there are any noticable performance changes on high-traffic sites.

I take it you didn't try to read 20+ files per page, on every page.

vB 1.1.4 alleviates a lot of the queries for templates by getting every template needed in one query, and put into a cache.

Also,

function gettemplate($templatename,$escape=1) { 
// grab a template 

$temp_file = "/dir/to/templates/$templatename.txt"; 
$fd = fopen ($temp_file, "r"); 
$template = fread ($fd, filesize ($temp_file)); 
fclose($fd); 

return $template; 
}

Try viewing a thread with 30 posts. You're going to read 30 postbit.txt's alone.

Never the less, I'm going to try it anyway, I'll be back with findings in about 30 minutes.

hey Krucifyx

well i believe freddie/rangersfan did a similar hack with templates i think ? you'd have to ask freddie though

Results were inconclusive:

Using flat file templates:
----------------------------------------------------------------------
                                /vb114/index.php		1.7282350063324
                                /vb114/index.php		INVALID TIME
                                /vb114/index.php		1.5354700088501
                                /vb114/index.php		0.5702189207077
                                /vb114/index.php		0.53852188587189
                                /vb114/index.php		1.5131859779358
                                /vb114/index.php		0.56164300441742
                                /vb114/index.php		0.64329504966736
                                /vb114/index.php		0.51034200191498
                                /vb114/index.php		0.53449499607086
               /vb114/forumdisplay.php?forumid=1		1.563912987709
               /vb114/forumdisplay.php?forumid=1		INVALID TIME
               /vb114/forumdisplay.php?forumid=1		1.4870719909668
               /vb114/forumdisplay.php?forumid=1		INVALID TIME
               /vb114/forumdisplay.php?forumid=1		1.4797700643539
               /vb114/forumdisplay.php?forumid=1		0.42898499965668
               /vb114/forumdisplay.php?forumid=1		0.49602794647217
               /vb114/forumdisplay.php?forumid=1		0.51602602005005
               /vb114/forumdisplay.php?forumid=1		0.52056705951691
/vb114/forumdisplay.php?forumid=1&daysprune=1000		0.55617499351501
/vb114/forumdisplay.php?forumid=1&daysprune=1000		INVALID TIME
/vb114/forumdisplay.php?forumid=1&daysprune=1000		1.5123800039291
/vb114/forumdisplay.php?forumid=1&daysprune=1000		INVALID TIME
/vb114/forumdisplay.php?forumid=1&daysprune=1000		1.5282100439072
                /vb114/showthread.php?threadid=4		0.97411799430847
                /vb114/showthread.php?threadid=4		0.73896205425262
                /vb114/showthread.php?threadid=4		0.65621793270111
                /vb114/showthread.php?threadid=4		INVALID TIME
                /vb114/showthread.php?threadid=4		INVALID TIME
                /vb114/showthread.php?threadid=4		1.6474299430847
                /vb114/showthread.php?threadid=4		0.66209602355957
                /vb114/showthread.php?threadid=4		0.27366995811462
                /vb114/showthread.php?threadid=4		0.69844806194305
                /vb114/showthread.php?threadid=4		0.7712379693985
                /vb114/showthread.php?threadid=4		0.87718105316162
----------------------------------------------------------------------

Using MySQL templates:
----------------------------------------------------------------------
                                /vb114/index.php		0.56958401203156
                                /vb114/index.php		INVALID TIME
                                /vb114/index.php		0.52925097942352
                                /vb114/index.php		0.56821501255035
                                /vb114/index.php		0.56365704536438
                                /vb114/index.php		0.61730802059174
                                /vb114/index.php		1.5742579698563
                                /vb114/index.php		0.52257800102234
                                /vb114/index.php		INVALID TIME
                                /vb114/index.php		1.6354759931564
                                /vb114/index.php		0.60910892486572
               /vb114/forumdisplay.php?forumid=1		INVALID TIME
               /vb114/forumdisplay.php?forumid=1		INVALID TIME
               /vb114/forumdisplay.php?forumid=1		1.4293439388275
               /vb114/forumdisplay.php?forumid=1		INVALID TIME
               /vb114/forumdisplay.php?forumid=1		0.48564791679382
               /vb114/forumdisplay.php?forumid=1		0.43799495697021
               /vb114/forumdisplay.php?forumid=1		0.52786505222321
               /vb114/forumdisplay.php?forumid=1		1.4195520877838
               /vb114/forumdisplay.php?forumid=1		INVALID TIME
/vb114/forumdisplay.php?forumid=1&daysprune=1000		INVALID TIME
/vb114/forumdisplay.php?forumid=1&daysprune=1000		0.61003601551056
/vb114/forumdisplay.php?forumid=1&daysprune=1000		INVALID TIME
/vb114/forumdisplay.php?forumid=1&daysprune=1000		0.5251590013504
/vb114/forumdisplay.php?forumid=1&daysprune=1000		0.55706202983856
/vb114/forumdisplay.php?forumid=1&daysprune=1000		0.60305690765381
                /vb114/showthread.php?threadid=4		INVALID TIME
                /vb114/showthread.php?threadid=4		0.68921291828156
                /vb114/showthread.php?threadid=4		0.68019592761993
                /vb114/showthread.php?threadid=4		1.6432230472565
                /vb114/showthread.php?threadid=4		INVALID TIME
                /vb114/showthread.php?threadid=4		0.63571894168854
                /vb114/showthread.php?threadid=4		0.71191096305847
                /vb114/showthread.php?threadid=4		0.63852798938751
----------------------------------------------------------------------

Here's my time testing code, inserted under set_magic_quotes_runtime(0); in global.php:

function timeinsert() {
	global $starttime,$PATH_INFO,$QUERY_STRING;
	
	$timeparts = explode(" ",microtime());
  	$endtime = $timeparts[1].substr($timeparts[0],1);
  	
	$timedif = $endtime - $starttime;
	if ($timedif<0) {
		$timedif = "INVALID TIME";
	}
	if ($QUERY_STRING) {
		$blah = "$PATH_INFO?$QUERY_STRING";
		$write = sprintf("%60s", $blah);
	} else {
		$write = sprintf("%60s", $PATH_INFO);
	}
		$write .= "\t\t$timedif\n";
	$file = fopen("C:/Inetpub/wwwroot/vb114/timingtest.txt", "a");
	fwrite($file, "$write");
	fclose($file);
}
register_shutdown_function("timeinsert");

$timeparts = explode(" ",microtime());
$starttime = $timeparts[1].substr($timeparts[0],1);

(edit so it works in your directory)

Here's the updated gettemplate function for flat files:

function gettemplate($templatename,$escape=1) { 
	global $templatecache;

	if ($templatecache[$templatename]!="") {
		$template = $templatecache[$templatename];
	} else {
		$temp_file = "C:/Inetpub/wwwroot/vb114/templates/$templatename.txt"; 
		$fd = fopen ($temp_file, "r"); 
		$template = fread ($fd, filesize ($temp_file)); 
		fclose($fd); 
		$templatecache[$templatename] = $template;
	}
	if ($escape==1) {
    $template=str_replace("\"","\\\"",$template);
  	}

	return $template; 
}

"Tests" done on a Celeron500, 64MB (ick, soon to be 198), Win98SE, PWS.

I have done this before but I didn't run any time tests. I also cached the .txt files so there were no differences between the two.

My interest was would the less load on MySQL (no longer loading templates) vs the increased load on the webserver (now loading files) benefit vB. I also didn't come to any usable conclusions as I had no way to put it to the test on a large site.

[Edited by freddie on 12-01-2000 at 09:39 PM]

Just a tip for 1.14 users on high-traffic sites...

Add an index on the "title" field in the "template" table. Its CRITICAL.