in function using PreparedStatement create performance problem or not?

[ahmet oguz]

Hi,

I want to ask about PreparedStatement performance. Which one is more efficient.
Not: In my Sample class foo and func function is used many many times in my project. These are the most called function in my project so its performans is very important.

1)
class Sample{
private Connection con;
private PreparedStatement pre;
private String sql_foo;
private String sql_func;
puclic Sample(){
// set the connection object;
}
public Arraylist foo(){
pre = con.preparedStatement(sql);
Arraylist list = new Arraylist;
pre.setString();...
ResultSet rs = pre.executeQuery();
....
...
return list;
}
}
public Arraylist func(){
pre = con.preparedStatement(sql_func);
Arraylist list = new Arraylist;
pre.setString();...
ResultSet rs = pre.executeQuery();
....
...
return list;
}
}

2)
class Sample{
private Connection con;
private PreparedStatement pre_foo;
private PreparedStatement pre_func;
private String sql_foo;
private String sql_func;
public Sample(){
//set the con object
//SET THE pre_foo and pre_func object with appropriate string(sql_foo or sql_func)
}
public ArrayList foo(){
Arraylist list = new ArrayList();
pre_foo.setString();
resultSet = ...
return list;
}

public ArrayList func(){
Arraylist list = new ArrayList();
pre_func.setString();
resultSet = ...
return list;
}
******************************************

My question is settting preparedStatement object in a most used function
is bad way? In both foo and func function is this bad?
pre = con.preparedStatement(appropriateString);

Thanks,

[Paul Sturrock]

I think you are asking whether it is a good thing to re-use a PreparedStatement - to which the answer is "yes". That is largely what they are for.

[ahmet oguz]

ok, but which one is effective? if we writes in function everytime
pre = con.prepareStatement(sql);
is this the right usage or not? if not, must i make this not in function, out of function and in function only set the parameters?

[Jeanne Boyarsky]

Ahmet,
This is the correct usage. The JDBC driver only cares that the SQL is exactly the same. It does not care how many times you call "conn.preparedStatement()" or from where. With the exception if you use too many different SQL statements, you will run out of cache and it will have to re-prepare some.

In your example, the prepared statements will be cached. You don't have to do anything extra explicitly.