Forum Stats

  • 3,784,382 Users
  • 2,254,931 Discussions
  • 7,880,794 Comments

Discussions

Categories

PreparedStatement.executeQuery slow

RP24
RP24 Member Posts: 75 Red Ribbon
edited Mar 2, 2018 8:03PM in Java Database Connectivity (JDBC)

Hey,

I am using AppDynamics to instrument the code I have seen often in the reports that PreparedStatement.executeQuery(), PreparedStatement.executeUpdate(),  resultsSet.next() is very slow taking close to 350, 500 ms whereas the query takes only 10, 15 ms.

I am not sure what is causing slowness within PreparedStatetment.

Any pointers?

The database is oracle. Also we are using DBCP 1.4 connection pool

public ResultSet executeQuery(PreparedStatement statement)

   {

     ResultSet results = null;

     try

    {

       results = statement.executeQuery();   

     }catch(Exception e){

          //Exception handling

     }

return results;

}

}

Thanks!

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Unknown
    Unknown
    edited Feb 28, 2018 2:08PM

    Slow and fast are RELATIVE terms. They are only meaningful when compared to something similar.

    It takes me an hour to get to work.

    Is that 'slow' or 'fast'?

    Please SHOW US:

    1. WHAT you do

    2. HOW you do it

    3. WHAT results you get

    4. Evidence showing those results are 'slow'.

    I am using AppDynamics to instrument the code I have seen often in the reports that PreparedStatement.executeQuery(), PreparedStatement.executeUpdate(),  resultsSet.next() is very slow taking close to 350, 500 ms whereas the query takes only 10, 15 ms.

    Query execution can be 'fast' or 'slow' - again, if depends on what the query is doing. Selecting one row from a table without ANY predicates or conditions is 'fast'. Querying sorted, grouped or aggregated data can be 'very slow'.

    What is it that indicates the query takes only 10 or 15 ms? How do you know the query has completed? What query do you mean - a select or an update?

    Is it the FIRST 'next' taking time? Or subsequent 'next' statements?

    What instrumentation is available from 'AppDynamics'?

    · Share on TwitterShare on Facebook
  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Feb 28, 2018 2:45PM

    Which value has the Fetch Size parameter? Maybe 10?

    Try to put 100, 1000 and see if it works faster (with SELECT commands):

    stmt = conn. prepareStatement ("select c1, c2 from t");

    stmt.setFetchSize (100);

    Of course, maybe the problem is somewhere else.

    It would be good to show what your code is doing (at least the main part).

    Regards,

    Zlatko

    · Share on TwitterShare on Facebook
  • RP24
    RP24 Member Posts: 75 Red Ribbon
    edited Mar 2, 2018 5:48PM 
    What is it that indicates the query takes only 10 or 15 ms? How do you know the query has completed? What query do you mean - a select or an update?

    The performance tool (AppDynamics) shows that the query is taking 10 ms . It also shows that PreparedStatement.executeQuery() method took 350 ms for the query that took 10 ms. My question here is not about the query. What I am asking is  - is there anything in executeQuery method that can slow down the the performance of the method?

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Mar 2, 2018 8:03PM 
    RP24 wrote:What is it that indicates the query takes only 10 or 15 ms? How do you know the query has completed? What query do you mean - a select or an update?The performance tool (AppDynamics) shows that the query is taking 10 ms . It also shows that PreparedStatement.executeQuery() method took 350 ms for the query that took 10 ms. My question here is not about the query. What I am asking is - is there anything in executeQuery method that can slow down the the performance of the method?

    I already addressed all of those things to the extent possible without knowing what that 'tool' is really measuring..

    What you just said above makes no sense given the order that things happen as I explained earlier.

    1. the query is taking 10 ms

    2. the executeQuery() method took 350 ms for the query that took 10ms

    The query doesn't even execute until the executeQuery method submits it to the server. So the order is the EXACT OPPOSITE of what you listed: first the executeQuery submits the query and THEN the query itself is executed by the server.

    I suggest you INSTRUMENT your code to capture the times yourself. For one, add statements directly BEFORE and again  AFTER the 'executeQuery' statement and capture the time in millis. That will tell you how long the 'executeQuery' statement is taking to execute.

    I have no idea what that tool is using to determine that 'the query is taking 10ms'.

    So you need to dig into what AppDynamics is REALLY doing if you want to understand the numbers it is giving you.

    We can't see your monitor and you haven't posted ANY screenshots. So we have no idea what you are looking at.

    · Share on TwitterShare on Facebook
This discussion has been closed.