i tried run query last week after 7 hours still executing , had cancel @ end of day. had returned 800+ rows end result should 20,000 rows.. advise on improving performance appreciated.
many thanks.
select a.entryid, g.groupname, max(case when e.attributedefinitionid = 15 e.attributevalue end) englishwording, max(case when e.attributedefinitionid = 41 e.attributevalue end) germanwording, max(case when e.attributedefinitionid = 64 e.attributevalue end) russianwording, max(case when e.attributedefinitionid = 91 e.attributevalue end) frenchwording, max(case when e.attributedefinitionid = 143 e.attributevalue end) italianwording, max(case when e.attributedefinitionid = 160 e.attributevalue end) spanishwording, max(case when e.attributedefinitionid = 165 e.attributevalue end) chinesewording, max(case when e.attributedefinitionid = 170 e.attributevalue end) japanesewording, max(case when h.attributedefinitionid = 17 convert(varchar(10), h.attributevalue, 120) end) datea, max(case when h.attributedefinitionid = 557 convert(varchar(10), h.attributevalue, 120) end) dateo, max(case when h.attributedefinitionid = 558 convert(varchar(10), h.attributevalue, 120) end) datec, max(case when j.attributedefinitionid = 29 j.attributevalue end) warning, max(case when l.attributedefinitionid = 23 l.attributevalue end) highlight, max(case when n.attributedefinitionid = 572 n.attributevalue end) investigations, max(case when p.attributedefinitionid = 30 p.attributevalue end) comments, max(case when s.attributedefinitionid = 39 s.attributevalue end) usd, max(case when s.attributedefinitionid = 40 s.attributevalue end) eur, max(case when s.attributedefinitionid = 92 s.attributevalue end) gbp, max(case when s.attributedefinitionid = 450 s.attributevalue end) rbn, max(case when s.attributedefinitionid = 451 s.attributevalue end) jpy, max(case when s.attributedefinitionid = 552 s.attributevalue end) hk$, max(case when u.attributedefinitionid = 142 u.attributevalue end) [status] entry inner join entrycategory b on b.entryid = a.entryid inner join category c on c.categoryid = b.categoryid inner join [group] g on g.groupid = c.groupid left outer join entryattribute d on d.entryid = a.entryid left outer join attributestring e on e.attributeid = d.attributeid left outer join entryattribute f on f.entryid = a.entryid left outer join attributedatetime h on h.attributeid = f.attributeid left outer join entryattribute on i.entryid = a.entryid left outer join attributestring j on j.attributeid = i.attributeid left outer join entryattribute k on k.entryid = a.entryid left outer join attributestring l on l.attributeid = k.attributeid left outer join entryattribute m on m.entryid = a.entryid left outer join attributestring n on n.attributeid = m.attributeid left outer join entryattribute o on o.entryid = a.entryid left outer join attributestring p on p.attributeid = o.attributeid inner join entryattribute r on r.entryid = a.entryid inner join attributestring s on s.attributeid = r.attributeid left outer join entryattribute t on t.entryid = a.entryid left outer join attributestring u on u.attributeid = t.attributeid group a.entryid, g.groupname
as far can tell, need 1 join entryattribute , attributestring tables. aggregation takes care of rest.
select a.entryid,g.groupname, max(case when e.attributedefinitionid = 15 e.attributevalue end) englishwording, max(case when e.attributedefinitionid = 41 e.attributevalue end) germanwording, max(case when e.attributedefinitionid = 64 e.attributevalue end) russianwording, max(case when e.attributedefinitionid = 91 e.attributevalue end) frenchwording, max(case when e.attributedefinitionid = 143 e.attributevalue end) italianwording, max(case when e.attributedefinitionid = 160 e.attributevalue end) spanishwording, max(case when e.attributedefinitionid = 165 e.attributevalue end) chinesewording, max(case when e.attributedefinitionid = 170 e.attributevalue end) japanesewording, max(case when e.attributedefinitionid = 17 convert(varchar(10),e.attributevalue,120) end) datea, max(case when e.attributedefinitionid = 557 convert(varchar(10),e.attributevalue,120) end) dateo, max(case when e.attributedefinitionid = 558 convert(varchar(10),e.attributevalue,120) end) datec, max(case when e.attributedefinitionid = 29 e.attributevalue end) warning, max(case when e.attributedefinitionid = 23 e.attributevalue end) highlight, max(case when e.attributedefinitionid = 572 e.attributevalue end) investigations, max(case when e.attributedefinitionid = 30 e.attributevalue end) comments, max(case when e.attributedefinitionid = 39 e.attributevalue end) usd, max(case when e.attributedefinitionid = 40 e.attributevalue end) eur, max(case when e.attributedefinitionid = 92 e.attributevalue end) gbp, max(case when e.attributedefinitionid = 450 e.attributevalue end) rbn, max(case when e.attributedefinitionid = 451 e.attributevalue end) jpy, max(case when e.attributedefinitionid = 552 e.attributevalue end) hk$, max(case when e.attributedefinitionid = 142 e.attributevalue end) [status] entry inner join entrycategory b on b.entryid = a.entryid inner join category c on c.categoryid= b.categoryid inner join [group] g on g.groupid = c.groupid left outer join entryattribute d on d.entryid = a.entryid left outer join attributestring e on e.attributeid = d.attributeid group a.entryid,g.groupname; by having multiple joins, generating humungous cartesian products within each entryid, groupname group.
Comments
Post a Comment