What are my options for optimizing this Spring/Hibernate page?

I have a page that touches a lot of data in the database. It displays a lot of data, and also does a fair amount of calculations on it, which would be a really massive hassle to do inside of a group by query, or in a stored procedure. It's just so much easier when you have your domain model.

Anyway, as a result, Hibernate ends up making TONS of queries as it pulls out all of the data. It doesn't really matter if I get the data eagerly or lazily... all the queries tally up to about ~600-700ms.

The individual queries are very fast. They are not even 1 ms. But as you can see, this page makes a lot of queries for a table that lists of the results of a training course for 58 students:

0 ms  JDBC SELECT (MODULE)
0 ms  JDBC SELECT (TASK)
0 ms  JDBC SELECT (USER_ACCOUNT) // student #1
0 ms  JDBC SELECT (ACTIVE_TASK)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (USER_ACCOUNT)
0 ms  JDBC SELECT (ACTIVE_TASK)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (USER_ACCOUNT)
0 ms  JDBC SELECT (ACTIVE_TASK)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)

0 ms  JDBC SELECT (USER_ACCOUNT) // student #2
0 ms  JDBC SELECT (ACTIVE_TASK)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (QUESTION)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (KEYWORD)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_FILL_IN_THE_BLANK_ANSWER)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_FILL_IN_THE_BLANK_ANSWER)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RE开发者_如何学运维SULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)

0 ms  JDBC SELECT (USER_ACCOUNT) // student #3
....
0 ms  JDBC SELECT (USER_ACCOUNT) // student #58
0 ms  JDBC SELECT (ACTIVE_TASK)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (ACTIVE_QUESTION)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_LANGUAGE_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)
0 ms  JDBC SELECT (QUESTION_RESULT_TO_KEYWORD)

It is going to be very common that 30-60 students take a course, so 600-700ms is going to kill the server.

To give an idea as to the complexity, here's the 1-M relationships:

Module(1)->Task->Question->Keyword

ActiveModule(1)->ActiveTask->ActiveQuestion->QuestionResult->Keyword, LanguageKeyword

Also, there are other 1-M links too:

Module->ActiveModule

Task->ActiveTask

Question->ActiveQuestion

Basically, this is a really complex graph of data.

What are my options? The server this application is running on only has 1 GB of RAM, so caching the individual objects is pretty much out of the question. I don't know if getting Hibernate to issue less queries would even make much difference. There are just so many.

The schema for this app is very tree-like, and it's deep and complex. Unfortunately, all of this data is needed. The trees are basically all or nothing.

Use a view. That should optimize your joins and reduce the number of individual queries you have to do.

I think you could easily do this using one or two sql queries joining the

ActiveModule(1)->ActiveTask->ActiveQuestion->QuestionResult->Keyword, LanguageKeyword

You could even unload some of your calculations on the data base engine as well.

This sort of thing is trivial for any modern database, ignore hibernate and just pass through a raw SQL query.