[DB2 6.1 JDBC] PreparedStatement incredibly slow compared to the same Statement
Ludovic Russo
wrote a little Java class which just gets a Connection to a DB2 6.1 FP7
database, gets a PreparedStatement, sets the variables and does an
executeQuery(). This is very slow and sets the db server CPU to 100%
during 20 seconds.
The same "hard-coded" query with a Statement take approximately 2
seconds.
Is there a problem with the db2java.zip ? (I've executed the
usejdbc2.bat).
What is strange is that this problem seems to be recent as we didn't have
such perf problems before but we (of course) don't know the why of this
loss of performance (I wouldn't be posting here !)
During the 20 seconds, if I do a "db2 get snapshot for application
agentid xxx" on the server, I can see that there are more than 4 million
lines read (which could explain the 20 seconds!) but why ??!!
If you can help me (again)...
Thanks,
-----------------------------------
Ludovic Russo
M.I.S. (Monaco Informatiques Systèmes)
lru...@mis.mc
bernd hohmann
> We encountered a performance problem in a Java-JDBC application. So I
> wrote a little Java class which just gets a Connection to a DB2 6.1 FP7
> database, gets a PreparedStatement, sets the variables and does an
> executeQuery(). This is very slow and sets the db server CPU to 100%
> during 20 seconds.
the 'problem' with a prepared statement is (i think), that it is
compiled more carefully than a 'on the fly' sql-statement.
you may try a runstats on the affected tables to speedup the
compilation.
bernd
--
class Mandel{public static void main(String a[]){float b,e,r,n,d;int h;
for(e=1.1f;e>-1.2;e-=.1){for(b=-2;b<1;b+=.04){r=n=0;for(h=127;r*r+n*n<4
&&--h>32;){d=r;r=r*r-n*n+b;n=2*d*n+e;}System.out.write(b>0.98?10:h);}}}}
Ludovic Russo
results...
I have more precisions. My PreparedStatement uses 6 parameters. I tested
it with 6, 5, 4, ..., 0 parameters. Here are the results :
6 : 22s (String, String, int, int, int, String)
5 : 23s (String, String, int, int, int )
4 : 23s (String, String, int, int)
3 : 1mn 17s (String, String, int)
2 : 1mn 17s (String, String)
1 : 1mn 16s (String)
0 : 701 ms
These are average time, calculated with several runs.
Thanks anyway,
-----------------------------------
Ludovic Russo
M.I.S. (Monaco Informatiques Systèmes)
lru...@mis.mc
In article <ubuznaauneqqvfxpns...@news.cis.dfn.de>,
hoh...@harddiskcafe.de says...
bernd hohmann
> I tested it with 6, 5, 4, ..., 0 parameters. Here are the results :
[..]
> 3 : 1mn 17s (String, String, int)
> 2 : 1mn 17s (String, String)
> 1 : 1mn 16s (String)
<ugh> i'm out of ideas .
Dirk Wollscheid
mentioned that the application snapshot said that the insert touches 4
million rows (Is 4 mill rows the whole table?)
I think this is not a JDBC problem, but more of a general SQL problem.
I don't know what can go wrong on insert so that it touches so many rows.
Could it be that you have unique constraints with no index?
pm3...@attglobal.net
The last time you asked for help on this, you were after parameter markers of
type timestamp.
Is this the post related to the BETWEEN stuff?
If yes,
How come you have String and int all over the place?
How do you calculate your time? Where did you get the numbers?
Is this alapsed run time including the time it takes java to print to the info?
How many rows are retrieved?
PM
Ludovic Russo
This post is not related to the BETWEEN keyword....
The time you see is the running time of my request depending on the
number of parameters I pass to my PreparedStatement...
-----------------------------------
Ludovic Russo
M.I.S. (Monaco Informatiques Systèmes)
lru...@mis.mc
In article <3B16B98B...@attglobal.net>, pm3...@attglobal.net
says...