I recently had a similar problem. Users wanted to export a highly normalized structure to a csv file for munging in a spreadsheet. They were limited to 200 ro

I recently had a similar problem. Users wanted to export a highly normalized structure to a csv file for munging in a spreadsheet. They were limited to 200 rows, because a thousand rows would bring everything to its knees. My first cut was to move from Hibernate to pure SQL, but if I tried 1000 rows it went away for a long time. I dont know how long because I killed it after 15 minutes. The real killers seemed to be lef outer joins. I then split the query into several temporary tables. The first table contained all the rows that I was interested in. The other tables replaced the left outer joins and nasty beasts like group concatenates and queries that turned aggregate results into separate fields. The temp tables were used to update the first table. Runtime went from a significant part of an hour to under 5 seconds.