-1

In a ResultSet from either Oracle,MySQl or MSSQL, how can you make the resultset for loop quicker? 

    while (rs.next()) {

        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
            rs.getString(i)

        }

    }

This works great as long as you don't have to many columns, but as soon as you have for example 90 columns and 100 000 rows, then this for loops goes trough 100 000 rows 90 times.

miniHessel
  • 778
  • 4
  • 15
  • 39
  • what are you planning to do? – EpicPandaForce Nov 21 '14 at 15:42
  • have you looked into `jdbc paging` on google? it helps when having to deal with loads of data. or use a cursor when reading – Andrei Sfat Nov 21 '14 at 15:44
  • 3
    Well yeah, you're telling it to. If you need to get all the strings for all the columns for all the rows that's pretty much your option. You could try caching the column count, but I suspect that won't help much--you're still iterating 9M times. What do you actually *need* to do? – Dave Newton Nov 21 '14 at 15:44
  • 1 page is not enough to display all 100 000 records so its better to use pagination,and you will get better performance – Pravin Nov 21 '14 at 15:44
  • You can query smaller amounts of data iteratively. That might speed things up a bit instead of pulling down nine million datapoints and hoping for the best. – DejaVuSansMono Nov 21 '14 at 15:45
  • I basically need all the rows shown on one page. So guess there aint much alternatives then. – miniHessel Nov 21 '14 at 15:45
  • Depends on what you want to do with the results. You could skip date/time and numerical fields if you are doing a text searching. If just collecting, some databses have bulk output, CSV dump or whatever. Non-portable though. – Joop Eggen Nov 21 '14 at 15:50
  • Playing with row prefetching settings is likely the best you can do to tweak performance in the scenario sketched here. http://stackoverflow.com/questions/15074037/set-a-default-row-prefetch-in-sql-server-using-jdbc-driver . Just to note: the above code WILL fetch data iteratively, its not like the JDBC driver is going to tank all 100000 rows to the client in one go. That's what the whole JDBC iterating structure is all about - to be able to "window" through the data. – Gimby Nov 21 '14 at 15:55

1 Answers1

2

One thing to try would be to just get the column count once:

int colCount = rs.getMetaData().getColumnCount();
while (rs.next()) {
    for (int i = 0; i < colCount; i++) {
        rs.getString(i)
    }
}
BarrySW19
  • 3,759
  • 12
  • 26