Announcement Announcement Module
Collapse
No announcement yet.
Performance issues with jdbctemplate.query Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance issues with jdbctemplate.query

    Guyz,

    We are facing extremely slow performance with jdbctemplate.query whereas the usual plain jdbc statements brings the records at once.


    Blow is our code which tries execute simple select statement without any conditions.
    -------------------------------------------
    System.out.println(new Date()+"-Before creating JdbcTemplate instance");
    JdbcTemplate jt = new JdbcTemplate(getDataSource());
    System.out.println(new Date()+"-After creating JdbcTemplate instance and Before executing jt.query");
    List MrpList = jt.query(MrpConstants.MRP_DATA_VW, new MrpMapper());
    System.out.println(new Date()+"-After jt.query:"+MrpList.size());
    OrganizationMaintenanceDTO[] retMrp = new OrganizationMaintenanceDTO[MrpList
    .size()];
    if (MrpList.size() > 0)
    {
    for (int i = 0; i < MrpList.size(); i++)
    {
    System.out.println(new Date()+"-After jt.query: looping records:"+i+1);
    retMrp[i] = (OrganizationMaintenanceDTO) MrpList.get(i);
    }
    }
    -------------------------------------------
    20,000 records - 2 hrs

    Wed Jul 10 11:09:29 EDT 2007-Before creating JdbcTemplate instance
    Wed Jul 10 11:09:29 EDT 2007-After creating JdbcTemplate instance and Before executing jt.query
    Wed Jul 10 13:12:20 EDT 2007-After jt.query:20551
    ------------------------------------
    1400 records - 11 mins

    Wed Jul 11 12:15:29 EDT 2007-Before creating JdbcTemplate instance
    Wed Jul 11 12:15:29 EDT 2007-After creating JdbcTemplate instance and Before executing jt.query
    Wed Jul 11 12:26:20 EDT 2007-After jt.query:1448
    ------------------------------------

    Any help?

    Thanks,
    Nambi

  • #2
    There have been lots of posts like this, it's usually that the test code doesn't compare like for like. It would be useful if you could post both of the tests you are using. If you wrap this in [code] [ /code] tags, it's soooo much easier to read!

    Comment


    • #3
      Code:
      System.out.println(new Date()+"-Before creating JdbcTemplate instance");
      JdbcTemplate jt = new JdbcTemplate(getDataSource());
      System.out.println(new Date()+"-After creating JdbcTemplate instance and Before executing jt.query");
      List MrpList = jt.query(MrpConstants.MRP_DATA_VW, new MrpMapper());
      System.out.println(new Date()+"-After jt.query:"+MrpList.size());
      OrganizationMaintenanceDTO[] retMrp = new OrganizationMaintenanceDTO[MrpList
      .size()];
      if (MrpList.size() > 0)
      {
      for (int i = 0; i < MrpList.size(); i++)
      {
      System.out.println(new Date()+"-After jt.query: looping records:"+i+1);
      retMrp[i] = (OrganizationMaintenanceDTO) MrpList.get(i);
      }
      }
      I am using DBCP connection pool and there are no issues in getting connection ..etc

      Comment


      • #4
        Ok but what about the plain JDBC code?

        Comment


        • #5
          This thread may be relevant; the root cause was a (hidden) data type mismatch resulting in poor index selection.

          Comment


          • #6
            Most of the time it is that what is being tested isn't the same.

            Make sure that when testing your jdbc stuff that you include
            - obtain connection
            - create/fill prepared statement
            - execute prepared statement
            - resultset conversion into objects
            - close connection/resultset

            I've seen a lot of comparisons and they forget to transform the resultset into objects. That makes a major difference but it is like comparing apples and oranges.

            Comment


            • #7
              The metrics are derived just before converting the resultset to object. Even to populate the List, the JdbcTemplate takes so much time.

              Comment


              • #8
                Also, I am using plain select query without any bindings
                Code:
                select * from user order by user_id
                hence I dont think there are issues with any indexing

                Comment


                • #9
                  The metrics are derived just before converting the resultset to object. Even to populate the List, the JdbcTemplate takes so much time.
                  Before converting? That means that you don't compare the same things. For me this states that

                  1. plain Jdbc you measure the time it takes for a query to return (a resultset)
                  2. JdbcTemplate measue time to take query + return + create objects from resultset

                  Also JdbcTemplate isn't doing anything special. It is just a convenience wrapper around your jdbc. Take a look at the code if you don't believe that.

                  However to tell something more about your metrics (and if they are correct or not) we need to see both of the testcases, including the RowMapper and constants you use.

                  Comment


                  • #10
                    Thanks!

                    The issue is with complex mapper.

                    Comment

                    Working...
                    X