November 6, 2009 at 3:22 pm
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
November 6, 2009 at 3:37 pm
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.
November 7, 2009 at 12:33 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 7, 2009 at 8:55 pm
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
Change is inevitable... Change for the better is not.
November 8, 2009 at 7:20 am
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.
November 8, 2009 at 1:44 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2009 at 5:08 pm
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
Change is inevitable... Change for the better is not.
November 9, 2009 at 8:03 am
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
November 9, 2009 at 9:19 am
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
November 9, 2009 at 9:33 am
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
November 9, 2009 at 9:51 am
. . . 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
November 9, 2009 at 12:39 pm
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
November 9, 2009 at 3:16 pm
November 9, 2009 at 3:22 pm
The query has 12 joins in that 2 views are there...
i can not use indexed view..help me out..
November 9, 2009 at 3:49 pm
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
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply