11 replies to this topic

[Balor]

Hi,

does anybody know of an alternative to recursive programming for getting an unlimited amount of subs out of a mysql db? recursive programming is the only good way i know of, but it's really not very performant.

for example a simple tree file folder for an asset management system:

sql would be something like:

id, parent_id, name

if I have hundreds of folders in there, it's really unperformant...

Any ideas are welcome.

Sincerely,
Chris

[Mr. Matt]

Sorry but the only way really is recursion, well that I know off, it is pretty simple to do, providing you don't get stuck in an infinate loop and send yourself 10,000 emails like I did earlier this week it should be fine.

If you are worried about load, then you don't need to show them all at the same time, you can show one or two levels, with links to show the lower levels and so on.

Matt

[Balor]

Mr. Matt, on Aug 31 2007, 08:18 PM, said:

Sorry but the only way really is recursion, well that I know off, it is pretty simple to do, providing you don't get stuck in an infinate loop and send yourself 10,000 emails like I did earlier this week it should be fine.

If you are worried about load, then you don't need to show them all at the same time, you can show one or two levels, with links to show the lower levels and so on.

Matt

Ya it's not the problem to program a recursive function, the only problem is the performance as I wanted to load everything and this can be quite much... Also some php configs don't allow inifinite recursive loops... they just break it off after X loops.

[Demonslay]

Well ya you can't have infinite recursions with PHP. That would mean your user waiting absolutely forever for a page from the server and such. Also, even if you were planning on it for a cron job kind of feature to run independent of a site visitor, that is still a large stress on the web-server.

Recursion is the only way if you want to retrieve everything. I would recommend, as Matt said, to only show so many levels at once, and link to others, if your reasoning is that you have too much load for it. Usually there is no problem when going a good 20 levels down in recursion without too many problems, but I'm guessing you are planning on having like. way too many folders to be worth it. If you have so many levels, it would make more sense to do the linking method anyways, so you aren't displaying so many folders at once to the user and overwhelming them.

[rc69]

Could you show us the code you're using? Since i have no idea what you're talking about, i can't really say whether or not there's a non-recursive method we could try, but i know loops often offer a slight boost in performance over recursion (the fibonacci sequence being a shining example).

[Balor]

Na I don't want to make an infinite loop, also I don't have code to show yet. I just this with the infinite loop because you said that I have to be careful^^ However, I need a performant solution for an asset management system, but it's really large so there will be maaaaaany folders and subfolders... can this stand 1000+ folders?

[rc69]

I have a non-recursive method of sorting through folders in a directory system. "Folders" in a mysql system is some what different and i don't know if i could modify the script or not (which is why i'd of liked to see an example).

Besides, the whole point of recursion is the meeting of a condition to stop it. With out that condition, you have infinite recursion, and that is more deadly than an infinite loop (which would stop on the same condition most likely).

Can it stand 1000+ folders? I don't think anything in this universe can, but you DEFINATELY don't want to use recursion of your going that deep.

This will give you an idea:

/* NOTE: This was found in the php.net user comments and has been slightly modified from
 * it's orginial version. I can no longer find the original comment... */
function rread_dir($folder){
	/* $stack = list of folders */
	$stack[] = $folder;
	while($stack){
		/* Pop a folder off of the stack of folders */
		$current_dir = array_pop($stack);
		/* Slash fix */
		$current_dir = substr($current_dir,-1) == '/' ? substr($current_dir,0,-1) : $current_dir;
		/* Make sure we opened the directory */
		if($dh = opendir($current_dir)){
			/* Run through the files in the directory */
			while(($file = readdir($dh)) !== false){
				if($file != '.' && $file != '..'){
					$list[] = $current_dir.'/'.$file;
					if(is_dir($file)){
						/* Add the directory to the stack, so we can run through it */
						$stack[] = $current_file;
					}
				}
			}
		}else{
			die('rread_dir: Could not open <b>'.($current_dir ? $current_dir : '/').'</b>');
		}
	}

	return $list;
}

Edited by rc69, 01 September 2007 - 11:06 AM.

[mattcch2007]

must a condition statement for getting out of the infinite loop,
it is the most important point,just a little reminder.

I think memory is the key for it, huge of memory may enforce its success.

Edited by mattcch2007, 02 September 2007 - 03:55 AM.

[Balor]

Thanks for your comments, but still not what I exactly needed Seems like there is no way of doing this perfomant at the moment.

[rc69]

Again, an example script of what you're using would help us to better understand what you're doing/thinking of doing, and give us ideas for other ways to go about it.

I'll say this again, if you want to go 1000+ levels deep, you will NOT find anything that will perform at an acceptable rate. Recursion would only add to the problem. If you can find a way to implement the loop algorithm i provided, that would likely be your best option.

[mattcch2007]

hi,

in general;

for such deep level recursive method/function,

there may be the 'stack overflow' problem (forget "heap" part at this moment);

so large memory media is required or use some hard disk space for swapping,

then you maybe need a "memory manager" routine for such task;

maybe m$ guys are specialized in creating this kind of routines

just for your reference only.

Edited by mattcch2007, 02 September 2007 - 06:22 PM.

[Balor]

It's solved, I wrote a function that just fetchs the first folders... onclick the ajax query fetches the sub folders. It's definantly more performant and better for my server

Thank you so much for your help! That's why I love p2l