sql-serverdatabasejava-8simplejdbccall

Optimization of execution of stored procedure in MSSQL server with SimpleJdbcCall


I have the code that executes some stored procedure. mssql-jdbc driver to connect to Microsoft SQL Server database is used.

Here is simplified code

SimpleJdbcCall call = simpleJdbcCallFactory.create(jdbcTemplate)
    .withSchemaName(SCHEMA)
    .withProcedureName(SP)
    .declareParameters(
        new SqlParameter("Id1", Types.INTEGER),
        new SqlParameter("Id2", Types.TINYINT),
        new SqlParameter("Id3", Types.INTEGER))
    .returningResultSet("result", (rs, rowNum) -> MappingObject.builder()
            .id(rs.getInt("Id"))
            .date(rs.getTimestamp("Date"))
            // .......
            .build());

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("Id1", 1);
params.addValue("Id2", 2);
params.addValue("Id3", 3);

Map queryRes = call.execute(params);  

When I run this I see in profiler 3 calls:

I see in profiler 3 sql calls every time I run this one stored procedure. I'd like to make only one call, is it possible with SimpleJdbcCall?


Solution

  • I've just found an answer, maybe this will be useful to somebody. All we need to avoid 3 calls is withoutProcedureColumnMetaDataAccess() for SimpleJdbcCall.

    Like this

    SimpleJdbcCall getQACall = simpleJdbcCallFactory.create(jdbcTemplate)
        .withSchemaName(SCHEMA_ARTICLE)
        .withProcedureName(SP_GET_ALL_QUESTIONS_ANSWERS)
        .withoutProcedureColumnMetaDataAccess()
        .declareParameters(
        // etc