Bulk insert/delete (Re: [CDBI] Re: Make CDBI go fast)
Michael G Schwern
schwern at gmail.com
Thu Feb 15 17:04:49 GMT 2007
Edward J. Sabol wrote:
>> * 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.
> 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.
Got anything more specific about why you don't like it?
> Bulk delete is covered by the other tools mentioned above, but they
> don't support any kind of bulk insert that I'm aware of. To make bulk
> inserts really fast, you have to use database specific extensions,
> like MySQL's multi-row insert statement.
I don't mind putting MySQL specific stuff into CDBI::mysql.
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. It might also be possible to reduce the # of calls to the triggers or at least not have to check if there's a trigger N times, just once.
As for delete there's ample room for improvement. Having to load an object to delete it is just wrong. Something like the deprecated Class->delete(@search) would be handy. Why was that deprecated?
> 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.
If I hand write the SQL the in-CDBI delete-on-cascades won't happen. Of course, ideally that should be in the database and not CDBI.
More information about the ClassDBI