Fully featured low overhead profiler for Java EE and Java SE platforms.
Ultimate profiling and monitoring solution for Gradle, Maven, Ant, JUnit and TestNG.
Easy to use performance and memory profiler for .NET framework.

New inspection for finding non-use of prepared statements

Moderators: Vladimir Kondratyev, Anton Katilin

New inspection for finding non-use of prepared statements

Postby lars t » Wed Nov 17, 2010 9:40 pm

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.
lars t
 
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am
Location: Cologne

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

Postby Anton Katilin » Thu Nov 18, 2010 5:38 pm

Hello Lars

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

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

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

Postby Anton Katilin » Wed Dec 07, 2016 12:40 pm

This inspection will be added in the next EAP build 33: https://www.yourkit.com/eap
Anton Katilin
 
Posts: 5576
Joined: Wed Aug 11, 2004 8:37 am

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

Postby lars t » Wed Dec 07, 2016 9:42 pm

Do you mean the inspection "Statements opened but not accessed"?
lars t
 
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am
Location: Cologne

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

Postby Anton Katilin » Wed Dec 07, 2016 9:57 pm

Yes.
Anton Katilin
 
Posts: 5576
Joined: Wed Aug 11, 2004 8:37 am

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

Postby lars t » Wed Dec 07, 2016 10:16 pm

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?
lars t
 
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am
Location: Cologne

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

Postby Anton Katilin » Thu Dec 08, 2016 11:43 am

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?
Anton Katilin
 
Posts: 5576
Joined: Wed Aug 11, 2004 8:37 am

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

Postby lars t » Thu Dec 08, 2016 2:47 pm

> 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.
lars t
 
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am
Location: Cologne

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

Postby Anton Katilin » Thu Dec 08, 2016 3:19 pm

Could you please clarify what you mean with "the same location". Do you mean the same stack trace?
Anton Katilin
 
Posts: 5576
Joined: Wed Aug 11, 2004 8:37 am

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

Postby lars t » Thu Dec 08, 2016 9:45 pm

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).
lars t
 
Posts: 16
Joined: Fri Oct 05, 2007 10:36 am
Location: Cologne

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

Postby Anton Katilin » Tue Dec 13, 2016 3:36 pm

Could you please try the new inspection "Queries can be prepared" introduced in the latest EAP build 34:
https://www.yourkit.com/eap
Anton Katilin
 
Posts: 5576
Joined: Wed Aug 11, 2004 8:37 am


Return to Java Profiler

Who is online

Users browsing this forum: No registered users and 9 guests