Quote:
I don't understand why in such a supposedly high performance framework it's so difficult to iterate over a 'small' 2Gb database ?
With native sql I do it in under 2 mins with hibernate it all breaks down and lasts for hours
It all depends on what you want to do with this 2GB data. If you need to process it within Java, you have to load each of them out of the database, if you have to do a bulk update, use bulk update statements and don't try to load the data.
The problem is that you're copying the 2GB data from the database into your JVM, you might want to avoid that, or if you have good reasons to do it then it will be slower than just processing it in the database.
You can still get decent performance using scrollable results with tuned batch sizes, but it won't be on par of a single SQL statement on the database, that would be comparing apples to pears.