How to improve performance of the view with 12 joins

Question

How to improve performance of the view with 12 joins

Viewing 15 posts - 16 through 30 (of 36 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

CirquedeSQLeil (11/9/2009)
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.

I will add that refactoring something as potentially complex as this is not really the ideal scenario for a forum post. You are really pushing the boundaries whereby a paid engagement is appropriate. I have spent DAYS tuning stuff like that before.

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Paul White (11/9/2009)
Grant,
86? :w00t:
Paul

Not my design, man, not my design.

"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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

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

You've got to answer the questions that we've been posting back. We really can't help you based on the information you've provided.

"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 4

Grant Fritchey (11/9/2009)
Paul White (11/9/2009)
Grant,
86? :w00t:
Paul
Not my design, man, not my design.

Yeah - this was your IMPROVED design, right?? LOL

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

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

TheSQLGuru (11/9/2009)
Yeah - this was your IMPROVED design, right?? LOL

LMAO!!! :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

Paul White (11/10/2009)
TheSQLGuru (11/9/2009)
Yeah - this was your IMPROVED design, right?? LOL
LMAO!!! :laugh:

You guys kidding? This app has had more examples of bad design than any I've seen. It was built originally with nothing by multi-statement table valued UDF's, then a consultant redesigned it with 86 table joins, then it was redesigned again by another consultant who put a query hint on one query (FAST 1) which was copied to every other query by the developers (along with several other hints including READ UNCOMITTED)... I love this app. It's a constant source of material for what NOT to do. Unfortunately it's been working and scaling well for about two years now (ever since we finally took it out of the hands of the developers & consultants). I've got a new monster lurking in the wings though, a DB built entirely with, by and for nHibernate. Should be another great source for material.

"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 7

I have said this before and will say it again here: SQL Server continually amazes me with how well it keeps on chugging despite INCREDIBLY bad design, code, setup, configuration, hardware, (cough) tuning, etc,etc!!!!

Oh, and ORM = $$$ from where I sit! I LOVE those things. hehehe Yet another example of what helps developers slap code together quickly is BAD for the database (and it doesn't matter WHICH RDBMS either).

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

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

Andrew Gothard-467944

Hall of Fame

Points: 3928

November 10, 2009 at 8:23 am

#1077300

ORM - Obscene Relation Mangling?

Paul White SSC Guru Points: 150467 More actions · Answer 9

Grant Fritchey (11/10/2009)
I've got a new monster lurking in the wings though, a DB built entirely with, by and for nHibernate. Should be another great source for material.

The mind boggles...let's hope it catches on so we can all retire after a couple of gravy-train consulting years fixing it all up 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

Back to OP...

I believe he has a table with 12 columns denoting the months of a year.

If this is the case, you can use UNPIVOT to normalise your data on-the-fly.

N 56°04'39.16"
E 12°55'05.25"

Paul White SSC Guru Points: 150467 More actions · Answer 11

Peso,

You muse be psychic to get that from the first post and this:

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

...that's all the information that we've had so far, unless I've missed something...?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 12

It's still a guess, but my experience (been a teacher for many years) says it's something to do with months, due to the number 12.

OP has not that many questions before and 12 "normalized" joins is nothing you get your hands on with that little experience.

However writing a query with 12 subselects, correlated subqueries or even JOINs is something an unexperienced person would do.

You know... 🙂

SELECT (SELECT SUM(Amount) FROM Table1 WHERE MONTH(Col1) = 1) AS January,

(SELECT SUM(Amount) FROM Table1 WHERE MONTH(Col1) = 2) AS February,

(SELECT SUM(Amount) FROM Table1 WHERE MONTH(Col1) = 3) AS March

...

Or similar approach with a 12 correlated subqueries or 12 joins to do the same thing.

N 56°04'39.16"
E 12°55'05.25"

Paul White SSC Guru Points: 150467 More actions · Answer 13

It's certainly plausible...;-)

And it does help to be psychic with a lot of these questions.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 14

Good guess Peso , though i think that it may be the 12 days of Christmas 😀

You know....

Create View 12Days

as

Select Qty from Bird where Species = 'Partridge' and InAnPearTree = 'Y'

union all

Select Qty from Bird where Species ='Dove' and SubSpecies = 'Turtle'

union all

Select Qty from Bird where Species = 'Hen' and SubSpecies = 'Speckled'

union all

Select Qty from Bird where Action = 'Calling'

union all

Select Qty from Jewellery where Type = 'Ring' and Metal='Gold'

etc......

How many times have we seen that 😀

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 15

CREATE VIEW dbo.CrystalBalls

WITH SCHEMABINDING

AS SELECT Egg FROM

(

VALUES

('Egg'), ('Egg'), ('Egg'), ('Egg'),

('Egg'), ('Egg'), ('Egg'), ('Egg')

) Eggs (Egg);

...count them...there's a dozen fresh free-range ones 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi