Bulk insert/delete (Re: [CDBI] Re: Make CDBI go fast)
Edward J. Sabol
sabol at alderaan.gsfc.nasa.gov
Fri Feb 16 06:11:29 GMT 2007
Michael Schwern suggested:
>>> * A bulk insert method
>>> * A bulk delete method
>>> Calling ->insert over and over again is inefficient. Having to load
>>> an object only to delete it is even worse. Bulk insert and delete
>>> methods would be handy.
Ed Sabol replied:
>> For some reason, I just don't like the sound of these methods. I would
>> suggest going the CDBI::Plugin route with them instead. Not everything needs
>> to be in the core module.
Michael Schwern asked:
> Got anything more specific about why you don't like it?
I suppose it's mostly a philosophical objection. They stray a little too far
outside the "one CDBI object == one row in the database" paradigm that is
Class::DBI's strength. I strongly suspect that's why Tony deprecated the
Class->delete() method as well.
I think they would make for a fine Plugin though. Just not one I see needing
personally. I'm using creating/inserting single objects at a time. When I'm
loading a table up in bulk, I don't use CDBI.
> If nothing else a bulk insert would be faster because it does not have to
> create return a new object at the end. In fact, insert() could be made to be
> more efficient in void context.
I have no objection to making insert() more efficient when called in void
context. That sounds better to me than the bulk_insert() method you
suggested, and I suspect the improvement in speed would be comparable.
> Bulk inserts don't see to come up too often, and for bulk deletes I
> just write the sql. I actually find set_sql to be just fine for more
> complex tasks.
I agree with Perrin here. For bulk deletes, I would just use SQL with a
suitable WHERE clause. Something like Class::DBI::AbstractSearch but for
deletes could be useful, however. (Class::DBI::AbstractDelete anyone? Should
be pretty straightforward.)
Michael, you might want to check out Class::DBI::Plugin::FastDelete:
More information about the ClassDBI