Hi, Currently I am using an embedded database with my Java application. This database has been normalised into 9 tables with each about 3-5 columns except for 1 "main table". This main table holds 8 foreign keys to the other tables. The primary keys of the other tables are mostly of datatype "Varchar(3)"
What I wish to retrieve is all this data with 1 query (this would indeed mean a resultset with about 45 columns). However, writing this query will mean I have to join 8 tables. This is where my question comes in. For testing purposes all tables have only 1 or 2 rows of data. When I execute the query it takes about 800ms to execute, which seems very long. I have checked the "explain" and the query isnt tablescanning. When I remove 1 join, doesn't matter which join, time is reduced to 250-300 ms.. and when I remove another one its about 0-10ms. I have also tried to make a View of the query and querying the View (with all joins) reduces time to 0-10ms but sporadically (about every 3 times I execute the query in my browser) it's 1500ms. So im not quite sure if this solves my problem. If im correct, a View executes the same query but im not certain about that. What I wish to know is, am I doing something wrong? is having 8 joins too intensive? and what would be a good way to find a solution and join all the data in 1 resultset? As it seems now, I would almost think that normalising everything has only given me a performance penalty. Furthermore, if there's any data needed with my question I'll gladly provide it. Thank in advance --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
