How to improve performance of the view with 12 joins

Question

How to improve performance of the view with 12 joins

rshm35

SSC-Addicted

Points: 496
More actions
November 6, 2009 at 3:22 pm

#216464

Hi,
I have a view which selects the data with 12 joins in it...
can you give me suggestions how can i optimize this view....
Thanks

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Based on your post? No. Not enough information to be of real help. It would help to see the DDL for the view and the underlying tables. May help further if you provided sample data for the underlying tables as well.

Please read the first two articles I reference in my signature block below. If you follow the instructions in those articles, I'm sure you will get much better answers as well as tested code.

Paul White SSC Guru Points: 150468 More actions · Answer 2

As a general point, if you find yourself regularly performing 12 joins to get at the data you need, the database design may be too normalized. Consider de-normalizing the data appropriately. This might involve updating summary data using triggers, or it might involve creating an indexed (materialized) view.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

rshm35 (11/6/2009)
Hi,
I have a view which selects the data with 12 joins in it...
can you give me suggestions how can i optimize this view....
Thanks

Yes...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 4

Rather than denormalise (assuming the db is correctly normalised in the first place) and potentially throwing the baby out with the bathwater, if you have complex reporting needs you would almost always (IMHO) be better off setting up a dedicated dimensional reporting database for your reporting requirements.

Reporting and OLTP can frequently have totally opposed requirements, and rather than compromise your whole OLTP database for the sake of a handful of reports - you are in most cases better off setting up an appropriate reporting framework.

Do a quick google on Dimensional modelling, Kimball, and ETL - extract transform and load. SSIS can be an excellent tool for transferring data from 'live' and transforming the data into a fomrat optimised for your reporting needs.

There's plenty of help and advise out there - and on here - on this approach.

Paul White SSC Guru Points: 150468 More actions · Answer 5

I doubt if a 'handful of reports' would justify the considerable complexity and expense of setting up a data warehouse 🙂

A small amount of appropriate denormalization is a perfectly sound practical approach, in the majority of cases. Indexed views are a powerful tool when used correctly, especially in Enterprise Edition.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

I agree... The occasional properly written indexed view or Divide'n'Conquer stored procedure is frequently the only denormalization needed and it can be done quite effeciently without setting up a data warehouse or even getting SSIS involved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

12 tables in a join isn't exactly going to break the system assuming the tables are well indexed and the query is properly written. I wouldn't even start sweating that I was pulling too much together until I hit 20 or more tables in a join (again, depending on how many rows we're talking about, how the query is structured, the design of the tables themselves, etc.).

Do you at least have an execution plan? That would help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Grant Fritchey (11/9/2009)
12 tables in a join isn't exactly going to break the system assuming the tables are well indexed and the query is properly written. I wouldn't even start sweating that I was pulling too much together until I hit 20 or more tables in a join (again, depending on how many rows we're talking about, how the query is structured, the design of the tables themselves, etc.).
Do you at least have an execution plan? That would help.

Gonna have to take exception to that Grant, for several reasons:

1) I have NEVER been to a client that was "well indexed".

2) A large percentage of queries are not "properly written", especially the complex ones.

3) The more stuff you join the more a) effort query optimization takes and b) statistical variances are likely to cause bad plans.

I will note that I usually only get called in for poorly-performing clients so my 'view of the world' may well be skewed. Much like when I asked at the PASS Insider session about trace replay and about a quarter to a third of the room raised their hand yet I had never in a dozen years run into a single client that used it. 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 9

TheSQLGuru (11/9/2009)
Grant Fritchey (11/9/2009)
12 tables in a join isn't exactly going to break the system assuming the tables are well indexed and the query is properly written. I wouldn't even start sweating that I was pulling too much together until I hit 20 or more tables in a join (again, depending on how many rows we're talking about, how the query is structured, the design of the tables themselves, etc.).
Do you at least have an execution plan? That would help.
Gonna have to take exception to that Grant, for several reasons:
1) I have NEVER been to a client that was "well indexed".
2) A large percentage of queries are not "properly written", especially the complex ones.
3) The more stuff you join the more a) effort query optimization takes and b) statistical variances are likely to cause bad plans.
I will note that I usually only get called in for poorly-performing clients so my 'view of the world' may well be skewed. Much like when I asked at the PASS Insider session about trace replay and about a quarter to a third of the room raised their hand yet I had never in a dozen years run into a single client that used it. 😀

Yeah, no worries there. I think my POV is skewed too. I had a system that was running 86 table joins and we "fixed" it at 40 table joins (which can perform quite well after tons of hard work). Having had to deal with that (stupid) high end, I'm a lot more lenient for joins that are "only" 12 tables.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

. . . I had a system that was running 86 table joins . . .

I wonder if that is some sort of record? Worse than I have seen by a LONG shot! I see something like that and hear this big kaCHIINNNNGGGG in the background. 😀 I would NOT want to see that as a salaried employee however - job security just isn't worth that much pain! LOL

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

12 Tables in a join is a handful. However, I have seen it done many times over at several clients. A little query tuning can go a long way. In some cases though, a little denormalization would be a better fit.

We would need to see queries and execution plans to be better help.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 12

Grant,

86? :w00t:

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

rshm35 SSC-Addicted Points: 496 More actions · Answer 13

The query has 12 joins in that 2 views are there...

i can not use indexed view..help me out..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

rshm35 (11/9/2009)
The query has 12 joins in that 2 views are there...
i can not use indexed view..help me out..

We need more information to help. Some great comments have been made in this thread for your issue. A join on 12 tables can be done (even with 2 of them being views). In order to better help, we need to know execution plans, queries, sample table structure, sample data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events