SELECT *,
       pt1.ProtocolDate,
       IF(COALESCE(a.CompanyName, '') = '', a.FullName, a.CompanyName) AS AddressText
from (
     SELECT pr1.GUID_ID, pr1.Report_ID, pr1.Protocol_GUID, pr1.Project_GUID, pr1.Place_GUID,
       pr1.Catalog_GUID, pr1.Catalog_Type, '' AS Catalog_GUID_Parent,
       CAST('' AS VARCHAR(200)) AS Description,
       pr1.Address_GUID, pr1.ContactName, pr1.Date_ToDoFrom, pr1.Date_ToDo, pr1.Date_Done,
       pr1.ObjType, pr1.State,
       pr1.ReportPriority, pr1.ReportInfo,
       CAST('' AS VARCHAR(500)) AS PlaceText,
       pr1.CreateDate, pr1.ModifyDate
     FROM "ProtocolReport" pr1
     WHERE pr1."Protocol_GUID" = :aProtocol_GUID
     UNION
     SELECT pr2.GUID_ID, pr2.Report_ID, pr2.Protocol_GUID, pr2.Project_GUID, pr2.Place_GUID,
       pr2.Catalog_GUID, pr2.Catalog_Type, '' AS Catalog_GUID_Parent,
       '' AS Description,
       pr2.Address_GUID, pr2.ContactName, pr2.Date_ToDoFrom, pr2.Date_ToDo, pr2.Date_Done,
       pr2.ObjType, pr2.State,
       pr2.ReportPriority, pr2.ReportInfo, 
       '' AS PlaceText,
       pr2.CreateDate, pr2.ModifyDate
     FROM (
          select *
          from (
               select * 
               FROM ProtocolReport 
               where Project_GUID = :aProject_GUID
               AND ObjType > 0
               AND Protocol_GUID IN (SELECT ptOpen.GUID_ID 
                                    FROM "Protocol" ptOpen 
                                    WHERE Project_GUID = :aProject_GUID
                                    AND ptOpen.ProtocolDate < (SELECT ProtocolDate
                                                               FROM "Protocol" 
                                                               WHERE GUID_ID = :aProtocol_GUID
                                                               )
                                    )
               ) AS prOpen
          WHERE
            NOT GUID_ID IN (
                           select GUID_ID
                           from (
                                select *
                                FROM ProtocolReport
                                where Project_GUID = :aProject_GUID
                                AND ObjType <= 0
                                AND Protocol_GUID IN (SELECT ptClosed.GUID_ID
                                                     FROM "Protocol" ptClosed
                                                     WHERE Project_GUID = :aProject_GUID
                                                     AND ptClosed.ProtocolDate < (SELECT ProtocolDate
                                                                                  FROM "Protocol"
                                                                                  WHERE GUID_ID = :aProtocol_GUID
                                                                                  )
                                                     )
                                ) as prClosed
                           )
          GROUP BY GUID_ID
          ORDER BY GUID_ID, ModifyDate DESC
          ) as pr2
       WHERE
         NOT pr2.GUID_ID IN (SELECT pr.GUID_ID FROM "ProtocolReport" pr WHERE pr.Protocol_GUID = :aProtocol_GUID)
     ) as ptrep
 LEFT OUTER JOIN "Protocol" pt1 ON (pt1.GUID_ID = ptrep.Protocol_GUID)
 LEFT OUTER JOIN "vw_AddressFullNames" a ON (a.GUID_ID = ptrep.Address_GUID)
ORDER BY ptrep.CreateDate DESC, ptrep.Report_ID

---

 Comments

---

Internally, EDB was not creating the proper type of cursor to handle the nested derived tables, and this would cause a very large slowdown due to an increased amount of navigation/repositioning for the cursor.

---

 Resolution

---

Fixed Problem on 7/4/2011 in version 2.05 build 10

---

 Products Affected

---

ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial