we are trying to improve a performance with JPA with mysql, the code:
private String getProfileIdsSQL = "select up.user_profile_fk, up.Profile_id from account_config.user_profile as up " +
" where up.user_profile_fk IN (%s)";
.
.
.
String sql = String.format(getProfileIdsSQL, preparePlaceHolders(foreignKeys.length));
Query query = em.createNativeQuery(sql);
for (int i = 0; i < foreignKeys.length; i++) {
query.setParameter("p" + i, foreignKeys[i]);
}
.
.
.
protected static String preparePlaceHolders(int length) {
if (length == 0)
return "";
int capacity = length * 4;
StringBuilder ans = new StringBuilder(capacity);
for (int i = 0; i < length; i++) {
ans.append(":p").append(i).append(",");
}
ans.setLength(ans.length() - 1);
return ans.toString();
}
Run Very slowly but if i skip the parameter binding and write the values in the query string without setParameter
the result is very fast but we can't do it in production, how can we make setParameter
work faster ?
Thanks.