On Jun 9, 12:02 am, AD7six <[email protected]> wrote: > On Jun 8, 11:44 pm, Jamie <[email protected]> wrote: > > > > > > > > > > > On Jun 8, 8:05 am, AD7six <[email protected]> wrote: > > > > On Jun 8, 3:23 pm, Jamie <[email protected]> wrote: > > > > > On Jun 8, 1:07 am, AD7six <[email protected]> wrote: > > > > > > On Jun 8, 12:19 am, Jamie <[email protected]> wrote: > > > > > > > I posted this in the CakePHP core discussion group where it was > > > > > > completely ignored (isn't it supposed to be a place to get input on > > > > > > core code decisions?), but maybe someone here is interested, since > > > > > > it > > > > > > has a big impact on website performance: > > > > > > Most of the team are away just now - but cake already has an > > > > > insertMulti function. > > > > > Ahh, cool - looks like it's used by HABTM inserts. Didn't find it in > > > > any of the documentation, but I guess it's a mostly internal function. > > > > Thanks for pointing me there, I'll definitely be using that. > > > > > > > When saving multiple rows on the same model with saveAll(), I've > > > > > > noticed two things that have an adverse effect on performance: > > > > > > > #1: A separate INSERT query is done for each row > > > > > > #2: A "SELECT LAST_INSERT_ID() AS insertID" query is executed after > > > > > > each INSERT > > > > > > What is your app doing such that inserts are a "major bottleneck"? > > > > > Most likely the way fixtures are imported is something you can use > > > > > directly or as a template. > > > > > In the app in the question, users generate custom sets of reports. > > > > Each report set generation inserts about 1000 rows into a table, each > > > > with a good amount of data. Switching from the default saveAll() > > > > behavior - one INSERT query and one SELECT LAST_INSERT_ID() query, or > > > > 2000 queries in total - to one big INSERT query chopped the insert > > > > time in half, from 4 seconds to 2 seconds. In our busy time of year we > > > > have hundreds of these reports generated every day so that speed > > > > increase will add up. > > > > I don't know your app details of course but if that's really necessary > > > you should stick the request to do your inserts in a queue system, and > > > use a daemon process to process them. If "major bottleneck" means that > > > users are sitting there waiting while there reports are being > > > generated it's more an app-design problem - you've got almost built in > > > user-driven DDOS > > > > AD > > > Well perhaps major bottleneck wasn't the right choice of words. Just > > wanted to convey that it's basically twice as fast to use one INSERT > > query than it is to use multiple INSERT queries, at least for that > > many records (and 1000 isn't really that many). I think that speed > > difference is a pretty big deal. > > If you're inserting things that are already in the db somewhere (or > worse foreach($camefromselect as $row) { insert somewhereelse > $row; }), it's probable that you could do it in a fraction of the time > by using a stored procedure or, if it's simple enough, like so: > > insert into foo (select fields from bar) > > or even > > drop table if exists foo; create table foo as (select fields from bar) > > AD
Good point, but the data does not come from the DB - it's brand spankin' new. -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php
