Bulk insert/delete (Re: [CDBI] Re: Make CDBI go fast)

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.