Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
PreparedStatement.executeQuery slow

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!
Answers
-
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'?
-
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
-
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?
-
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.