Measure sql execution time in a Java Application

问题:

Is there a easy way to measure execution time of all sql statements that are executed by JDBC and print the result to the output?

Some may advise me to use AOP to do this but I'm trying to avoid this if possible. Is there another way?

回答1:

If you are not running the application in an application server that provides you a DataSource, you would find the log4jdbc project to be useful. The jdbc.sqltiming logger provided by the project will allow you to record the execution of the SQL statements executed.

You could use this in an application that relies on DataSources, by wrapping the connection returned from the DataSource in a ConnectionSpy object. This would require changes in your codebase.

There are of course, other options available the time of writing this:

• the P6Spy project that can still be used in most application servers. Although certainly dated (and considered abandoned by some), it is by no means obsolete.
• the JAMon API allows for monitoring of execution time for SQL commands executed. This would require using the JAMon API to monitor the connection.

回答2:

Ironically, when viewing your question the advert on the right was for the Appdynamics Lite Java Performance tool.

回答3:

We use three different ways to show execution time.

1. We use built in Sql Server tools to show execution time/frequency/io/etc. I don't do this myself so I don't know what the exact tool is.

2. We use AviCode to track execution time over a defined limit.

3. We run all of our sql calls through a library that automatically metrics all sql calls.

We use these different methods because they each provide a different view of the execution. When there is a problem we look at all of them to make sure they agree.

Do you have something like this available in your environment?

回答4:

Check this out. They mention using Sql Recorder with JDBC. It might work for you.

Anything better than P6Spy?

回答5:

If you want to check execution time take by your java application then print date then execute the statement and again print the date you can see the diffrence. Like

System.out.println(new Date()); stmt.executeUpdate(); System.out.println(new Date());

If you want to see the time taken by SQL server, execute query in SQL Query analyzer, on right hand side below corner of the window you will find the time taken to execute the query.

Thanks