how to split a large query to a small queries and append them as a single query

Question

I have a large query which is taking more time to execute. I want to split the query into small queries and excute them and append them as a single result.

select
  mm.Member_Id,
  (SELECT
     SubCategory_Name
   From member_subcategory
   WHERE mm.Admiral_Type_Id = SubCategory_Id) AS Admiral_Type_Id,
  (SELECT
     SubCategory_Name
   From member_subcategory
   WHERE mm.Royalty_Type_Id = SubCategory_Id) AS Royalty_Type_Id,
  (SELECT
     SubCategory_Name
   From member_subcategory
   WHERE mm.Parent_Type_Id = SubCategory_Id) AS Parent_Type_Id,
  (SELECT
     SubCategory_Name
   From member_subcategory
   WHERE mm.Guest_Type_Id = SubCategory_Id) AS Guest_Type_Id,
  mha.First_Name,
  mha.Middle_Name,
  mha.Last_Name,
  mha.Address,
  mha.City,
  mha.State,
  mha.Zip,
  mwa.First_Name    AS Work_First_Name,
  mwa.Middle_Name   AS Work_Middle_Name,
  mwa.Last_Name     AS Work_Last_Name,
  mwa.City          AS Work_City,
  mwa.State         AS Work_State,
  mwa.Zip           AS Work_Zip,
  mrm.Year          AS Royalty_Year,
  mrm.Title         AS Royalty_Title,
  mrm.First         AS Royalty_First,
  mrm.Maiden        AS Royalty_Maiden,
  (CASE WHEN mha.Preferred=0 THEN 'Work Address' WHEN mha.Preferred=1 THEN 'Home Address' END) AS Preferred,
  (select
     msi.MaidenName  AS Spouse_MaidenName
   FROM member_spouse_info msi
   WHERE msi.Member_Id = mm.Member_Id) as Spouse_MaidenName,
  (select
     msi.Mobile      AS Spouse_Mobile
   FROM member_spouse_info msi
   WHERE msi.Member_Id = mm.Member_Id) as Spouse_Mobile,
  (select
     mai.Fleet_Year  AS Fleet_Year
   FROM member_admiral_info mai
   WHERE mai.Member_Id = mm.Member_Id) as Fleet_Year,
  mmt.Admiral_Title,
  mmt.Spouse_Title,
  mmt.Couple_Title,
  (select
     mdi.Year          AS Duchess_Year
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Year,
  (select
     mdi.College       AS Duchess_College
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_College,
  (select
     mdi.Major         AS Duchess_Major
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Major,
  (select
     mdi.Sorority      AS Duchess_Sorority
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Sorority,
  (select
     mdi.Parent_Name   AS Duchess_Parent_Name
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Parent_Name,
  (select
     mdi.Escort_Name   AS Duchess_Escort_Name
   FROM member_duchess_info mdi
   WHERE mdi.Member_Id = mm.Member_Id) as Duchess_Escort_Name,
  (select
     dp.Duchess_Note   AS Duchess_Note
   FROM duchess_payment dp
   WHERE dp.Duchess_Id = (select
                mdi.Duchess_Id
              FROM member_duchess_info mdi
              WHERE mdi.Member_Id = mm.Member_Id)) as Duchess_Note,
  (select
     em.Year           AS Escort_Year
   FROM escort_master em
   WHERE em.Member_Id = mm.Member_Id) as Escort_Year,
  (select
     em.Name           AS Escort_Name
   FROM escort_master em
   WHERE em.Member_Id = mm.Member_Id) as Escort_Name,
  (select
     em.Parent_Name    AS Escort_Parent_Name
   FROM escort_master em
   WHERE em.Member_Id = mm.Member_Id) as Escort_Parent_Name,
  (select
     em.Notes          AS Escort_Note
   FROM escort_master em
   WHERE em.Member_Id = mm.Member_Id) as Escort_Note,
  (select
     guest.Guest_Name
   FROM guest
   WHERE guest.Member_Id = mm.Member_Id) as Guest_Name,
  (select
     (CASE WHEN mpm.Status=0 THEN 'Current' WHEN mpm.Status=1 THEN 'Past due' WHEN mpm.Status=2 THEN 'Paid' END) AS Payment_Status
   FROM member_payment_master mpm
   WHERE mpm.Member_Id = mm.Member_Id) AS Payment_Status,
  (select
     mpm.Payment_Date
   FROM member_payment_master mpm
   WHERE mpm.Member_Id = mm.Member_Id) AS Payment_Date,
  (select
     mpm.Payment_Method
   FROM member_payment_master mpm
   WHERE mpm.Member_Id = mm.Member_Id) AS Payment_Method,
  (select
     mpm.Payment_Amount
   FROM member_payment_master mpm
   WHERE mpm.Member_Id = mm.Member_Id) AS Payment_Amount
FROM member_master mm,
  member_home_address mha,
  member_work_address mwa,
  member_mailing_title mmt,
  member_royalty_master mrm
where mha.Member_Id = mm.Member_Id
    AND mwa.Member_Id = mm.Member_Id
    AND mmt.Member_Id = mm.Member_Id
    AND mrm.Member_Id = mm.Member_Id
ORDER BY mm.Member_Id;

Frazz · Accepted Answer

Breaking up a query and putting together the pieces, just for optimization purposes... is not a good idea. The DBMS engine should be able to optimize things on its own, if what you are asking it is clear.

Subqueries are not evil, per se. Some RDBMS (such as FireBird) can handle many subqueries on the same tables and optimize the plan to just access the required tables once. Unfortunately not all RDBMS do this. So, removing subqueries may help out, especially in cases like this where you are fetching several columns from the same table.

Another issue is the old JOIN syntax (using the WHERE conditions). It is now advised to use the explicit JOIN... ON syntax. This may also have impact on optimization.

Here is an optimized version of your query, you should try before more extreme measures:

SELECT
   mm.Member_Id,
   sca.SubCategory_Name AS Admiral_Type_Id,
   scr.SubCategory_Name AS Royalty_Type_Id,
   scp.SubCategory_Name AS Parent_Type_Id,
   scg.SubCategory_Name AS Guest_Type_Id,
   mha.First_Name,
   mha.Middle_Name,
   mha.Last_Name,
   mha.Address,
   mha.City,
   mha.State,
   mha.Zip,
   mwa.First_Name       AS Work_First_Name,
   mwa.Middle_Name      AS Work_Middle_Name,
   mwa.Last_Name        AS Work_Last_Name,
   mwa.City             AS Work_City,
   mwa.State            AS Work_State,
   mwa.Zip              AS Work_Zip,
   mrm.Year             AS Royalty_Year,
   mrm.Title            AS Royalty_Title,
   mrm.First            AS Royalty_First,
   mrm.Maiden           AS Royalty_Maiden,
   (CASE
      WHEN mha.Preferred=0 THEN 'Work Address'
      WHEN mha.Preferred=1 THEN 'Home Address'
   END)                 AS Preferred,
   msi.MaidenName       AS Spouse_MaidenName,
   msi.Mobile           AS Spouse_Mobile,
   mai.Fleet_Year       AS Fleet_Year,
   mmt.Admiral_Title,  
   mmt.Spouse_Title,   
   mmt.Couple_Title,   
   mdi.Year             AS Duchess_Year,
   mdi.College          AS Duchess_College,
   mdi.Major            AS Duchess_Major,
   mdi.Sorority         AS Duchess_Sorority,
   mdi.Parent_Name      AS Duchess_Parent_Name,
   mdi.Escort_Name      AS Duchess_Escort_Name,
   dp.Duchess_Note      AS Duchess_Note,
   em.Year              AS Escort_Year,
   em.Name              AS Escort_Name,
   em.Parent_Name       AS Escort_Parent_Name,
   em.Notes             AS Escort_Note,
   g.Guest_Name         AS Guest_Name,
   (CASE
      WHEN mpm.Status=0 THEN 'Current'
      WHEN mpm.Status=1 THEN 'Past due'
      WHEN mpm.Status=2 THEN 'Paid'
   END)                 AS Payment_Status,
   mpm.Payment_Date     AS Payment_Date,
   mpm.Payment_Method   AS Payment_Method,
   mpm.Payment_Amount   AS Payment_Amount
FROM      member_master         mm
JOIN      member_home_address   mha ON mha.Member_Id = mm.Member_Id
JOIN      member_work_address   mwa ON mwa.Member_Id = mm.Member_Id 
JOIN      member_mailing_title  mmt ON mmt.Member_Id = mm.Member_Id
JOIN      member_royalty_master mrm ON mrm.Member_Id = mm.Member_Id
LEFT JOIN member_spouse_info    msi ON msi.Member_Id = mm.Member_Id
LEFT JOIN member_admiral_info   mai ON mai.Member_Id = mm.Member_Id
LEFT JOIN member_duchess_info   mdi ON mdi.Member_Id = mm.Member_Id
LEFT JOIN duchess_payment       dp  ON dp.Duchess_Id = mdi.Duchess_Id
LEFT JOIN escort_master         em  ON em.Member_Id  = mm.Member_Id
LEFT JOIN guest                 g   ON g.Member_Id   = mm.Member_Id
LEFT JOIN member_payment_master mpm ON mpm.Member_Id = mm.Member_Id
LEFT JOIN member_subcategory    sca ON sca.SubCategory_Id = mm.Admiral_Type_Id
LEFT JOIN member_subcategory    scr ON scr.SubCategory_Id = mm.Royalty_Type_Id
LEFT JOIN member_subcategory    scp ON scp.SubCategory_Id = mm.Parent_Type_Id
LEFT JOIN member_subcategory    scg ON scg.SubCategory_Id = mm.Guest_Type_Id
ORDER BY mm.Member_Id;

Notice that I have used many LEFT JOINS. That is because I have no idea if the rows must exist or not in the related tables. You have not told us anything about that. Query performance could be optimized further by using (INNER) JOINs instead of LEFT JOINs on tables that must have a matching row.

I also hope you have FOREIGN KEY costraints on all those tables... or at least indexes to support those relations.

how to split a large query to a small queries and append them as a single query

Answers (1)

Related Questions