Query Performance - Advice needed on Joining same table multiple times

Question

Query Performance - Advice needed on Joining same table multiple times

Michael T2

SSChasing Mays

Points: 614
More actions
October 2, 2012 at 3:18 pm

#263392

We have a product table which some of the products are kits which include other products. So i have 2 tables
Item table
(id)
(title)
and
Groups Table
(gleaderid)
(itemid)
(grank)
So to get all the items in a kit i have to a statement like
Select i.id,
'description' = 'Kit contains' + childitem1.title + childitem2.title + etc
from imitem i
inner JOIN groups ON groups.gLeaderID = I.ID AND groups.grank = 1
left JOIN groups groups2 ON groups2.gLeaderID = I.ID AND groups2.grank = 2
left JOIN groups groups3 ON groups3.gLeaderID = I.ID AND groups3.grank = 3
left JOIN groups groups4 ON groups4.gLeaderID = I.ID AND groups4.grank = 4
left JOIN groups groups5 ON groups5.gLeaderID = I.ID AND groups5.grank = 5
left JOIN groups groups6 ON groups6.gLeaderID = I.ID AND groups6.grank = 6
left JOIN groups groups7 ON groups7.gLeaderID = I.ID AND groups7.grank = 7
left JOIN groups groups8 ON groups8.gLeaderID = I.ID AND groups8.grank = 8
left JOIN groups groups9 ON groups9.gLeaderID = I.ID AND groups9.grank = 9
left JOIN groups groups10 ON groups10.gLeaderID = I.ID AND groups10.grank = 10
left JOIN groups groups11 ON groups11.gLeaderID = I.ID AND groups10.grank = 11
left JOIN groups groups12 ON groups12.gLeaderID = I.ID AND groups10.grank = 12
left JOIN groups groups13 ON groups13.gLeaderID = I.ID AND groups10.grank = 13
left JOIN groups groups14 ON groups14.gLeaderID = I.ID AND groups10.grank = 14
left JOIN groups groups15 ON groups15.gLeaderID = I.ID AND groups10.grank = 15
inner JOIN IMItem childItem1 ON childItem1.id = groups.itemid
left JOIN IMItem childItem2 ON childItem2.id = groups2.itemid
left JOIN IMItem childItem3 ON childItem3.id = groups3.itemid
left JOIN IMItem childItem4 ON childItem4.id = groups4.itemid
left JOIN IMItem childItem5 ON childItem5.id = groups5.itemid
left JOIN IMItem childItem6 ON childItem6.id = groups6.itemid
left JOIN IMItem childItem7 ON childItem7.id = groups7.itemid
left JOIN IMItem childItem8 ON childItem8.id = groups8.itemid
left JOIN IMItem childItem9 ON childItem9.id = groups9.itemid
left JOIN IMItem childItem10 ON childItem10.id = groups10.itemid
left JOIN IMItem childItem11 ON childItem10.id = groups11.itemid
left JOIN IMItem childItem12 ON childItem10.id = groups12.itemid
This takes forever to run. Would maybe using some sort of function make this run faster you think? Looking for any advice
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 1

Any reason why you couldn't just turn this into separate queries and union them together? You could just PIVOT them together if you need it all in one flat row.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

It's really hard to give a decent answer without any sample data.

How about you provide some sample data and expected results and you will definitely get a decent answer 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Michael T2 SSChasing Mays Points: 614 More actions · Answer 3

Groups Table

gid gleaderid itemid grank

46768 23014 36501 1

46769 23014 35405 2

46771 23014 447874

46772 23014 300265

50127 23014 185373

222140 23014 272586

222141 23014 476197

Item Table

id title

23014 Canon 430EX II Kit

18537SLR System Camera Case

2725810 Free Prints

30026Lens Cleaning Kit

35405Tripod

36501430EX II Flash

44787Batteries & Rapid Charger

47619Digital Photo Magazine

Query and result

Select i.id,

'description' = 'Kit contains' + childitem1.title + childitem2.title + etc

from imitem i

inner JOIN groups ON groups.gLeaderID = I.ID AND groups.grank = 1

left JOIN groups groups2 ON groups2.gLeaderID = I.ID AND groups2.grank = 2

left JOIN groups groups3 ON groups3.gLeaderID = I.ID AND groups3.grank = 3

left JOIN groups groups4 ON groups4.gLeaderID = I.ID AND groups4.grank = 4

