In the project at IVV Hannover most of our problems stem from inperformant queries or just too many of them. inspectIT (currently version 1.3) can be improved to improve both the performance (by reducing the overhead) and the functionality. This page is a collection of ideas what we can do.
Do not sent everything
In the beginning of inspectIT we really sent everything. We also sent information for the SQL sensor that are in 99,99% not necessary on the client/UI. In case of SQL this is prepareStatement calls and all setter that are necessary to built up the concrete SQL query (parameter replacement):
- Do not sent prepareStatements
- We only capture prepare statements in order to be able to capture the SQL that will be executed (as the preparedstatement object does not hold this information in an general accessible format)
- Improvement: We send way less data. For the IVV, preparing is delegated to 4 different Connection objects, so for every prepare we get in fact 4 measurements. Thus invocation sequences get smaller and faster to load, memory buffer will also last (a bit) longer.
- Do not sent prepare statement parameters
- We currently capture the concrete set methods that are applied to fill a prepared statement. In addition we sent this information to the UI in order to display them in the invocation sequence. Never ever will anyone have a look at the concrete methods. Everyone is only interested in the correctly built up SQL query
- Improvement: Way less data needs to be sent and parameter replacement could in fact be used for more than a one-user scenario. Sure the overhead on the agent is still there, but the flood of data is reduced
Provide more information for batching
At IVV SQL batching is quite heavily used. Currently inspectIT only shows the query that got batched and the total time. But we are missing important factors here:
- Number of queries that were executed within on batch
- Average execution time of the query (that is total / count)
- Integration of the corrected average time in the SQL views
- In case of parameter replacement we should be able to capture the data for all queries and not just for one.
Provide information about data reading (ResultSet)
We are currently only measuring the time a query takes to execute. After a query is executed, the application usually reads the data. Technically this means that the ResultSet is parsed (ResultSet.next()) and additional time is spent in that as well. For a number of queries the time spent in ResultSet operations is way higher than the time spent to execute the query. So what would we need:
- Instrumentation of ResultSet.next operations, but ResultSet.next is really heavily used in all applications. We really impose a high overhead here because the method is quite fast and often executed.
- We should not sent all information back to the client (as there is a high number of executions), thus we must wrap the resultset operations in one dataobject and send this to the client (even when within an invocation sequence). The final representation would then show how much time is spent in ResultSet operations as one element in the invocation sequence.
- This is highly useful for applications like we have at IVV