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