SQL Pagination optimization in Java??
Posted on 2011-03-03
Hello to all experts and laymen alike!
My question deals with optimization of pagination using Java.
We are trying to write a Java database comparison tool that compares very large query results in two databases for regression testing purposes. Query results are often 20 million records large!
Right now, we are working with MySQL with migration to Oracle in mind.
We have tried to use the LIMIT/OFFSET approach built into MySQL in a syntactically simple manner, but have run into performance issues when trying to get the pages representing the millionth plus records.
What we have done is Implement a chunk approach using specific 'where' clauses that limit the actual query results, based on an auto incremented column that we know is there.
The problem is that now we need to compare generic DBs and queries without being certain of it's structure, or contents.
We have also looked into JPA and Hibernate and would love to hear whether that could serve us here.
THANK YOU SO MUCH!