Sybase ASE : Pagination

Question

I want to implement a code which retrieves 5 million records from DB. But when I retrieve it, the connection is open for long which causes application to get stuck for long time. Also causes Out of memory issue.

I have used PreparedStatementCreator to set the fetch size which helps the data to retrieve fast. But for that particular period, the application is unusable for other users accessing the application as it gets stuck.

So I am trying to implement batch select, but Sybase ASE 16.3 does not support Offset, Limit, Start At, etc.

I am using jdbctemplate of spring.

Can you please help with a workaround for this issue.

Regards, Ashish M

Answer 1

Sybase recommended using temp table to do these pagination work --

eg.
create table #result (rid bigint identity, col1 int, col2 varchar...)
insert #result(col1,col2...) select ... from yourtable1, yourtable2 ... where ...

select * from #result where rid between 1 and 1000  -- page1
select * from #result where rid between 1001 and 2000 -- page2
...

Answer 2

Have you tried setting various isolation levels, like in an example below?

import java.sql.*;

Connection conn;
DatabaseMetaData dbmd;

<open connection>
dbmd = conn.getMetaData();

if (dbmd.supportsTransactionIsolationLevel(TRANSACTION_READ_UNCOMMITTED))
{ 
    conn.setTransactionIsolation(TRANSACTION_READ_UNCOMMITTED); 
}