Advice optimizing Live Report

Amanda_Rose

We have 2 variations of a Live Report desigend to display any unique documents that a user has added or changed on our system in the past month. While these work, they run terribly slowly. Can I ask fro advice on optimizing the SQL or other wise tweaking these to speed the results up? Thanks in advance, Amanda See query below:Query 1:select top 100 %1 from Dtree Dtree, Dtree OwnerNode, DVersData V where (OwnerNode.SubType not in (161, 162)) and %2 and Dtree.OwnerID=-OwnerNode.DataID and Dtree.OwnerID not in (-2060,-2063) and V.DocID=Dtree.DataID and V.Version=Dtree.VersionNum and V.Owner=%3 order by Dtree.CreateDate DESCQuery 2:select top 100 Dtree.*, OwnerNode.Name as ProjectName from (select top 1000 D.* from Dtree D, (SELECT Distinct DocID FROM DVersData where VerMDate>GetDate()-31 and Owner=%1) V where D.SubType=144 and D.OwnerID not in (-2060,-2063) and V.DocID=D.DataID) Dtree, ll9501.Dtree OwnerNode where Dtree.OwnerID=-OwnerNode.DataID and (OwnerNode.SubType not in (162 , 161)) order by Dtree.ModifyDate Desc

Tim_Hunter

Amanda, what are the parameter values for these two reports? I am not sure I understand what some of the query is returning for you