Recursive query with 6 joins
-
<FONT face="Times New Roman" size=3>I hope I don’t have to list all the things wrong with this.
sBuilder.AppendLine("With LatestReports (reportID) as")<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
sBuilder.AppendLine("(")<o:p></o:p>
sBuilder.AppendLine(" SELECT max(ID) from [reports]")<o:p></o:p>
sBuilder.AppendLine(" GROUP BY XMLID")<o:p></o:p>
sBuilder.AppendLine(")")<o:p></o:p>
sBuilder.AppendLine("SELECT [rdls].ID, [rdls].Name, temp.ID as reportID, temp.Date, temp.ReportID as Status")<o:p></o:p>
sBuilder.AppendLine("FROM [rdls]")<o:p></o:p>
sBuilder.AppendLine("INNER JOIN")<o:p></o:p>
sBuilder.AppendLine(" (")<o:p></o:p>
sBuilder.AppendLine(" select r.xmlID, r.ID, r.Date, rl.reportID")<o:p></o:p>
sBuilder.AppendLine(" from [RequiredReports] rq")<o:p></o:p>
sBuilder.AppendLine(" INNER JOIN [Reports] r on (r.xmlID = rq.xmlID)")<o:p></o:p>
sBuilder.AppendLine(" INNER JOIN [LatestReports] on ([LatestReports].reportID = r.ID)")<o:p></o:p>
sBuilder.AppendLine(" LEFT JOIN [RequiredReportLogs] rl on (rl.reportID = r.ID)")<o:p></o:p>
sBuilder.AppendLine(" WHERE rq.userID = @userID")<o:p></o:p>
sBuilder.AppendLine(" <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place>UNION</st1:place>")<o:p></o:p>
sBuilder.AppendLine(" select r.xmlID, r.ID, r.Date, rl.reportID from [RequiredReports] rq")<o:p></o:p>
sBuilder.AppendLine(" INNER JOIN [Reports] r on (r.xmlID = rq.xmlID)")<o:p></o:p>
sBuilder.AppendLine(" LEFT JOIN [RequiredReportLogs] rl on (rl.reportID = r.ID)")<o:p></o:p>
sBuilder.AppendLine(" WHERE rq.userID = @userID and rl.reportID is null")<o:p></o:p>
sBuilder.AppendLine(" )")<o:p></o:p>
sBuilder.AppendLine(" as Temp on (Temp.xmlID = [RDLs].xmlID)")<o:p></o:p>
sBuilder.AppendLine("and [RDLs].Subreport = 0")
</FONT>
-
I didn't try to see what's wrong, but it has very good identifiers. I wish I worked with a database with such good names for columns and tables.
-
Should be moved into a view, for readability. Also for a performance gain, depending on whether the program wastes time rebuilding the same string each time you enter the routine that makes use of it.
Can be simplified to the following, unless I've missed something:
SELECT RDLs.ID, RDLs.Name, r.ID as reportID, r.Date, rl.ReportID as Status FROM RDLs INNER JOIN RequiredReports rq ON RDLs.xmlID = rq.xmlID INNER JOIN Reports r ON rq.xmlID = r.xmlID LEFT JOIN RequiredReportLogs rl on r.ID = rl.ReportID WHERE rq.UserID = @UserID AND RDLs.Subreport = 0 AND ( rl.ReportID IS NULL OR r.ID IN ( SELECT MAX(ID) FROM Reports GROUP BY xmlID ) )
-
@emurphy said:
Also for a performance gain, depending on whether the program wastes time rebuilding the same string each time you enter the routine that makes use of it.
Time to build a string consisting of ~800 chars: 0.000001sec
Time to run a recursive query with 6 joins: probably a little bit more
But there might be a performance gain because the database doesn't have to reparse the same statement again and again.
-
@ammoQ said:
But there might be a performance gain because the database doesn't have to reparse the same statement again and again.
Assuming that he doesn't use a prepared statement, of course.
-
@Cloaked User said:
@ammoQ said:
But there might be a performance gain because the database doesn't have to reparse the same statement again and again.
Assuming that he doesn't use a prepared statement, of course.
If it was a prepared statement, I suppose there would be no performance difference at all between a long query and the same long query wrapped into a view.
-
@liserdarts said:
temp.ID as reportID, … temp.ReportID as Status
Okay, that looks a little bit psychotic.