Re: Multiple Inserts without a loop
I've just made some test populating a database with millions of rows and
the difference between ->save() and ->saveMany() wasn't very important
(~100 query/seconds vs ~125 q/sec) (on a old local server).
But then I've made it work throught
$this->MyModel->getDataSource()->insertMulti('MyModel', $fields, $values)
and the difference is so HUGE ! I've been able to pass ~2000 q/sec
continuously and my code wasn't optimized.
The fact is, parsing the data to go from a standard $dataToSave: array(
'MyModel' => array('data1' ...) ) to a $query compatible with insertMulti
is just a breeze. Why not use this method with the ->saveMany() ? It's just
way more efficient data processing.
Thanks anyhow for the info, saved me probably a month of passive computing
^^
Le mardi 30 septembre 2008 13:12:22 UTC+2, JadB a écrit :
>
>
> DboMysql::insertMulti will return an SQL query similar to the one you
> are looking to get: INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),
> (7,8,9);
>
> You could add this new method to app_controller.php if you want:
>
> function multiSave(&$model, $values, $fields = null)
> {
>$this->db =& $model->getDataSource();
>$this->db->insertMulti($model->table, $fields, $values);
> }
>
> Pass to it something like:
>
> $values = array('(1, 2, 3)', '(4, 5, 6)', '(7, 8, 9)');
> $fields = array('a','b','c');
>
> I might be wrong, but this works for me so far and isn't really any
> kind of 'hack', it's just using what's there in the framework. Hope it
> helps!
>
> JadB
> http://loudbaking.myeasyscripts.com
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to cake-php+unsubscr...@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: Multiple Inserts without a loop
@Chris: no comment...
@RichardAtHome: run any benchmark, you'd quickly realize that there is
a BIG difference between 1 and 1000 insert made to a DB.
@teknoid, Richard: not all applications are about inserting what the
user is trying to insert in the DB, sometimes you have applications
that are building reports or scraping data, etc. those can easily do
over 1000 rows insert at times, all day long...
@seth: in an attempt to answer your question
DboMysql::insertMulti will return an SQL query similar to the one you
are looking to get: INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),
(7,8,9);
You could add this new method to app_controller.php if you want:
function multiSave(&$model, $values, $fields = null)
{
$this->db =& $model->getDataSource();
$this->db->insertMulti($model->table, $fields, $values);
}
Pass to it something like:
$values = array('(1, 2, 3)', '(4, 5, 6)', '(7, 8, 9)');
$fields = array('a','b','c');
I might be wrong, but this works for me so far and isn't really any
kind of 'hack', it's just using what's there in the framework. Hope it
helps!
JadB
http://loudbaking.myeasyscripts.com
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
You're right, multiple "plain jane" inserts are significantly slower than one large INSERT statement. Some quick comparisons showed a huge difference between inserting 1000 rows at once and doing each individually. There's a handful of ways to try and get around this, anything from disabling the indexes while inserting to (the best way, if you can) using LOAD DATA and pushing the information directly to the DB from the filesystem. But the easiest way is already built into Cake. Use a transaction. saveAll() will start a transaction which results in one write to the table. This means the index is regenerated after the lock is lifted and that there is only one write to the filesystem with your changes as opposed to the many writes and index regenerations that you would get with multiple inserts. On Aug 20, 10:55 am, seth <[EMAIL PROTECTED]> wrote: > So it seems most implementations of active record I've used have NOT > had a way to do this, so I guess I'm hoping Cake is above the curve > here... > > I'd like the general ability to do N record inserts (and updates) > without doing N database calls. Essentially, I want something of the > form: > > INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); > > I'd like to do this without writing custom sql. Ideally I'd like to > do this elegantly with associated models in one save(). If I have a > Exam model that hasMany Questions, I'd like to do the following: > > $this->data['Exam']['title'] = 'Midterm Exam'; > $this->data['Exam']['user_id'] = 'kls93mklj03m9d'; > $this->data['Question'][0]['question_txt'] = "Question one > text"; > $this->data['Question'][1]['question_txt'] = "Question two > text"; > $this->Exam->create(); > $this->Exam->save($this->data); > > The way I imagine this would work is actually using two inserts, as > you would need the insert id of Exam to be added to each Question. > Ok, so clearly this isn't trivial, but it also seems extremely useful. > Did someone already implement this, or come up with a relatively > elegant hack? Should I be posting this in the feature request for > cake instead of here? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
atomic 'true', doesn't mean it's going to build the INSERT as described above, in one call. saveAll() is going to do multiple inserts, but within a transaction, which ensures data integrity. On Sep 29, 3:29 pm, Bruno Bergher <[EMAIL PROTECTED]> wrote: > Inserting 100 records all at once will always be better regarding data > consistency. > > If something wrong happens with the DB server between two calls, how > you you tell the application where to pick up next? > > By inserting them all at one (usgin Model::saveAll()) with the > 'atomic' property set to the default of 'true' you won't have to worry > about this. And the performance will certainly be much better, > specially if the DB and Web servers are two different machines, > perhaps in different locations. > > Best, > > Bruno > > On 21 ago, 08:22, RichardAtHome <[EMAIL PROTECTED]> wrote: > > > I think the 1000 was for illustration only. > > > I'd run a few quick benchmarks to see what the real difference between > > 1insertwith 1000 values() and 1000 queries would be. I'm betting it > > wont be much... > > > On Aug 20, 11:04 pm, teknoid <[EMAIL PROTECTED]> wrote: > > > > AFAIK, the syntax you are proposing is MySQL (or at least DB vendor) > > > specific. > > > So, it won't be supported by cake. > > > > And do you reallyinsert1001 rows? > > > > On Aug 20, 5:51 pm, seth <[EMAIL PROTECTED]> wrote: > > > > > > > Right, but how many inserts does this perform? > > > > > > - As many inserts as required > > > > > Yes, but my whole point is that if you are inserting 1000 questions, > > > > this could be done in 2 interests or 1001... Is cake currently smart > > > > enough to do it in 2? > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
Inserting 100 records all at once will always be better regarding data consistency. If something wrong happens with the DB server between two calls, how you you tell the application where to pick up next? By inserting them all at one (usgin Model::saveAll()) with the 'atomic' property set to the default of 'true' you won't have to worry about this. And the performance will certainly be much better, specially if the DB and Web servers are two different machines, perhaps in different locations. Best, Bruno On 21 ago, 08:22, RichardAtHome <[EMAIL PROTECTED]> wrote: > I think the 1000 was for illustration only. > > I'd run a few quick benchmarks to see what the real difference between > 1insertwith 1000 values() and 1000 queries would be. I'm betting it > wont be much... > > On Aug 20, 11:04 pm, teknoid <[EMAIL PROTECTED]> wrote: > > > AFAIK, the syntax you are proposing is MySQL (or at least DB vendor) > > specific. > > So, it won't be supported by cake. > > > And do you reallyinsert1001 rows? > > > On Aug 20, 5:51 pm, seth <[EMAIL PROTECTED]> wrote: > > > > > > Right, but how many inserts does this perform? > > > > > - As many inserts as required > > > > Yes, but my whole point is that if you are inserting 1000 questions, > > > this could be done in 2 interests or 1001... Is cake currently smart > > > enough to do it in 2? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
I think the 1000 was for illustration only. I'd run a few quick benchmarks to see what the real difference between 1 insert with 1000 values() and 1000 queries would be. I'm betting it wont be much... On Aug 20, 11:04 pm, teknoid <[EMAIL PROTECTED]> wrote: > AFAIK, the syntax you are proposing is MySQL (or at least DB vendor) > specific. > So, it won't be supported by cake. > > And do you really insert 1001 rows? > > On Aug 20, 5:51 pm, seth <[EMAIL PROTECTED]> wrote: > > > > > Right, but how many inserts does this perform? > > > > - As many inserts as required > > > Yes, but my whole point is that if you are inserting 1000 questions, > > this could be done in 2 interests or 1001... Is cake currently smart > > enough to do it in 2? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
AFAIK, the syntax you are proposing is MySQL (or at least DB vendor) specific. So, it won't be supported by cake. And do you really insert 1001 rows? On Aug 20, 5:51 pm, seth <[EMAIL PROTECTED]> wrote: > > > Right, but how many inserts does this perform? > > > - As many inserts as required > > Yes, but my whole point is that if you are inserting 1000 questions, > this could be done in 2 interests or 1001... Is cake currently smart > enough to do it in 2? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
On Wed, Aug 20, 2008 at 5:51 PM, seth <[EMAIL PROTECTED]> wrote: > > >> > Right, but how many inserts does this perform? >> >> - As many inserts as required >> > > Yes, but my whole point is that if you are inserting 1000 questions, > this could be done in 2 interests or 1001... Is cake currently smart > enough to do it in 2? If you set debug in config/core.php to 1 or greater you can see this for yourself. You'll see all the SQL calls. Or, if you're feeling really adventurous, you could actually build the damn thing and see if it matters. I'm willing to bet that it won't matter to your application's performance whether it's 2 or 1000. If it *does* matter, then congratulations are in order because you've built an application with more users than 99% of CakePHP apps out there and you will have other problems not related to Cake to worry about. -- Chris Hartjes Motto for 2008: "Moving from herding elephants to handling snakes..." @TheKeyBoard: http://www.littlehart.net/atthekeyboard --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
> > Right, but how many inserts does this perform? > > - As many inserts as required > Yes, but my whole point is that if you are inserting 1000 questions, this could be done in 2 interests or 1001... Is cake currently smart enough to do it in 2? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
> Right, but how many inserts does this perform? - As many inserts as required > And does the auto-magic know to use the Exam's newly created id? - Yes On Aug 20, 5:37 pm, seth <[EMAIL PROTECTED]> wrote: > Right, but how many inserts does this perform? And does the auto- > magic know to use the Exam's newly created id? > -seth > > On Aug 20, 10:36 am, teknoid <[EMAIL PROTECTED]> wrote: > > > use saveAll() to store multiple records > > > On Aug 20, 12:55 pm, seth <[EMAIL PROTECTED]> wrote: > > > > So it seems most implementations of active record I've used have NOT > > > had a way to do this, so I guess I'm hoping Cake is above the curve > > > here... > > > > I'd like the general ability to do N record inserts (and updates) > > > without doing N database calls. Essentially, I want something of the > > > form: > > > > INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); > > > > I'd like to do this without writing custom sql. Ideally I'd like to > > > do this elegantly with associated models in one save(). If I have a > > > Exam model that hasMany Questions, I'd like to do the following: > > > > $this->data['Exam']['title'] = 'Midterm Exam'; > > > $this->data['Exam']['user_id'] = 'kls93mklj03m9d'; > > > $this->data['Question'][0]['question_txt'] = "Question > > > one text"; > > > $this->data['Question'][1]['question_txt'] = "Question > > > two text"; > > > $this->Exam->create(); > > > $this->Exam->save($this->data); > > > > The way I imagine this would work is actually using two inserts, as > > > you would need the insert id of Exam to be added to each Question. > > > Ok, so clearly this isn't trivial, but it also seems extremely useful. > > > Did someone already implement this, or come up with a relatively > > > elegant hack? Should I be posting this in the feature request for > > > cake instead of here? > > --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
Right, but how many inserts does this perform? And does the auto- magic know to use the Exam's newly created id? -seth On Aug 20, 10:36 am, teknoid <[EMAIL PROTECTED]> wrote: > use saveAll() to store multiple records > > On Aug 20, 12:55 pm, seth <[EMAIL PROTECTED]> wrote: > > > So it seems most implementations of active record I've used have NOT > > had a way to do this, so I guess I'm hoping Cake is above the curve > > here... > > > I'd like the general ability to do N record inserts (and updates) > > without doing N database calls. Essentially, I want something of the > > form: > > > INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); > > > I'd like to do this without writing custom sql. Ideally I'd like to > > do this elegantly with associated models in one save(). If I have a > > Exam model that hasMany Questions, I'd like to do the following: > > > $this->data['Exam']['title'] = 'Midterm Exam'; > > $this->data['Exam']['user_id'] = 'kls93mklj03m9d'; > > $this->data['Question'][0]['question_txt'] = "Question one > > text"; > > $this->data['Question'][1]['question_txt'] = "Question two > > text"; > > $this->Exam->create(); > > $this->Exam->save($this->data); > > > The way I imagine this would work is actually using two inserts, as > > you would need the insert id of Exam to be added to each Question. > > Ok, so clearly this isn't trivial, but it also seems extremely useful. > > Did someone already implement this, or come up with a relatively > > elegant hack? Should I be posting this in the feature request for > > cake instead of here? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Multiple Inserts without a loop
use saveAll() to store multiple records On Aug 20, 12:55 pm, seth <[EMAIL PROTECTED]> wrote: > So it seems most implementations of active record I've used have NOT > had a way to do this, so I guess I'm hoping Cake is above the curve > here... > > I'd like the general ability to do N record inserts (and updates) > without doing N database calls. Essentially, I want something of the > form: > > INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); > > I'd like to do this without writing custom sql. Ideally I'd like to > do this elegantly with associated models in one save(). If I have a > Exam model that hasMany Questions, I'd like to do the following: > > $this->data['Exam']['title'] = 'Midterm Exam'; > $this->data['Exam']['user_id'] = 'kls93mklj03m9d'; > $this->data['Question'][0]['question_txt'] = "Question one > text"; > $this->data['Question'][1]['question_txt'] = "Question two > text"; > $this->Exam->create(); > $this->Exam->save($this->data); > > The way I imagine this would work is actually using two inserts, as > you would need the insert id of Exam to be added to each Question. > Ok, so clearly this isn't trivial, but it also seems extremely useful. > Did someone already implement this, or come up with a relatively > elegant hack? Should I be posting this in the feature request for > cake instead of here? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
