Batched insert queries, passing in multiple rows as params

Zackster · July 15, 2025, 2:18pm

After looking into this problem a few weeks ago, it got me thinking

this was using the approach of multiple values with an insert

insert into tbl (id) values (?) ,(?), (?).....etc

At the moment it’s either that approach or looping over an insert statement, but that’s not great performance wise even within a transaction.

For the multi-table QoQ, we load all the queries using jdbc into a temp database for we can execute them

github.com

lucee/Lucee/blob/7.0/core/src/main/java/lucee/runtime/db/HSQLDBHandler.java#L156


      
          	 * populates a table to the memory database, but only the required columns from the source query
          	 *
          	 * @param conn
          	 * @param pc
          	 * @param name name of the new table
          	 * @param query data source for table
          	 * @throws SQLException
          	 * @throws PageException
          	 */
          
          	private static void populateTable(Connection conn, PageContext pc, String dbTableName, String cfQueryName, boolean doSimpleTypes, Struct tableCols)
          			throws SQLException, PageException {
          
          		// Stopwatch stopwatch = new Stopwatch(Stopwatch.UNIT_MILLI);
          		// stopwatch.start();
          		Query query = Caster.toQuery(pc.getVariable(StringUtil.removeQuotes(cfQueryName, true)));
          
          		Key[] cols = CollectionUtil.keys(query);
          		ArrayList<String> targetCols = new ArrayList<String>();
          
          		int[] srcTypes = query.getTypes();

My idea was to allow passing in multiple rows into cfquery and then efficiently inserting them once, avoiding going thru all the query processing code for each row, or using massive value statements like above

I’ve started on a proof of concept PR for this with <CFQUERY>

cfquery( sql=insertSql, params=arr, datasource=ds, batch=true );

https://luceeserver.atlassian.net/browse/LDEV-5704

It’s a bit hacky and needs more refactoring but the initial results are quite promising for high performance inserts (i.e it can go even much faster)

 [java]    [script]         test.tickets.LDEV5704
 [java]    [script] individual took 287ms for 1000 rows
 [java]    [script] individual (w/transaction) took 188ms for 1000 rows
 [java]    [script] batch took 45ms for 1000 rows

I wouldn’t mind being able to potentially supporting passing in a query as params and have Lucee automatically read the query metadata and handle all the sqltypes etc (which I’m manually doing in the testcase for now)