Active Record Multiple Insert

[eluser]dmorin[/eluser]
I'm in the process of migrating all of my queries to the active record pattern and I need to run an insert query that inserts multiple rows into one table. The query is something like:

Code:

INSERT INTO table (field1, field2) VALUES (1,1), (2,2), (3,3), (4,4)

At any time, I could be inserting anywhere from one set of data to 100. I would prefer not to run 100 separate insert queries for performance reasons. I can't figure out how to get this to work with Active Record. Passing a multidimensional array to the insert method doesn't work.

Does anyone know how I could go about doing this? Is this capability added in 1.6? Has anyone extended the active record class to add this functionality? Thanks for any help you can provide.

[eluser]xwero[/eluser]
To make it into the AR library i think it needs to be supported by most of the databases CI has drivers for. I did some quick check and mysql together with postgresql support the same syntax. For ms sql there is a sql statement solution and for oracle are two solutions possible

[eluser]Colin Williams[/eluser]
It would be great to see this in AR, but it is currently not supported

[eluser]dmorin[/eluser]
Does anyone have any idea of the performance implications of having a multi-insert query as shown above versus doing a separate query for each set of values? I would guess that the connection setup and tear down would be the most resource intensive, but with persistent connections, would the performance be negligible or still significant?

Is there anywhere we can request features be added to future versions?

[eluser]tonanbarbarian[/eluser]
the only issue comes about when you have large records and you are entering a lot of them
you might think in that case that one statement rather than lots of little ones is better, and it is, right up until the point you hit the maximum sql statement length

[eluser]Seppo[/eluser]
It is definetly faster, but it may lock the table for a while, until all inserts are done.

[eluser]BizComputing[/eluser]
I could be totally off base on this, but, I think the real performance diff doesn't come from the connection since if I recall, persist connections are for maintaining a connection between page requests, not sql statement requests.

I think the real performance diff is in 1 how much info needs to be piped between php and the db ( ie, seperate queries have to have a full query def ), and the time it takes for the db server to parse the query.

But the flip side of an all in one statement may be more lock contentions.