| JDBC - Oracle Performance tuning [message #125555] |
Mon, 27 June 2005 10:08 |
aalessio
Messages: 3
Registered: May 2005
|
Junior Member |
|
|
Hello, I hope someone may help me.
I have a table, we call it CTable defined as follow
ID NUMBER PK
LOGTYPE NUMBER
RTYPE NUMBER
REF VARCHAR2
LOGTIME DATE
LOGTIMEMILLISEC NUMBER
COMP NUMBER
COMPM VARCHAR2
COMPN VARCHAR2
COMPS VARCHAR2
COMPTYPE NUMBER
CTabel may contain upto 3 million of records.
At this moment I am working with 500.000 records.
My client application (it has rights only to perform select statements) needs to display blocks of, we say, 1000 records.
What I do is given a OracleConnection conn:
OracleStatement stmt = conn.createStatement();
stmt.setRowPrefetch(1000);
String query = "select * from ( select rownum as rown, CTable.* from CTable WHERE ID >= 0 AND ID <= 413654 ) where rown >= 1 and rown <= 1000"
long t1 = = System.currentTimeMillis();
OracleResultSet rs = = (OracleResultSet)stmt.executeQuery(query);
long t2 = = System.currentTimeMillis();
while (rs.next ()){
do_work();
}
long t3 = = System.currentTimeMillis();
The time measurements are the following:
t2 - t1 = 94
t3 - t2 = 14546 (!?!?!?)
Initially I thought the time was spent for do_work(), but even if I query for only one record (i.e. query = "select * from ( select rownum as rown, CTable.* from CTable WHERE ID >= 0 AND ID <= 413654 ) where rown >= 1 and rown <= 2"), both the time keep to be nearly the same. The time reduces if the amount of records in CTables reduces.Why rs.next() takes so long time and how I may reduce it?
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
