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. -- 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
