Alfredo Osorio
Alfredo Osorio

Reputation: 11475

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?

Upvotes: 5

Views: 12032

Answers (5)

Vineet Reynolds
Vineet Reynolds

Reputation: 76729

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:

Upvotes: 2

DMS
DMS

Reputation: 818

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

Upvotes: 1

Scott Bruns
Scott Bruns

Reputation: 1981

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

Anything better than P6Spy?

Upvotes: 1

andyb
andyb

Reputation: 43823

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

Upvotes: 2

Scott Bruns
Scott Bruns

Reputation: 1981

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?

Upvotes: 1

Related Questions