Search This Blog

Monday, September 13, 2010

In Java, how does PreparedStatement work for the following query

Programmer Question

I have a query like the following and was wondering what kind of SQL is produced by batching a PreparedStatement.



INSERT INTO table1 (id, version, data)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
table1.data = IF(table1.version > table2.version, table1.data, table2.data),
table1.version = IF(table1.version > table2.version, table1.version, table2.version)


The question is, will it resolve this to a copy of this whole sql string for each row in the batch or will it do something like:



INSERT INTO table1 (id, version, data)
VALUES (a1, b1, c1), (a2, b2, c2), (a3, b3, c3), ...
ON DUPLICATE KEY UPDATE
table1.data = IF(table1.version > table2.version, table1.data, table2.data),
table1.version = IF(table1.version > table2.version, table1.version, table2.version)


If not, what is the performance implication and how do I write it in such a way that I can batch many of these INSERT..UPDATE statements using PreparedStatement without incurring a performance penalty?



Find the answer here

No comments:

Post a Comment

Related Posts with Thumbnails