groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike M <mike...@outlook.com>
Subject Re: SQL: Bind variables in INSERT statement resulting in extra SELECT statement
Date Fri, 07 Aug 2020 05:34:14 GMT
A little more investigation shows that the additional SELECT statement originates from a call
to getParameterMetaData by the Groovy Sql object.

The impact is:

  *   Prepared statements are slower than literal SQL statements
  *   Batch inserts are slower than row-by-row inserts

Bug logged at https://issues.apache.org/jira/browse/GROOVY-9676
See attachment to this bug for test details and measurements.
________________________________
From: Mike M <mike_m_@outlook.com>
Sent: 01 August 2020 08:29
To: users@groovy.apache.org <users@groovy.apache.org>
Subject: SQL: Bind variables in INSERT statement resulting in extra SELECT statement

A statement such as:
String qry = "INSERT INTO Author (firstname, lastname) VALUES ('$name', '$name')"
sql.execute qry

results in one database roundtrip, as one would expect.

Using bind variables such as this:
sql.execute "INSERT INTO Author (firstname, lastname) VALUES ($fname, $lname)"

results in 2 roundtrips (as visible in Wireshark):

  *   The INSERT statement, with the bind variables
  *   A subsequent SELECT firstname,lastname FROM Author

Is Groovy generating this second statement, or is it the JDBC driver? And why?
BTW: I suspect there is a where clause on the select, but I do not see it in the wireshark
capture.

Mime
View raw message