Recursion function takes a lot of time to load!

[Ik]

Hi,

I have a recursive function that loads categories and sub-categories and displays them as a jQuery tree on my page.

The code takes a lot of time to run. What can I do to minimize the load time?

Function is below. Please help. Thanks

function catsTree($selected, $parent, $level){
	$level = $level + 1;
	require('config.php');
	$sql = "SELECT name, id_cat, id_parent FROM cat where online = 1 and trash = 0 and id_parent = " . $parent . " order by name";
	$result = mysql_query($sql) or die('error, query failed');
	$num = mysql_num_rows($result);
	$i=0;
	
	if($num > 0){
		if($level > 1){
			echo '<ul>';
		}
		while ($i < $num){
			echo '<li><span>';
			echo mysql_result($result,$i,"name");
			echo '</span>';
			catsTree($selected, mysql_result($result,$i,"id_cat"), $level);
			products(mysql_result($result,$i,"id_cat"));
			echo '</li>';
			$i++;
		}
		if($level > 1){
			echo '</ul>';
		}
	}
}

 

[Kate]

With mysql_result() you're fetching only one field at a time, you should instead fetch a full row (even if you're needing only one field). The function also affects the internal mysql pointer and probably slowing down the other operations. Consider using mysql_fetch_array in your loop.

Also, you're multiplying function calls with

catsTree($selected, mysql_result($result,$i,"id_cat"), $level);

Instead, you should save the field values in variables as you loop through the recordset, and pass the variables to your function.

How many records has table cat ? You may need an index if you have many records. It's one possible bottleneck.

 

[Ik]

Hi,

Thanks for responding.

I'm not sure I get all what you told me, but I know that the cat and product tables are indexed.

We have a total of 120 categories and 550 products, increasing........

Can you modify my function as you see it the best, please?

I have to stick to the recursion because we display all cats and products as a jQuery tree, and categories are nested, example:

Cars
--> 4x4
----> Japanese
------> Mitsubishi
--------> Pajero
----------> Whatever

Thanks for helping me

 

[Kate]

Untested but it would be something along these lines:

function catsTree($selected, $parent, $level){
	$level++;
	require('config.php');
	$sql = "SELECT name, id_cat, id_parent FROM cat where online = 1 and trash = 0 and id_parent = " . $parent . " order by name";
	$result = mysql_query($sql) or die('error, query failed');
	$num = mysql_num_rows($result);
	$i=0;
	
	if($num > 0){
		if($level > 1){
			echo '<ul>';
		}
		
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$id_cat=$row["id_cat"];
		
			echo '<li><span>';
			echo htmlentities($row["name"]);
			catsTree($selected, $id_cat, $level);
			products($id_cat);
			echo '</li></span>';
			$i++;
		}

		if($level > 1){
			echo '</ul>';
		}
	}
}

 

[Ik]

Hi,

I applied your code, but it didn't help! Same poor performance!

 

[Kate]

I don't know the purpose of this:
products($id_cat);
That must be a function.

Did you try running the PHP code stand-alone ? It could be the javascript code that is slow.

 

[Ik]

Hi,

Yes I tried running the code without any JavaScript, and it still takes the same time to load.

The products($id_cat) is a function that lists the products under the current category.

A lot of recursion is there.

 

[DU]

Remove the recursion. You're doing a load of SQL selects EACH ONE with an order by. It would be much better to build a different table structure or store the data better and do a simple query.

Recursion like this is terrible. imho.

 

[Ik]

Hi defaultuser,

Thanks for your input. What would you recommend? could you suggest a sample code?

The table stores the categories in the structure of id and parent id, that's why I had to go for recursion functions.

 

[richiathton07]

Remove the recursion. You are doing a load of SQL select each with an order. It would be better to build a table structure to store the data differently or better and do a simple query.

 

[Peter]

Just a slight side note. As well as the issue already raised another performance hit that you have is that you have require("config.php") in the function. Everytime this function runs it is required to open the file system, locate the file and open it. If this contains the database connection details as well for every function call it will also be making a brand new database connection over writing the old 1. The actual database connect is also a very expensive task which is being over replicated.

If you 100% need the recursive function at least remove the require from the function, place it just outside of the function.

Regarding the actual matter at hand have you looked at sql joins at all?

 

[Ik]

Thanks Peter,

Your advice worked well; performance got much better. But I still don't know how to use joins in this case.

The way the categories structure in my table is like the following:

name
id_cat
id_parent

If the id_parent is 0 that means that this particular category is a main category. Any category with id_parent > 0 is a sub category. We have unlimited levels.

Could you show me how to use joins in this case for better performance?

Thanks

Just a slight side note. As well as the issue already raised another performance hit that you have is that you have require("config.php") in the function. Everytime this function runs it is required to open the file system, locate the file and open it. If this contains the database connection details as well for every function call it will also be making a brand new database connection over writing the old 1. The actual database connect is also a very expensive task which is being over replicated.

If you 100% need the recursive function at least remove the require from the function, place it just outside of the function.

Regarding the actual matter at hand have you looked at sql joins at all?