sql server 2008 - WITH clause performance issues; Rewrite into While Loop? -
i looking @ areas optimize of our stored procedures , have run across in performance analysis, chunk of sql in stored proc performing terribly. i've done research , i've read using clause when don't have unique records no-no rewrite having troubles. i'm thinking while loop may best bet in order achieve result after cannot hierarchy of data populate correctly (specifically [level] field). here chunk of sql trying rewrite. suggestions/ideas tremendously appreciated.
;with rulechart ( ruleid, ruledetailid, ruleunitgroupid, parentruleunitgroupid, [level], isnextruleunitgroupavailable ) ( select distinct rd.ruleid, rd.ruledetailid, rd.ruleunitgroupid, rd.parentruleunitgroupid, 0 [level], rd.isnextruleunitgroupavailable #ttblruledetail rd rd.ruleid in ( select ruleid #ttblrule resultid in ( select resultid #ttblresultunit ) ) , rd.parentruleunitgroupid null union select rd.ruleid, rd.ruledetailid, rd.ruleunitgroupid, rd.parentruleunitgroupid, [level] + 1, rd.isnextruleunitgroupavailable #ttblruledetail rd inner join rulechart rc on rd.parentruleunitgroupid = rc.ruleunitgroupid , rd.ruleid=rc.ruleid )
a lot depends on size of temp tables you're utilizing. how big they, create indexes on them prior clause? looking @ statement looks create index on #ttblruledetail.ruleid, , on #ttblruledetail.parentruleunitgroupid. performance, better approach eliminate temp tables altogether, because may have indexes on source tables speed query.
Comments
Post a Comment