I have the following fragment of code:
public static void main(String[] args) throws SQLException {
Connection c = getConnection();
long time = System.currentTimeMillis();
PreparedStatement ps = c.prepareStatement(sqlQuery);
int index = 1;
for (String param: parameters) {
if (isInt(param)) {
//ps.setInt(index++, Integer.parseInt(param));
ps.setObject(index++, Integer.parseInt(param), java.sql.Types.NUMERIC , 0);
} else {
ps.setString(index++, param);
}
}
displayResult(ps.executeQuery());
System.out.println("It took " + (System.currentTimeMillis()-time) + ".");
time = System.currentTimeMillis();
Statement s = c.createStatement();
ResultSet rs = s.executeQuery(expandParametersInStatement(sqlQuery, parameters));
displayResult(rs);
System.out.println("It took " + (System.currentTimeMillis()-time) + ".");
}
The query executed with a PreparedStatement is slower by a factor of 4000. Compared to the Statement approach. They give the same result and the order of execution makes no huge difference.
Using the setObject() instead of setInt() makes the PreparedStatement as fast as the Statement.
What is the difference? The cast in the Database cannot be that expensive? The data type in the database is a NUMBER(10). I guess it is a matter of the indeces which are used. However, I cannot replicate this in the SQL Developer with CAST(x AS INTEGER)?
Thanks.
The statement is:
private static String sqlQuery = "SELECT sum(value) " +
"FROM a monat, " +
" n jahr, " +
" kunde kunde " +
"WHERE monat.kunde_nr IN " +
" (SELECT DISTINCT kunde.kunde_nr " +
" FROM MASKE_4_KUNDEN kunde " +
" WHERE kunde.firma_nr = ? " +
" AND kunde.verkaufsbereich_nr = ? " +
" AND kunde.vertriebsbereich_nr BETWEEN (CASE WHEN ? <> -1 THEN ? ELSE -9999999999 END) AND (CASE WHEN ? <> -1 THEN ? ELSE 9999999999 END) " +
" AND kunde.vertreter_nr BETWEEN (CASE WHEN ? <> -1 THEN ? ELSE -9999999999 END) AND (CASE WHEN ? <> -1 THEN ? ELSE 9999999999 END)" +
" AND kunde.konzern_nr BETWEEN (CASE WHEN ? <> -1 THEN ? ELSE -9999999999 END) AND (CASE WHEN ? <> -1 THEN ? ELSE 9999999999 END) " +
" AND kunde.geschaeftsjahr = ? " +
" AND kunde.kunde_nr BETWEEN (CASE WHEN ? <> -1 THEN ? ELSE -9999999999 END) AND (CASE WHEN ? <> -1 THEN ? ELSE 9999999999 END))" +
" AND monat.firma_nr = ? " +
" AND monat.verkaufsbereich_nr = ? " +
" AND monat.jahr_nr = ? " +
" AND jahr.kunde_nr = monat.kunde_nr " +
" AND jahr.firma_nr = monat.firma_nr " +
" AND jahr.jahr_nr = monat.jahr_nr " +
" AND jahr.verkaufsbereich_nr = monat.verkaufsbereich_nr " +
" AND kunde.kunde_nr = monat.kunde_nr " +
" AND kunde.firma_nr = monat.firma_nr";
?
and compare that to the explain plan of the statment using literals. – a_horse_with_no_name Feb 1 '12 at 13:09