I always learned that the more select statements you have, the slower your app will work.
Now I have done a few tests on the project I am currently working on and it amazed me that 100 selects runs almost twice (and probably scales higher as it grows) as fast as a big join query.
This happens when there are alot of collections in the "tree" of the query.

Is this normal?
Should I try and optimize the queries with join and selects? (for example: only use joins for 1 sub collection, do not join multiple collections in one query)
I never had this problem since I never really had to use big queries in the past, but now that I do I see it as a serious problem (loading a full page with 2 seconds or 1 second differs alot).

Anyone got input on this? As far as I see it there is no "standard" fastest way to retrieve data but you must optimise with each query?

thanks

That is absolutely right. Usually it's faster to do one big statement instead of many smaller ones - but a join can be really slow if you don't optimize it. DBMS are usually pretty good at optimizing, but sometimes you need to give them the correct hint.

This question is really a SQL question. I suggest to dig into your DBMS documentation (Oracle has a huge documentation) or search for SQL optimizations in Google - you will find tons of documents.

Yes, although I think it is related to Hibernate though.
I've always written SQL queries but this project I started on Hibernate.
I was surprised to see that there were so many select queries for something very basic, one time it was faster then a large join, the other time it was way slower.

With the knowledge that Hibernate was always meant for people that don't really "know" how to communicate with a database was printed in my mind, it seems I took this a bit to extreme by thinking everything can be done by just a simple query.

I know better now, optimizing it is kind of fun too.