SQL EXPERTS... performance tune this query???

[lloydsj]

Hi, I'm trying to write the fastest sql i can and need some idea's. Hopefully someone can help. Data/tables is as below and can't be changed.

The tables in question both have MILLIONS of rows... and the number of attributes i'm pulling out will be more like 20 (rather than the 3 i've mocked up here). Also the tables (table1 and table2 ) will be getting writen to continuously, if that makes any difference.

table1

id	desc	detail1		detail2		detail3
######################################################
1	banana	jsdja		nbsdnbs		kjuif
2	apple	fruit		round		tree
3	orange	etc		etc		and so on


table2

id	table1_id	attribute	attribute_value
#################################################
1	1		blah		blah
2	2		colour		red
3	2		width		11
4	2		length		11
5	3		etc		etc
6	4		etc		and so on

What I'm after getting is as below...

desc	detail1	detail2	detail3	colour	width	length
######################################################
apple	fruit	round	tree	red	11	11

I've got to it with the sql below... but is there a better (i.e. faster!) way to do it???

select t1.*, t2_1.attribute_value colour, t2_2.attribute_value width, t2_3.attribute_value length
from table1 t1,
	 table2 t2_1,
	 table2 t2_2,
	 table2 t2_3
where t1.desc = 'apple'
and t1.id = t2_1.table1_id
and t2_1.attribute = 'colour'
and t1.id = t2_2.table1_id
and t2_2.attribute = 'width'
and t1.id = t2_3.table1_id
and t2_3.attribute = 'length';

hope that all makes sense.

Cheers.

 

[Dj_Jestar]

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

[Pho]

As above.

SELECT
t1.desc,t1.detail1,t1.detail2,t1.detail3,t2.attribute,t2.attribute_value
FROM table1 t1
INNER JOIN table2 t2 ON t2.table1_id=t1.id

 

[SimonCHere]

But that's not what he wants.
He is trying to change rows (in table2) into columns.

If you are using SLQ2005 there is a operator called PIVOT which does exactly what you want.

or for 2000
http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm

 

[Dj_Jestar]

No, he's not. He is using t1.id = t2.id as a foreign key (though not sure why it is referenced 3 times as t2_1-3?)

 

[SimonCHere]

No, he's not. He is using t1.id = t2.id as a foreign key (though not sure why it is referenced 3 times as t2_1-3?)

He's referencing it 3 times because he wants them in columns.

desc	detail1	detail2	detail3	colour	width	length
######################################################
apple	fruit	round	tree	red	11	11

Your way will give him something like

apple	fruit	         round	tree	        color   red
apple	fruit	         round	tree	        width  11
apple	fruit	         round	tree	        length 11

You could do it something like:

select t1.*, att_color.attribute_value colour, att_width.attribute_value width, att_length.attribute_value length
from table1 t1 
Left outer join (select table1_id, attribute_value from table2 where attribute = 'color') att_color
  on att_color.table1_id = t1.id
Left outer join (select table1_id, attribute_value from table2 where attribute = 'width') att_width
  on att_color.table1_id = t1.id
Left outer join (select table1_id, attribute_value from table2 where attribute = 'length') att_length
  on att_color.table1_id = t1.id
where t1.desc = 'apple';

 

[lloydsj]

Hi,

Yes i think SimonCHere is on the same wave length as what i want. I'm just checking out PIVOT and the last example using LEFT OUTER joins. cheers for the idea's so far.

Any suggestion as to which will perform the best? (between mine and SimonCHere's 2 ideas).

BTW. I'm using oracle 11g (which also has PIVOT)

 

[Dj_Jestar]

No it wouldn't?!

do you know the meaning of a foreign key? (I mistakenly put t2.id where I meant t2.table1_id in my previous post.)

 

[Dj_Jestar]

Hi,

Yes i think SimonCHere is on the same wave length as what i want. I'm just checking out PIVOT and the last example using LEFT OUTER joins. cheers for the idea's so far.

Any suggestion as to which will perform the best? (between mine and SimonCHere's 2 ideas).

BTW. I'm using oracle 11g (which also has PIVOT)

Pho's example.

 

[rpstewart]

Pho's example gives the wrong results though.

The OP wants one row per item, not the one row per item per attribute that Pho's example gives.

 

[SimonCHere]

Sorry some copy/paste errors in the above.

select t1.*, att_color.attribute_value colour, att_width.attribute_value width, att_length.attribute_value length, att_etc.attribute_value etc
from table1 t1 
left join (select table1_id, attribute_value from table2 where attribute = 'colour') att_color
  on att_color.table1_id = t1.id
left join (select table1_id, attribute_value from table2 where attribute = 'width') att_width
  on att_width.table1_id = t1.id
left join (select table1_id, attribute_value from table2 where attribute = 'length') att_length
  on att_length.table1_id = t1.id
left join (select table1_id, attribute_value from table2 where attribute = 'etc') att_etc
  on att_etc.table1_id = t1.id
where t1.[desc] = 'apple'
or t1.[desc]='orange'

My code gives:

2	apple	fruit	round	tree	red	11	11	NULL
3	orange	etc	etc	and so on	NULL	NULL	NULL	etc

Pho's code gives:

bannana	jsdja	ndsnbs	kjuif	blah	blah
apple	fruit	round	tree	colour	red
apple	fruit	round	tree	width	11
apple	fruit	round	tree	length	11
orange	etc	etc	and so on	etc	etc

Oh and DJ_Jestar I do know the meaning of a foreign key.

 

[Dj_Jestar]

Nested selects for performance? :/

 

[SimonCHere]

Nested selects for performance? :/

As opposed to the wrong data being returned?

Anyway, to answer your question, nope and personally I wouldn't. The way I'd do it would be:

SELECT
t1.id, t1.[desc],t1.detail1,t1.detail2, t1.detail3,
min(CASE attribute WHEN 'colour' THEN attribute_value END) AS 'colour',
min(CASE attribute WHEN 'width' THEN attribute_value END) AS 'width',
min(CASE attribute WHEN 'length' THEN attribute_value END) AS 'length',
min(CASE attribute WHEN 'etc' THEN attribute_value END) AS 'etc'
FROM table1 t1 
inner join Table2 t2
on t1.id = t2.table1_id
WHERE t1.[desc]='apple'
group by t1.id, t1.[desc],t1.detail1,t1.detail2, t1.detail3

which I linked to in my original reply.

Anyway. ep's from the OPs code next to mine give:
OP's code:
Query cost (relative to the batch): 42.44%
The one you seem not to like:
Query cost (relative to the batch): 39.46%
The one I've just posted:
Query cost (relative to the batch): 18.11%

 

[lloydsj]

Cheers SimonCHere, that last example is working spot on. Thanks

Cheers everyone else for your efforts too. Obviously, if anyone can think of something even better than im still interested (although i think SimonCHere has pretty much sorted it)