November 9, 2009 at 3:55 pm
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
November 9, 2009 at 5:11 pm
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
November 9, 2009 at 5:13 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..
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
November 9, 2009 at 7:13 pm
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
November 10, 2009 at 1:58 am
TheSQLGuru (11/9/2009)
Yeah - this was your IMPROVED design, right?? LOL
LMAO!!! :laugh:
November 10, 2009 at 6:28 am
Paul White (11/10/2009)
TheSQLGuru (11/9/2009)
Yeah - this was your IMPROVED design, right?? LOLLMAO!!! :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
November 10, 2009 at 7:39 am
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
November 10, 2009 at 8:23 am
ORM - Obscene Relation Mangling?
November 10, 2009 at 2:30 pm
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 😀
November 10, 2009 at 11:47 pm
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"
November 11, 2009 at 12:39 am
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
November 11, 2009 at 12:48 am
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"
November 11, 2009 at 1:13 am
It's certainly plausible...;-)
And it does help to be psychic with a lot of these questions.
November 11, 2009 at 1:17 am
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 😀
November 11, 2009 at 2:07 am
CREATE VIEW dbo.CrystalBalls
WITH SCHEMABINDING
AS SELECT Egg FROM
(
VALUES
('Egg'), ('Egg'), ('Egg'), ('Egg'),
('Egg'), ('Egg'), ('Egg'), ('Egg'),
('Egg'), ('Egg'), ('Egg'), ('Egg')
) Eggs (Egg);
...count them...there's a dozen fresh free-range ones 😛
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply