Spring JPA and JDBC Template - very slow select query execution with IN clause-CodePudding

I am trying to execute the following query from my Java project. I am using MySQL and data store and have configured Hikari CP as Datasource.

SELECT iv.* FROM identifier_definition id 
INNER JOIN identifier_list_values iv on id.definition_id = iv.definition_id
where 
     id.status IN (:statuses)
 AND id.type = :listType
 AND iv.identifier_value IN (:valuesToAdd)

MySQL connection String:

jdbc:mysql://hostname:3306/DBNAME?useSSL=true&allowPublicKeyRetrieval=true&useServerPrepStmts=true&generateSimpleParameterMetadata=true

When I execute this same query from MySQL workbench it returns results in 0.5 sec.

However when I do the same from JPA Repository or Spring JDBC Template its taking almost 50 secs to execute.

This query has 2 IN clauses, where statuses collection has 3 only items whereas identifierValues collection has 10000 items.

When I execute raw SQL query without named params using JDBC template it got results in 2 secs. However, this approach is suseptible to SQL injection.

Both JPA and JDBC Templete under the hood makes used of Java PreparedStatement. My hunch is the underlying PreparedStatement while adding large params set is causing performance issue.

How do I improve my query performance?

Following is the JDBC template code that I am using:

@Component
public class ListValuesDAO {

    private static final Logger LOGGER = LoggerFactory.getLogger(ListValuesDAO.class);

    private final NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    public ListValuesDAO(DataSource dataSource) {
        jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public void validateListOverlap(List<String> valuesToAdd, ListType listType) {

    String query = "SELECT iv.* FROM identifier_definition id  "  
            "INNER JOIN identifier_list_values iv on id.definition_id = iv.definition_id where "  
            "id.status IN (:statuses) AND id.type = :listType AND iv.identifier_value IN (:valuesToAdd)";

    List<String> statuses = Arrays.stream(ListStatus.values())
            .map(ListStatus::getValue)
            .collect(Collectors.toList());

    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("statuses", statuses);
    parameters.addValue("listType", listType.toString());
    parameters.addValue("valuesToAdd", valuesToAdd);

    List<String> duplicateValues = jdbcTemplate.query(query, parameters, new DuplicateListValueMapper());

    if (isNotEmpty(duplicateValues)) {
        LOGGER.info("Fetched duplicate list value entities");
    } else {
        LOGGER.info("Could not find duplicate list value entities");
    }
}

EDIT - 1

I came across this post where other's faced similar issue while running select query using PreparedStatement on MS SQL Server. Is there any such property like "sendStringParametersAsUnicode" available in MySQL?

EDIT - 2

Tried enabling few MySQL Performance related properties. Still the same result.

jdbc:mysql://localhost:3306/DBNAME?useSSL=true&allowPublicKeyRetrieval=true&useServerPrepStmts=true&generateSimpleParameterMetadata=true&rewriteBatchedStatements=true&cacheResultSetMetadata=true&cachePrepStmts=true&cacheCallableStmts=true

CodePudding user response：

I think should enable "show_sql" to true in JPA and then try, I think its running multiple queries because of lazy loading because of which it may be taking time.

CodePudding user response：

Composite indexes to add to the tables:

id:  INDEX(type, status, definition_id)
id:  INDEX(definition_id, type, status)
iv:  INDEX(identifier_value, definition_id)
iv:  INDEX(definition_id, identifier_value)

For jdbc, the connection parameters should include something like

?useUnicode=yes&characterEncoding=UTF-8

For further discussion, please provide SHOW CREATE TABLE for each table and EXPLAIN SELECT... for any query in question.