X-Cart forums

[NuAlpha]

I was doing some profiling on Xcart and discovered that insert_productsonline() can take upwards of 15,000 milliseconds to execute, making up about 60% of the shopping cart's resource usage. This happens when MySQL hasn't stored and optimized the query for future use. Most of the time the calling of this function takes an average of 740 ms and about 11% of the shopping cart's execution time & resource usage.

I have found a simple remedy to this problem that produces the same results as the original code, but uses up only an average of 120 ms.

Note that our site holds over 65,000 products, so it is no wonder this takes up a lot of processing power to gather the statistics.

Replace the return line in insert_productsonline() in include/func.php with the following code:

return func_query_first_cell("SELECT COUNT(*) FROM $sql_tbl[products], $sql_tbl[categories] WHERE  $sql_tbl[products].forsale='Y' AND $sql_tbl[products].categoryid=$sql_tbl[categories].categoryid AND ($sql_tbl[categories].membership='$user_account[membership]' OR $sql_tbl[categories].membership='')");

The key to this code speed increase is the usage of COUNT(*) instead of COUNT($sql_tbl[products].productid).

Please note that your must be using a version of Xcart that has the func_query_first_cell() function available. If not, then simply use array_pop(func_query_first()) in its place.

[TelaFirma]

Whole crap... nice catch. Big difference made....


Thanks.

[CopperB]

I tried this MOD and my site only loads a blank white page.

Original code from func.php

#
# Return number of available products
#
function insert_productsonline() {
	global $sql_tbl;

	return func_query_first_cell("SELECT COUNT(*) FROM $sql_tbl[products] WHERE forsale!='N'");

Modded code from func.php

#
# Return number of available products
#
function insert_productsonline() {
	global $sql_tbl;

	return return func_query_first_cell("SELECT COUNT(*) FROM $sql_tbl[products], $sql_tbl[categories] WHERE  $sql_tbl[products].forsale='Y' AND $sql_tbl[products].categoryid=$sql_tbl[categories].categoryid AND ($sql_tbl[categories].membership='$user_account[membership]' OR $sql_tbl[categories].membership='')");

Am I missing or doing something wrong?


Thanks

ps. NuAlpha, thanks for all of your mods and posts. They are much apprecited.

[TelaFirma]

The original post states:

The key to this code speed increase is the usage of COUNT(*) instead of COUNT($sql_tbl[products].productid).

If you look at your original code, you see that the function is:

return func_query_first_cell("SELECT COUNT(*) FROM $sql_tbl[products] WHERE forsale!='N'");

therefore this modification is not for you. You do not need it. COUNT(*) is already written into that function in your version of X-Cart.

[NuAlpha]

Originally Posted by CopperB

Am I missing or doing something wrong?

You have a double "return return" in your modified code instead of just one "return".

Also, as TeleFirma stated, you wouldn't really need this code change as this code already appears to be in effect with Xcart 4.0.11.

[CopperB]

Thanks for the replies guys. It was getting late and obviously I wasn't thinking or reading very clearly. I'm also a newbie and still getting the hang of x-cart.