left JOIN groups groups5 ON groups5.gLeaderID = I.ID AND groups5.grank = 5

left JOIN groups groups6 ON groups6.gLeaderID = I.ID AND groups6.grank = 6

left JOIN groups groups7 ON groups7.gLeaderID = I.ID AND groups7.grank = 7

inner JOIN IMItem childItem1 ON childItem1.id = groups.itemid

left JOIN IMItem childItem2 ON childItem2.id = groups2.itemid

left JOIN IMItem childItem3 ON childItem3.id = groups3.itemid

left JOIN IMItem childItem4 ON childItem4.id = groups4.itemid

left JOIN IMItem childItem5 ON childItem5.id = groups5.itemid

left JOIN IMItem childItem6 ON childItem6.id = groups6.itemid

left JOIN IMItem childItem7 ON childItem7.id = groups7.itemid

Result

iddescription

23014Kit contains Canon Speedlite 430EX II Flash & Tripod & Camera Case & Batteries & Rapid Charger & Lens Cleaning Kit & Free Prints

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 4

I'm sorry, but this is just bad design and you should push back to the developer to have them either list the included items in the package, or if they really desire, concatenate it from multiple rows, themselves.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

I have turned your sample data into something that people can use - take note that you should do this , not us...

Things to note:

1. I used two CTE expressions to provide the sample data - just because I don't like bothering with tables for a small sample.

2. Your expected result contains data that is not present in the sample, so I best-guessed what you want.

3. I used a comma in the FOR XML PATH('') part of the query, then used a REPLACE to turn commas into Ampersands, just because ampersands get converted to & amp ; by the XML processor.

;with groups (gid,gleaderid,itemid,grank )

as

(

SELECT 46768,23014,36501,1 UNION ALL

SELECT 46769,23014,35405,2 UNION ALL

SELECT 46771,23014,44787,4 UNION ALL

SELECT 46772,23014,30026,5 UNION ALL

SELECT 50127,23014,18537,3 UNION ALL

SELECT 222140,23014,27258,6 UNION ALL

SELECT 222141,23014,47619,7

),items(id,title)

as

(

SELECT 23014,'Canon 430EX II Kit' UNION ALL

SELECT 18537,'SLR System Camera Case' UNION ALL

SELECT 27258,'10 Free Prints' UNION ALL

SELECT 30026,'Lens Cleaning Kit' UNION ALL

SELECT 35405,'Tripod' UNION ALL

SELECT 36501,'430EX II Flash' UNION ALL

SELECT 44787,'Batteries & Rapid Charger' UNION ALL

SELECT 47619,'Digital Photo Magazine'

)

select id,'Kit contains '+ i_outer.title + REPLACE(titles,',','&') as description

from items as i_outer

outer apply (

select ' , '+i_inner.title

from groups as g_inner

join items as i_inner

on i_inner.id = g_inner.itemid

where g_inner.gleaderid = i_outer.id

order by g_inner.grank

for xml path('')

) as ex(titles)

where id = 23014

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Michael T2 SSChasing Mays Points: 614 More actions · Answer 6

Thanks for doing that. I am going to try building a function to see if it is faster that way. Right now this query takes about 20 minutes to run using all the joins

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Michael T2 (10/3/2012)
Thanks for doing that. I am going to try building a function to see if it is faster that way. Right now this query takes about 20 minutes to run using all the joins

Did you try the solution I posted - it should outperform your multi-join solution?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Michael T2 SSChasing Mays Points: 614 More actions · Answer 8

oh sorry, i thought you just cleaned up my post so others could use it. I will try it now

Michael T2 SSChasing Mays Points: 614 More actions · Answer 9

Wow you are amazing. Thank you. Went from over 20 minutes to 34 Seconds.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

Thanks for the feedback,

That's not too bad, but that still sounds slow - how much data is in your groups and items tables?

Are they indexed on gleaderid and id respectively?

Can you post an actual execution plan?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Michael T2 SSChasing Mays Points: 614 More actions · Answer 11

Well i am getting a lot more data then just what i posted like upc,price,image url,category, etc (about 16 fields). The query is bringing back about 4,000 kits (with an average of about 8 items in each kit). Everything has an index

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

That does seem very slow for that number of rows - if you post the execution plan (de-sensitise it if you need to), maybe we can spot the problem?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);