Recursion function takes a lot of time to load!

Ik · 02-17-2011, 06:32 AM

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

Code:

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>';
		}
	}
}

sdsinc · 02-17-2011, 06:58 AM

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.
????: NamePros.com http://www.namepros.com/programming/702787-recursion-function-takes-lot-time-load.html

Also, you're multiplying function calls with

Code:

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 · 02-17-2011, 10:35 AM

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

sdsinc · 02-17-2011, 12:47 PM

Untested but it would be something along these lines:

PHP Code:

  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"];

????: NamePros.com http://www.namepros.com/showthread.php?t=702787
????: NamePros.com http://www.namepros.com/showthread.php?t=702787
        

            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 · 02-18-2011, 01:41 AM

Hi,

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

sdsinc · 02-18-2011, 03:18 AM

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 · 02-18-2011, 11:34 PM

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.

defaultuser · 02-18-2011, 11:48 PM

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 · 02-18-2011, 11:55 PM

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 · 02-22-2011, 01:43 AM

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 · 03-08-2011, 04:17 PM

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 · 03-09-2011, 01:57 AM

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:

Code:

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

Originally Posted by 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.
????: NamePros.com http://www.namepros.com/showthread.php?t=702787

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

02-17-2011, 06:58 AM	#2 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	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. ????: NamePros.com http://www.namepros.com/programming/702787-recursion-function-takes-lot-time-load.html Also, you're multiplying function calls with Code: 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. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

02-17-2011, 12:47 PM	#4 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	Untested but it would be something along these lines: PHP Code: 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"]; ????: NamePros.com http://www.namepros.com/showthread.php?t=702787 ????: NamePros.com http://www.namepros.com/showthread.php?t=702787 echo '<li><span>'; echo htmlentities($row["name"]); catsTree($selected, $id_cat, $level); products($id_cat); echo '</li></span>'; $i++; } if($level > 1){ echo '</ul>'; } } } __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

02-18-2011, 03:18 AM	#6 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	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. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

02-18-2011, 11:48 PM	#8 (permalink)
defaultuser Senior Member Join Date: May 2009 Location: internet@ctivist.com Posts: 4,790	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. __________________ A Member of: IdeationTeam.com HowToBeADomainer.com - a Domaining How To AuthorEditor.com

03-08-2011, 04:17 PM	#11 (permalink)
Peter NamePros Expert Join Date: Nov 2003 Location: Scotland Posts: 5,074	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? __________________ Manage your portfolio using my new Domain Portfolio Management script. Securing Your Domain Name From Theft

02-17-2011, 10:35 AM	THREAD STARTER #3 (permalink)
Ik Quality // Join Date: Sep 2008 Posts: 911	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

02-18-2011, 01:41 AM	THREAD STARTER #5 (permalink)
Ik Quality // Join Date: Sep 2008 Posts: 911	Hi, I applied your code, but it didn't help! Same poor performance!

02-18-2011, 11:34 PM	THREAD STARTER #7 (permalink)
Ik Quality // Join Date: Sep 2008 Posts: 911	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.

02-18-2011, 11:55 PM	THREAD STARTER #9 (permalink)
Ik Quality // Join Date: Sep 2008 Posts: 911	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.

02-22-2011, 01:43 AM	#10 (permalink)
richiathton07 Account Suspended Join Date: Feb 2011 Posts: 4	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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Tween Problem	food_consult	Graphic Design / Flash	0	01-26-2007 04:56 PM
DomainSponsor Landing Page Load Time -ugh	Dillpup	Parking & Traffic Monetization	3	04-20-2005 06:55 PM
eWebDevelopment.net - Load time?	goldsgym	Web Design Reviews	9	05-25-2004 06:11 PM