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.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.