Recursive query with 6 joins

liserdarts

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>

cangulo

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.

emurphy

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
      )
  )

ammoQ

@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.

Cloaked_User

@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.

ammoQ

@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.

db2

@liserdarts said:

temp.ID as reportID, … temp.ReportID as Status

Okay, that looks a little bit psychotic.