Announcement Announcement Module
No announcement yet.
Slow Oracle Query when MapSqlParameterSource added Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow Oracle Query when MapSqlParameterSource added

    I have a query whose performance dramatically degrades when I add a parameter using Spring.

    As a simple example
            String searchTerm = "852";
            String sql = "select * from myTable where textValue like '852%'";                           //option 1
            //String sql = "select * from myTable where textValue like '" + searchTerm + "%'";  //option 2
            //String sql = "select * from myTable where textValue like :searchTerm";                //option 3
            MapSqlParameterSource paramSource = new MapSqlParameterSource();
            paramSource.addValue("searchTerm", searchTerm + "%", Types.VARCHAR);
            List<SearchResultBean> result = namedParameterJdbcTemplate.query(sql, paramSource, new SearchResultMapper());
            logger.debug("list size = " + result.size());
    If I use option 1 or option 2 it's sub second, but option 3 takes 30 seconds.
    For some reason option 3 causes Oracle to do a full table scan.
    The column in question is defined as a VARCHAR2 and has an index.
    The actually query is more complex but it seems to boil down to me using option 3 and the explain plan changing and I have no idea why.

    Anyone any suggestions or solutions?


  • #2
    I think that the difference might be due to use of % rather than a Spring issue as Oracle is doing a full table scan.


    • #3
      Option 1 and option 2 are the same so nothing strange there.

      Have you tried the addValue method WITHOUT a sql type? It might be due to type compatibility that Oracle decides to do a full table scan. I'm also wondering what a normal jdbc template would do when you use a ? instead of a named value.


      • #4
        I'd agree with rishishehrawat that most likely the use of a bind variable is confusing the optimizer and forcing a table scan. Try using a hint or avoid the bind variable. There is lots of interesting info for query optimization in the Oracle docs.


        • #5
          Hi option 1 uses the % and uses the index, I think if you are using %852% the index can't be used, but wouldn't be that sure.

          Originally I had addValue without the sql type and had the same problem. I thought because the string passed in was a number Oracle was doing a type conversion which would cause a full table scan so I added in the type to be sure.

          I'll try a normal template and let you know.



          • #6
            I tried JdbcTemplate and then a direct java connection and got the same results, so apologies Spring is innocent in this.
            But thanks to all as it helped me find a solution, I read the link trisberg gave me (not all of it obviously) and found out I needed bind variable peeking.
            This is what was causing the full scan, or a lack of it.
            I then found this

            And upgraded my jdbc driver to Oracles latest "11g Release 2 ( JDBC Drivers" which has enabled bind peeking and my query is now fast.