Hello All,
I have a DAO layer to access the database
I have to join 10 -12 tables in order to display data in my jsp page.With performance's point of view, is it better to break the query to 2-5 simple queries with less number of joins or get the entire data in one query.
Help is greatly appreciated.
Thanks
*FLAG ON THE POST* Insufficient information
That depends on a great number of things. Not the least of which is how many columns and rows are we talking about joining? What database platform are we talking about (may or may not actually matter) What about indexes on the tables relevant to the joining and filtering criteria?
In basic, my practice is to use one query to get a list of the items to provide to the user. This list contains the primary key of the base database object (The thing that everything else joins to), and one or two descriptors that give something meaningful to the user. When the user selects an item from the list I go back to the database and get the required width of the data for that specific id. I've joined dozens of tables with hundreds of columns some of them containing large objects with no problems at all.
PS.
Create a view. And ****, I've never had to join more than 5 or 6 tables.
Having to join 10 to 12 tables seems excessive to me. Typically, you shouldn't need to join more than 4 or 5 tables. I suspect your database tables aren't normalized correctly (ie, overnormalized). For example, I suspect you have two tables that should have been made into one. Your friendly neighborhood DataBase Aministrator should review your database schema before any java code is written. He can also see what indexes and constraints should be made. If your database schema is not rock solid before coding, all java applications (including other people's proejects using that database) are going to be a mess to create, and to alter if the database schema is cleaned up afterward.
Personnally, I would break up the 12 joins into maybe say, 4 smaller ones so its easier for another programmer to follow what your getting from the database and make it easier for him to maintain your code. You can put all the queries in a transaction to ensure no table has been updated by others while extracting all your data. It may be a bit slower (or faster) than a 12 table join, but I believe ease of reading your code is more important.
Thank You All for your replies...
I'm planning to break up one query into several queries and write a stored procedure but not sure how to write a stored procedure in Java with several select statements, I'm comfortable writing but not sure how to store the retrieved information. Help is greatly appreciated.
Thanks...
