Resultset.next() taking more time

[murugesan]

Hi,

While analyzing the performance for my application, I have found that
resultset.next() method taking more time to move cursor on first
time. Resultset has only 10 records and when control reach at while
(rs.next()) taking more time (14 sec) on first time (to reach first
record) and
after control get into the while loop its working fast.

is there any alternative method to iterate resultset other than next
()?

Environment: Wbsphere Application server 6.1

code look like:

ResultSet rs = null;
CallableStatement cstmt = null;

cstmt = dbConnection.prepareCall(sql);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.executeQuery();

rs = (ResultSet)cstmt.getObject(1);

while(rs.next()){
//Only 10 times iterating as expected.
}

pls advise me, why resultset.next() method taking more time to reach
first record.

Regards
Murugesan

[gangadhar allada]

I think it is not ns.next() that takes more time....

It is the callable statements execution time (more likely a DB procedure here as u are using callable statement).

Try to look into the DB procedure....

--
Thanks,

Gangadhar.

[murugesan]

Hi,

DB procedure returns resultset within 1 sec and I put sysout above and
below while(rs.next()), this is only line taking more time, pls advise
me.

Regards
Murugesan

On Jul 14, 9:36 pm, gangadhar allada <gangadharall...@gmail.com>
wrote:

> I think it is not ns.next() that takes more time....
> It is the callable statements execution time (more likely a DB procedure
> here as u are using callable statement).
>
> Try to look into the DB procedure....
>

> Gangadhar.- Hide quoted text -
>
> - Show quoted text -

[gangadhar allada]

What is the driver type you are using?

 

Did you set any callableStatement.setFetchSize()? if not set it to some 10 or greater.

 

and see how it goes...

 

If the above still does not work for you...try to change the driver type and let us know.

--
Thanks,

Gangadhar.

[murugesan]

Hi,

I have tried to setFetchSize and changing driver type also, still
rs.next() taking more time. could you pls advise me.

Actually Resultset contains cursor value, ex:
cstmt.registerOutParameter(1, OracleTypes.CURSOR) and rs = (ResultSet)
cstmt.getObject(1);

is there any problem with resultset value because exactly moving
cursor from default position to first position (first record).

Regards
Murugesan

On Jul 14, 10:58 pm, gangadhar allada <gangadharall...@gmail.com>
wrote:

> What is the driver type you are using?
>
> Did you set any callableStatement.setFetchSize()? if not set it to some 10
> or greater.
>
> and see how it goes...
>
> If the above still does not work for you...try to change the driver type and
> let us know.
>

[gangadhar allada]

There is a dependecy on the driver type (and the fetch size as well).

 

Please look into the url...

 

http://www.coderanch.com/t/297038/JDBC/java/time-taken-rs-next

--
Thanks,

Gangadhar.

[murugesan]

Hi Gangs,

Yes, you are correct but I am already using type 4
(jdbc:oracle:thin:@), driver may ignore setFechSize as well that I am
fetching only 10 records and by default driver fetch 10 records well
so there is no point to set fetch size. for your information the
below code we use across the global, in my case its working very fast
on one module and its taking on another module. I am suspecting that
there might be some problem with resultset thats why its not moving
from default position to first position, pls advise me.

Regards
Murugesan


On Jul 15, 4:06 pm, gangadhar allada <gangadharall...@gmail.com>
wrote:

> There is a dependecy on the driver type (and the fetch size as well).
>
> Please look into the url...
>
> http://www.coderanch.com/t/297038/JDBC/java/time-taken-rs-next
>