title: Optimizing slow SQL query: derived tables and two different joins based on row type
body: <p>I'm using Firebird 2.1. I'd like to optimize the following query as it executes really slowly (almost 2 secs) on even a small dataset (cca 500 records):</p>
  
  <p>TABLE Client stores persons and companies/company groups. (ClientType=0 person, ClientType=1 company) Primary key: ClientID </p>
  
  <p>TABLE ClientContacts stores which persons are linked to which companies: ClientID is the company's ID, ContactClientID is the person's ID linked to the company. Primary keys: ClientID, ContactClientID</p>
  
  <p>TABLE CompanyGroups stores which companys are linked to which company groups: ParentClientID is the company group's ID, ClientID is the company's ID attached to the company group. Primary keys: ParentClientID, ClientID</p>
  
  <p>So a person may belong to more than one company, and a company may belong to more than one company groups.</p>
  
  <p>I would like to list all persons and companys. For persons I want to show which companies it belongs to, and for companys I want to show which company groups the company belongs to.</p>
  
  <p>To top it, I need to search by the name of the companies/company groups to which the person/company belongs. In Firebird because of aggregate function "LIST" I can achieve this with derived tables.</p>
  
  <p>Here is the query:</p>
  
  <pre><code>SELECT C.ClientID,
    C.ClientType,
    C.ClientName,
    IIF(C.ClientType = 0, PCN.PCompanyNames, CCN.CCompanyNames),
  FROM Clients C
    LEFT JOIN (SELECT CC.ContactClientID, LIST(CL.ClientName, ', ') AS PCompanyNames
     FROM ClientContacts CC LEFT JOIN Clients CL ON CL.ClientID = CC.ClientID WHERE 
      CL.AccessRights = 0 OR CL.UserID = :UserID OR (CL.AccessRights = 2 AND 
       CL.ClientID IN (SELECT ClientID FROM ClientRights WHERE UserID = :UserID))
        GROUP BY CC.ContactClientID) PCN ON PCN.ContactClientID = C.ClientID AND C.ClientType = 0
    LEFT JOIN (SELECT CG.ClientID, LIST(CL.ClientName, ', ') AS CCompanyNames
     FROM CompanyGroups CG LEFT JOIN Clients CL ON CL.ClientID = CG.ParentClientID WHERE 
      CL.AccessRights = 0 OR CL.UserID = :UserID OR (CL.AccessRights = 2 AND
       CL.ClientID IN (SELECT ClientID FROM ClientRights WHERE UserID = :UserID))
        GROUP BY CG.ClientID) CCN ON CCN.ClientID = C.ClientID AND C.ClientType = 1
  WHERE (C.AccessRights = 0
      OR C.UserID = :UserID
        OR (C.AccessRights = 2 AND C.ClientID IN (SELECT ClientID FROM ClientRights WHERE UserID = :UserID)))
    AND (:SearchStr IS NULL
      OR (PCN.PCompanyNames COLLATE UNICODE_CI LIKE '%' || :SearchStr || '%'
      OR CCN.CCompanyNames COLLATE UNICODE_CI LIKE '%' || :SearchStr || '%'))
  </code></pre>
  
  <hr>
  
  <p><em>Update</em> Query plan, first the above query, second the above query without the WHERE clauses everywhere (no IN SELECT)</p>
  
  <pre><code>Field #01: CLIENTS.CLIENTID Alias:CLIENTID Type:INTEGER
  Field #02: CLIENTS.CLIENTTYPE Alias:CLIENTTYPE Type:INTEGER
  Field #03: CLIENTS.CLIENTNAME Alias:CLIENTNAME Type:STRING(1000)
  Field #04: .CASE Alias:CASE Type:BLOB SUB_TYPE 1
  PLAN (PCN CLIENTRIGHTS INDEX (RDB$PRIMARY46))
  PLAN (CCN CLIENTRIGHTS INDEX (RDB$PRIMARY46))
  PLAN (CLIENTRIGHTS INDEX (RDB$PRIMARY46))
  PLAN JOIN (JOIN (C INDEX (IDX_CLIENTS_ACCESSRIGHTS, IDX_CLIENTS_USERID, IDX_CLIENTS_ACCESSRIGHTS), SORT (JOIN (PCN CC NATURAL, PCN CL INDEX (RDB$PRIMARY12)))), SORT (JOIN (CCN CG NATURAL, CCN CL INDEX (RDB$PRIMARY12))))
  
  119643 fetches, 0 marks, 0 reads, 0 writes.
  0 inserts, 0 updates, 0 deletes, 19977 index, 19629 seq.
  Delta memory: 321686664 bytes.
  Total execution time: 1.531s
  
  
  Field #01: CLIENTS.CLIENTID Alias:CLIENTID Type:INTEGER
  Field #02: CLIENTS.CLIENTTYPE Alias:CLIENTTYPE Type:INTEGER
  Field #03: CLIENTS.CLIENTNAME Alias:CLIENTNAME Type:STRING(1000)
  Field #04: .CASE Alias:CASE Type:BLOB SUB_TYPE 1
  PLAN JOIN (JOIN (C NATURAL, SORT (JOIN (PCN CC NATURAL, PCN CL INDEX (RDB$PRIMARY12)))), SORT (JOIN (CCN CG NATURAL, CCN CL INDEX (RDB$PRIMARY12))))
  
  119289 fetches, 0 marks, 0 reads, 0 writes.
  0 inserts, 0 updates, 0 deletes, 19646 index, 19832 seq.
  Delta memory: 321690896 bytes.
  Total execution time: 1.406s
  </code></pre>