Re: __mergeHasMany causing EXTREMELY slow data retrieval
Thanks for the replies and suggestions everyone. When I have a chance I will dig deeper into this. I've implemented a workaround that is fine for now. I was mainly curious if this was a known issue or just poor implementation on my part. Pete's suggestion may be the solution, I'll know after I try it. As I said the actual queries are correct and add no overhead. The overhead is in __mergeHasMany's building of the result array. I don't see how doing a custom query would solve this and may result in a mal- formatted result array. This is a very basic query. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
On Feb 5, 5:27 pm, Dan grip...@gmail.com wrote:
Thanks for the replies and suggestions everyone. When I have a chance
I will dig deeper into this. I've implemented a workaround that is
fine for now. I was mainly curious if this was a known issue or just
poor implementation on my part. Pete's suggestion may be the solution,
I'll know after I try it.
As I said the actual queries are correct and add no overhead. The
overhead is in __mergeHasMany's building of the result array. I don't
see how doing a custom query would solve this and may result in a mal-
formatted result array. This is a very basic query.
What you should have discovered by now, is that doing *any* kind of
loop in php when you don't need to is the wrong approach.
Here's what I would suggest is the best approach for generating csv
database dumps:
The SELECT ... INTO OUTFILE statement is intended primarily to let
you very quickly dump a table to a text file on the server machine.
Taken from http://dev.mysql.com/doc/refman/5.1/en/select.html
Irgo, anything at all that you want to dump, which you can generate
via a query, should be dumped using this approch.
If the db isn't mysql, it should still be possible to do the
equivalent of:
mysql -e SELECT ... file_name
If it *is* necessary to use php logic in the act of generating the
report data there are 2.1 obvious choices:
1) write a loop to process the data, write to a tmp table and use the
above approach
2) write a batch-loop process, e.g. while($results = $this-find
('pending', array('limit' = 100))) { .. foreach($results as
$result) {} $this-commit(); }
expecting to be able to find massive amounts of data and then loop
over them is inevitably going to end in tears.
hth,
AD
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
There's other simpler way if you have limited access to the server.
You must do the query by hand, get the datasource for your model,
execute the query with the query() method, and iterate throught the
Result resource identifier you receive from the query. In order to
dump this to a file, if the data is potentially big, you would create
a new file, and append each chunk of data or each line while iterating
with the query result, and then close the file. If you create a big
string and try to file_put_content it in a file, you'll eventually end
up in the same kind of problem, only this time will be memory limit.
On Feb 5, 2:51 pm, AD7six andydawso...@gmail.com wrote:
On Feb 5, 5:27 pm, Dan grip...@gmail.com wrote:
Thanks for the replies and suggestions everyone. When I have a chance
I will dig deeper into this. I've implemented a workaround that is
fine for now. I was mainly curious if this was a known issue or just
poor implementation on my part. Pete's suggestion may be the solution,
I'll know after I try it.
As I said the actual queries are correct and add no overhead. The
overhead is in __mergeHasMany's building of the result array. I don't
see how doing a custom query would solve this and may result in a mal-
formatted result array. This is a very basic query.
What you should have discovered by now, is that doing *any* kind of
loop in php when you don't need to is the wrong approach.
Here's what I would suggest is the best approach for generating csv
database dumps:
The SELECT ... INTO OUTFILE statement is intended primarily to let
you very quickly dump a table to a text file on the server machine.
Taken fromhttp://dev.mysql.com/doc/refman/5.1/en/select.html
Irgo, anything at all that you want to dump, which you can generate
via a query, should be dumped using this approch.
If the db isn't mysql, it should still be possible to do the
equivalent of:
mysql -e SELECT ... file_name
If it *is* necessary to use php logic in the act of generating the
report data there are 2.1 obvious choices:
1) write a loop to process the data, write to a tmp table and use the
above approach
2) write a batch-loop process, e.g. while($results = $this-find
('pending', array('limit' = 100))) { .. foreach($results as
$result) {} $this-commit(); }
expecting to be able to find massive amounts of data and then loop
over them is inevitably going to end in tears.
hth,
AD
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
Hi Dan,
this isn't a major flaw in CakePHP, it's the price you pay for the
ease of development. Fortunately Cake allows you very easy access to
SQL itself if you need it - although you may end up having to do some
more old school management of your result set rather than having
cake turn everything into handy arrays for you.
It's also possible that you'll be able to get the data you want by
turning your find() call around. I think you might look at this as a
call on your OrderLineItem model rather than Order, so inside the
OrderLineItems Controller you could do this:
$this-OrderLineItem-recursive = -1;
$orderlineitems = $this-OrderLineItem-find('all',array
('contain'=array('Order')));
$this-set( compact('orderlineitems') );
(or inside the model by skipping the -OrderLineItem bit)
That will be fast, and I think won't need any merging.
You said you had simplifed your example so it might not work in which
case you really will have to do something like:
$this-Order-execute(' some kind of SQL in here ')
and manually manage the result set.
Hope that helps
Pete
On Jan 30, 4:47 pm, Dan grip...@gmail.com wrote:
No one has feedback on this? It seems like a MAJOR issue if you can't
load more than a few thousand rows from has many relationship tables.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
No one has feedback on this? It seems like a MAJOR issue if you can't load more than a few thousand rows from has many relationship tables. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
On Jan 30, 5:47 pm, Dan grip...@gmail.com wrote: No one has feedback on this? It seems like a MAJOR issue if you can't load more than a few thousand rows from has many relationship tables. IMO, quite simply you're using the wrong approach. check the group for csv dumps and the likes. AD --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
There's probably a more optimal way to run the loop, but apparently one's run into this particular issue before. Your options are (a) file an optimization ticket and wait for someone to fix it, or (b) file an optimization ticket with a patch ping a core developer (gwoo, AD7six, Mark Story, me, etc.) to get it reviewed and committed, so no one else has this problem. Ain't Open Source the greatest? On Jan 30, 11:47 am, Dan grip...@gmail.com wrote: No one has feedback on this? It seems like a MAJOR issue if you can't load more than a few thousand rows from has many relationship tables. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
You might want to consider creating a finderQuery for this. (I'm definitely not the person to ask how to do that, btw) Of course, you could also select without the HABTM, then use the Set class to grab your IDs and make a new query for the OrderLineItems. Then merge using Set again. Or something like that. Without seeing your setup, I can't be more specific than that. But it might be an improvement. On Fri, Jan 30, 2009 at 2:52 PM, Nate nate.ab...@gmail.com wrote: There's probably a more optimal way to run the loop, but apparently one's run into this particular issue before. Your options are (a) file an optimization ticket and wait for someone to fix it, or (b) file an optimization ticket with a patch ping a core developer (gwoo, AD7six, Mark Story, me, etc.) to get it reviewed and committed, so no one else has this problem. Ain't Open Source the greatest? On Jan 30, 11:47 am, Dan grip...@gmail.com wrote: No one has feedback on this? It seems like a MAJOR issue if you can't load more than a few thousand rows from has many relationship tables. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
The exponential number would happen if your join isn't right. Are you saying that it's fetching 8 million rows, or that you think there's a looping bug in the code? On Jan 19, 7:48 am, Dan grip...@gmail.com wrote: On Jan 16, 4:57 pm, Miles J mileswjohn...@gmail.com wrote: Why do you even need to grab 2800? I need to load them to generate order statistics. Another reason would be for a CSV export. 2800 is very small. We're in the process of developing an app that could have hundreds of thousands of rows in multiple tables with a has many relationship. Also whats the speed like for a low amount of results. And what happens when you limit what fields are returned. I'm already limiting the fields to exactly what I need. I just simplified it for this post. I did some additional testing and it seems to loop (Order rows)^ (OrderLineItem rows) times! That's almost 8 million loops to process 2800 rows of data. It seems to loop through every Order row for every OrderLineItem row. Anyone know why it would do this? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
On Jan 16, 4:57 pm, Miles J mileswjohn...@gmail.com wrote: Why do you even need to grab 2800? I need to load them to generate order statistics. Another reason would be for a CSV export. 2800 is very small. We're in the process of developing an app that could have hundreds of thousands of rows in multiple tables with a has many relationship. Also whats the speed like for a low amount of results. And what happens when you limit what fields are returned. I'm already limiting the fields to exactly what I need. I just simplified it for this post. I did some additional testing and it seems to loop (Order rows)^ (OrderLineItem rows) times! That's almost 8 million loops to process 2800 rows of data. It seems to loop through every Order row for every OrderLineItem row. Anyone know why it would do this? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: __mergeHasMany causing EXTREMELY slow data retrieval
Why do you even need to grab 2800? Also whats the speed like for a low amount of results. And what happens when you limit what fields are returned. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
