New inspection for finding non-use of prepared statements

Questions about YourKit Java Profiler
Post Reply
lars t
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am

New inspection for finding non-use of prepared statements

Post by lars t »

Profiling legacy and 3rd party applications I often confronted with the use SQL statements which aren't use prepared statements to reuse the access plans for the statement and makes the program execute more efficiently.

http://www.theserverside.com/news/13652 ... m-properly

In the "J2EE Statistics" view I see a lot of SQLs with mostly a execution count of one. Hence there isn't a average execution time available.

It would be great if a new CPU inspection finds these issues, hence the user can improve the code.
Anton Katilin
Posts: 6172
Joined: Wed Aug 11, 2004 8:37 am

Re: New inspection for finding non-use of prepared statement

Post by Anton Katilin »

Hello Lars

Thank you for the suggestion. We've added corresponding feature request.

Best regards,
Anton
Anton Katilin
Posts: 6172
Joined: Wed Aug 11, 2004 8:37 am

Re: New inspection for finding non-use of prepared statement

Post by Anton Katilin »

This inspection will be added in the next EAP build 33: https://www.yourkit.com/eap
lars t
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am

Re: New inspection for finding non-use of prepared statement

Post by lars t »

Do you mean the inspection "Statements opened but not accessed"?
Anton Katilin
Posts: 6172
Joined: Wed Aug 11, 2004 8:37 am

Re: New inspection for finding non-use of prepared statement

Post by Anton Katilin »

Yes.
lars t
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am

Re: New inspection for finding non-use of prepared statement

Post by lars t »

The TheServerSide is about the wrong usage of PreparedStatement or Statement by using SQLs like

select a,b from t where c = 4711
select a,b from t where c = 42

instead of

select a,b from t where c = ?

The wrong statements are used and accessed. Will the new inspection find the wrong usage?
Anton Katilin
Posts: 6172
Joined: Wed Aug 11, 2004 8:37 am

Re: New inspection for finding non-use of prepared statement

Post by Anton Katilin »

The added inspection works with both prepared and non prepared statements. It finds those statements for which no calls (queries) have been made.

Indeed, this is not the same as the originally proposed inspection. Sorry for not being correct with the notification.

Nevertheless, we can add the "c= 42 instead of c = ?" inspection as well.

How should it work? What heuristics should it employ? Should it find multiple non-prepared statements with different parameter values?
lars t
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am

Re: New inspection for finding non-use of prepared statement

Post by lars t »

> How should it work? What heuristics should it employ? Should it find multiple non-prepared statements with different parameter values?

Find executions of
java.sql.Connection.prepareStatement
java.sql.Statement.execute
java.sql.Statement.executeQuery
java.sql.Statement.executeUpdate
...
at the same location, but with a different SQL parameter.
Anton Katilin
Posts: 6172
Joined: Wed Aug 11, 2004 8:37 am

Re: New inspection for finding non-use of prepared statement

Post by Anton Katilin »

Could you please clarify what you mean with "the same location". Do you mean the same stack trace?
lars t
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am

Re: New inspection for finding non-use of prepared statement

Post by lars t »

No, I mean the same class which invokes these methods. Stack traces can be different, but it's important that the class uses the same constant SQL string. And I mean class and not it's instances (objects).
Anton Katilin
Posts: 6172
Joined: Wed Aug 11, 2004 8:37 am

Re: New inspection for finding non-use of prepared statement

Post by Anton Katilin »

Could you please try the new inspection "Queries can be prepared" introduced in the latest EAP build 34:
https://www.yourkit.com/eap
Post Reply