Archive | March 2009

Help me : SQLite versus mySQL in PHP

Edited: Thanks to “no one” for comment on transaction mode INSERTION, with BEGIN IMMEDIATE-COMMIT pair that wrap INSERT QUERY. I got double perfomance than MySQL with this (1000 insertion, Mysql took 0.1 second, and sqlite 0.05 second)

Hi guys,

Lately, I read in http://www.sqlite.org/speed.html, that sqlite is a bit faster than mysql and postgreSQL. But, when I perform a little testing to compare them in PHP, I got a contrast result.

For both testing, I use this table :
CREATE TABLE test (a varchar(20) PRIMARY KEY, b varchar(20), c varchar(20), d varchar(20), e varchar(20),  f varchar(20), g varchar(20), h varchar(20),  i varchar(20), j varchar(20))

for that table, I perform 5000 insert for both sqlite and mysql :
here’s for sqlite code :
=============== sqlite-test.php ===============

$time_start = microtime(true);
$db = sqlite_open('pegawai.db', 0666, $sqliteerror);

sqlite_exec($db, "PRAGMA synchronous = OFF;");
for($i = 0; $i < 5000; $i++)
{
$sql = "INSERT INTO test VALUES ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
$q = sqlite_query($db, $sql);
}
sqlite_close($db);
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "executed in $time seconds\n";

=========================================

and here’s for mysql code :
============= mysql-test.php ================

.............
$time_start = microtime(true);
for($i = 0; $i < 5000; $i++)
{
$sql = "INSERT INTO test VALUES ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
$q = mysql_query($sql);
}
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "executed in $time seconds\n";
..............

===========================================

when I run those 2 script, the first one (sqlite) run for 43.4918169975 seconds, while the second (mysql) only take 0.52137298584 seconds.

It means that sqlite run 80x longer than mysql did (very contrast with the one I read in http://www.sqlite.org/speed.html).

Please tell me where do I did wrong. Thanks very much.

Advertisements

Front End Logging (tail -f stuff)

Entering a new coding environment (working in a national company), I’ve got several things learned.

One of the important is that I have to take care of every logging activities. It means that people needs me to write any transaction (success, fail, or error) to a flat file.

After my application’s been launching live, the “operation people” can check the log file for any transaction activities using unix command :

tail -f <log_file>

hmmmm…

Rather than supply them with backend checking, I’d prefer to make it (checking log file) into frontend using php.

Here’s the code I scratch to “tail” the log file in FrontEnd :

<html>
<head>
<script language="javascript">
setInterval ( "window.scrollBy(0,100)", 5 );
</script>
</head>
<body>
<a href="http://localhost/tesapps">Click</a>
<?
if(!empty($_GET))
{
$page = $_GET[log]; //supply this GET variable with path to log file
system("tail -f $page");
}
else
echo "<h1>Please provide log file to read !</h1>";
?>
</body>
</html>

With this code, I can simulate tail activities in web browser

nice, huh? 🙂