BIG (up to 45%) performance increase for categorybox.php

This message was sent from: Suggestions and Proposals
http://forums.oscommerce.com/viewtopic.php?p=199353#199353
----------------------------------------------------------------

Seeing as there's not yet too many shows of interest in my proposal, I thought I'd get some code done and do some quick benchmarking.
And I've thrown in a catchy title this time, just for good measure :-)

I have reworked the database parts of the category box to get some these benchmarks done.

I hope these ideas find their way into osC as I think it would help tremendously with performance. If this is not the right channel to get these ideas across to the dev team please let me know where I should post this instead.

I expect there are similar gains to be made with several other parts of the code, there are more places where there are queries inside loops.

Ok, on to the benchmarks, the first results look [b]really[/b] promising:

All results in seconds are averages over 10 runs, using the same database and server, doing requests for
http://osc-cvs.denet.nl/catalog/index.php?cPath=1 on an (almost) stock osC install. With default number of categories.


[code]
with SHOW_COUNTS 'true':

avg parse time*: 
old code:  .9087
new code:  .6598  (27% faster)

avg query time**:
old code:  .3445
new code:  .1907***    ( 45% faster )
[/code]

[code]
with SHOW_COUNTS 'false':

avg parse time: 
old code:  .6921
new code:  .6159  (11% faster)

avg query time**:
old code:  .2043
new code:  .1755  (14% faster)
[/code]

The code literally reduces the number of database queries with dozens per page. This is especially true when SHOW_COUNTS is enabled. As you can see the new code is faster (In terms of query time) with SHOW_COUNTS enabled than the old code was without!

Keep in mind that these results where for a default install of osC. (I have been playing around with it, so it's not a complete virgin database, there are some reviews and purchases done)
However if you have more categories in your database the performance gain will most likely be even bigger.

The display code for these results is really sloppy, I just wanted to hack something into osC to get the database timings. So take the overall timing benchmarks with a big grain of salt, I have not tried to implement this code efficiently, or functionally equivalent to the orignal code. 


P.S.:
For those of you who read my previous post, I made a mistake there.
The number of queries for the category box are:
SHOW_COUNTS = 'true'
- one query per category that has subcategories.
SHOW_COUNTS = 'false'
- one query per displayed category contaiing subcategories.


Rob


=======
The usual disclaimers apply; These are results on my box not yours, using my data not yours!

* These are of limited value I did not finish the layout code, the code currently constructs a multidimensional array with the category info.

** These are measured by timing each tep_db_query(). So this is [b]excluding[/b] any time setting up the connections. 

*** Sadly there was one .22 result the others were all in the .18 -.19 range