Out with SMARTY and Pear DB

[drifter]

Hey I am not sure if anyone cares, but I made some changes to my site. Hope someone will find this information useful.

Smarty->template-lite(modified)
Pear DB ->ezSQL(modified)

>>SMARTY>>

First I removed SMARTY - I went with template lite (source forge)- it is supposed to be a SMARTY drop in replacement, but that did not work so well - I spend about 5 hours debugging and changing features. I also stripped out all the caching and debugging code. My page has very few static parts, that are not changed by user preferences and queries etc, so I do not cache. Biggest problems I had were with the sections and the append/merge - these were both there but when they were ported from SMARTY there were some errors.

The result was first that my pages are MUCH faster. after doing this most of my pages (ones without huge database work) now loaded between .05-.08 seconds. When running SMARTY, I was getting pages times of .09-.12 - so this is quite an improvements. I have also notices since SMARTY was so large, my APC is now able to cache a lot more pages with the extra space cleared out... not sure if this is part of the speed up. The new component is about 1/7th the size.

>>PEAR DB>>

Next I went ahead and started work on removing Pear DB. I chose to go with ezSQL as the replacement You can find it [url=http://drifterlrsc.users.phpclasses.org]HERE[/url] just search for ezsql. This class is much difference then pear DB so I had to work on my code a bit. This change took me about 3 hours. Basically everyplace I have a select, I replace $db->query() with $db->get_results. Everplace I had a while($row=$res->getRow())- I replaced it with a foreach($res AS $row) - there are a few other tweaks, and functions I added to the class such as to get the insert ID. I did not use any of the Pear DBs prepare functions or anything like that so I did not have to worry. I do like having this in a seperate class rather then having the mysql functions in my code just incase I want to change DBs. With Pear that would have been easy, but even how I only have to rewrite like 10 small functions.

The results here were also far better then expected. Now that me average page speeds were at .05-.08 after getting rid of SMARTY, I did not think I would get much faster. Well.... Now most of my pages load between .02-.04 - that is a lot faster. I was noticing these speed increase even on pages that only have one small query. That tells me that just loading Pear DB was the problem. One my page that returns a large record set. 20 rows, about 100 columns - I noticed an even bigger speed increase. My page went from .30 to .17 using this new class. I am not sure if this is due to the way Pear DB handles the result set or what. This new class is also about 1/7th the size of the old class.

So overall, in about 8 hours, I droped my page creation times by 66% just getting rid of these classes. Now these page times a not under heavy load testing, but on my live server. All page times are average of all page views on a script over the course of a day.

The other nice side benifit is these new classes are so much smaller, that it is not intimidating to get in there and change something if you need to. With those other classes, making a minor tweak left me with a feeling of "where do I even begin"

Hope Someone finds this information usefull.

[trq]

Can I ask why you would want to use ezSQL over mysqli or even the mysql_* functions?

[drifter]

really the reason comes down to that fact that I do not know everything there is to know about databases. I may not want to be using mysql in the future as I get more traffic. Maybe oracle or maybe something else.

Most of what is in this function is the basic mysql functions anyway. For example my function $db->insert_id() is just

function insert_id(){
  return mysql_insert_id();
}

Some are a little bit more complex, but like I have it set up so I do
[code]
$rows=$db->get_results($sql);
foreach($rows AS $row){

}
[/code]
and that returns and assoc array I can just loop through. All in one step rather then
[code]
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
}
mysql_free_result($result);
[/code]

Now if I deside to move to say Oracle, I only have to write the equivalent code in my functions rather then rewrite my application.

My understanding is that is the major benefit to classes like MBD2 and BD - the problem is they are so big and there is so much stuff, they are hard to work on and are slow. (IMO)

[drifter]

Another benefit of running this through the class, to day I wrote a line in the query function to add every  call to the database to a string. Then in my disconnect function right before disconnect, I dump all the string into the DB -

wrote like a 20 line little script to read that table and tell me how many queries and what they are for every page on my site.

Then I can see what ones can be cached and such to cut down on extra stuff as much as possible.

I could take this a step farther and wrap the mysql_query statement with a timer and log all the calls and see what ones are slow. Doing this in my class would be WAY easier then filling my entire site with timers.