February 5, 2013 at 6:43 pm
folks,
i have 10 to 20 tables to be joined to get required results but i heard joins on so many tables is not an ideal option. could you tell me right approach for this?
February 6, 2013 at 12:22 am
From a default point of view, this should present no problems.
First though you need to ensure that design of the tables is 'correct' and properly normalised.
Indexing and statistics will probably also be required.
Do not assume that doing things 'correct' is simple or easy.
I have heard many times the 'nosql' argument that joins are slow ( amongst a slew of other things), and that can be true but buying into that means making other compromises.
If joins are a problem for you, then an indexed views can be utilised to denormalise the data, obviously this will increase the required storage and slow down DML tasks as the data has to be updated twice.
February 6, 2013 at 12:30 am
Compassionate (2/5/2013)
folks,i have 10 to 20 tables to be joined to get required results but i heard joins on so many tables is not an ideal option. could you tell me right approach for this?
Well, if there is a need to join 10 to 20 tables, you don't have an option other than joining those tables
You can try to reduce the number of JOINS, but there is no alternative to JOINS
One more option that you can consider is De-Normalization, but this will not be easy and will require change in database design.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 6, 2013 at 12:36 am
Do you *really* need to join that many tables? Ie. are you developing an application that displays this information all at once, on one screen, with bits of data from 20 tables? Or are you writing a report, in which case you need a reporting database that wont matter if a query is running all that slowly.
February 6, 2013 at 12:57 am
Many Joins means "poor database design" & Poor Joins means "poor indexing" 😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 6, 2013 at 3:40 am
Bhuvnesh (2/6/2013)
Many Joins means "poor database design"...
Really? Did you ever worked with data warehouses?
February 6, 2013 at 4:46 am
Also "many joins" is different if you:
a join b
b join c
c join d
d join e
e join f
f join g
or
a join b
a join c
a join d
a join e
a join f
a join g
same number of joins, but completely different logically and physically
February 6, 2013 at 7:01 am
Eugene Elutin (2/6/2013)
Bhuvnesh (2/6/2013)
Many Joins means "poor database design"...Really? Did you ever worked with data warehouses?
Or for that matter with a properly normalized system.
Many or few joins is dependant on the data model used to develop the database, nothing more.
I've got a few stored procs with 10-15 joins for a production OLTP system, those Stored procs generally result in index seeks because my indexing was done meticulously between me and 3 developers and we came up with good indexing plans to cover 90% of our queries with as few indexes as possible.
Runs quite well with a few million records.
February 8, 2013 at 3:34 pm
Many joins would indicate to me proper 3rd form normalisation. You may want to transform the data using dimensional architecture to avoid these. But with proper indexing, many joins are not a cause for concern.
The database engine tuning advisor in profiler helps you find the right columns to index.
----------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply