Alternate to Joins

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Alternate to Joins

Alternate to Joins

Rate Topic

Display Mode

Topic Options

Author

Message

Compassionate

Posted Tuesday, February 05, 2013 6:43 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, March 17, 2013 5:52 AM
Points: 11, Visits: 28

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?

Post #1416198

Dave Ballantyne

Posted Wednesday, February 06, 2013 12:22 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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.

Clear Sky SQL
My Blog
Kent user group

Post #1416275

Kingston Dhasian

Posted Wednesday, February 06, 2013 12:30 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 2,230, Visits: 4,197

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.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1416279

foxxo

Posted Wednesday, February 06, 2013 12:36 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 5:01 AM
Points: 707, Visits: 1,049

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.

Post #1416284

Bhuvnesh

Posted Wednesday, February 06, 2013 12:57 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

Many Joins means "poor database design" & Poor Joins means "poor indexing" BigGrin

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1416291

Eugene Elutin

Posted Wednesday, February 06, 2013 3:40 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:00 AM
Points: 2,543, Visits: 4,384

Bhuvnesh (2/6/2013)

Many Joins means "poor database design"...

Really? Did you ever worked with data warehouses?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1416377

Dave Ballantyne

Posted Wednesday, February 06, 2013 4:46 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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

Clear Sky SQL
My Blog
Kent user group

Post #1416409

mtassin

Posted Wednesday, February 06, 2013 7:01 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 3,229, Visits: 64,271

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.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems

Post #1416490

mmartin1

Posted Friday, February 08, 2013 3:34 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 193, Visits: 642

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.

Post #1417931

« Prev Topic | Next Topic »

Permissions