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
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.
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);
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
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);
oh sorry, i thought you just cleaned up my post so others could use it. I will try it now
Wow you are amazing. Thank you. Went from over 20 minutes to 34 Seconds.
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);
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
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);
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply