To UNION OR Not to UNION

Database
Enthusiast

To UNION OR Not to UNION

SELECT DISTINCT
‘ ON GOING’ AS RPT_TYPE
, CURRENT_DATE – CAST(b.TIMESTMP AS DATE) AS AGING
, D.NAME AS CATEGORY
, B.STATUS_ID AS “TYPE”
, A.WARNING_ID AS INCIDENT_ID
, E.RISK AS RISK_REASON
, C.STATUS_NAME AS STAT_DESCRIPTION
, I.COMP_UNT_NM AS DIVISION
,F.AGT_FRST_NM AS FIRST_NAME
,F.AGT_LAST_NM AS LAST_NAME
,(CURRENT_TIMESTAMP (FORMAT ‘MM/DD/YYYY HH:MI:SS’)(CHAR(20))) AS REPORT_RUN_DATE

FROM
   WARNINGS A
INNER JOIN
   WORK_HISTORY B
ON
   A.SRC_WARNING_KEY = B.ARTFCT_KEY
INNER JOIN
   WORK_JOB_STATUS C
ON
   B.WORK_JOB_STATUS ID = C.WORK_JOB_STATUS_ID
INNER JOIN
   ORGANIZATION_HIER D
ON
   D.ORG_ID = B.ORG_ID
LEFT OUTER JOIN
   WARNINGS_DTL_MEMO E
ON
   E.SRC_WARNING_KEY = A.SRC_WARNING_KEY
LEFT OUTER JOIN
   AGT_PRFL F
ON
   F.AGTID = CAST (B.CREAT_BY_AGTID AS DECIMAL(38,0))
LEFT OUTER JOIN
   AGNT_GROUP_RELATION G
ON
   F.AGTID  = CAST(G.AGTID AS DECIMAL(38,0))  
LEFT OUTER JOIN
   GROUP_ORGANIZATION_HIER H
ON
   G.GRP_ID = H.GRP_ID
LEFT OUTER JOIN
   ORGANIZATION_DIVISION I
ON
   I.SRC_ORG_UNT = H.SRC_ORG_UNT

WHERE
   WORK_JOB_STATUS ID IN (‘5609’,’6934’)

UNION

SELECT DISTINCT
‘ ON GOING’ AS RPT_TYPE
, CURRENT_DATE – CAST(b.TIMESTMP AS DATE) AS AGING
, D.NAME AS CATEGORY
, B.STATUS_ID AS “TYPE”
, A.BUREAU_ID AS INCIDENT_ID
, E.RISK AS RISK_REASON
, C.STATUS_NAME AS STAT_DESCRIPTION
, I.COMP_UNT_NM AS DIVISION
,F.AGT_FRST_NM AS FIRST_NAME
,F.AGT_LAST_NM AS LAST_NAME
,(CURRENT_TIMESTAMP (FORMAT ‘MM/DD/YYYY HH:MI:SS’)(CHAR(20))) AS REPORT_RUN_DATE

FROM
   BUREAU A
INNER JOIN
   WORK_HISTORY B
ON
   A.SRC_WARNING_KEY = B.ARTFCT_KEY
INNER JOIN
   WORK_JOB_STATUS C
ON
   B.WORK_JOB_STATUS ID = C.WORK_JOB_STATUS_ID
INNER JOIN
   ORGANIZATION_HIER D
ON
   D.ORG_ID = B.ORG_ID
LEFT OUTER JOIN
   WARNINGS_DTL_MEMO E
ON
   E.SRC_WARNING_KEY = A.SRC_WARNING_KEY
LEFT OUTER JOIN
   AGT_PRFL F
ON
   F.AGTID = CAST (B.CREAT_BY_AGTID AS DECIMAL(38,0))
LEFT OUTER JOIN
   AGNT_GROUP_RELATION G
ON
   F.AGTID  = CAST(G.AGTID AS DECIMAL(38,0))  
LEFT OUTER JOIN
   GROUP_ORGANIZATION_HIER H
ON
   G.GRP_ID = H.GRP_ID
LEFT OUTER JOIN
   ORGANIZATION_DIVISION I
ON
   I.SRC_ORG_UNT = H.SRC_ORG_UNT

WHERE
   WORK_JOB_STATUS ID IN (‘5128’,’5118’)
    

Hi,

I am trying to optimize my query from a UNION of two query into simply just one query that does everything.  Essentially the top and bottom half of the query is the same.  However, the difference lies in that one query takes incident_ids from a Warnings table and the other a Bureau table.  My code for the union.  This query would be fine for hundreds of rows but I need this query for 500 K rows or more.  Let me know your thoughts.  Thanks.

3 REPLIES
Enthusiast

Re: To UNION OR Not to UNION

Did you try UNION ALL, It should be faster as there is no extra processing required to remove the duplicate rows which UNION does. You are already selecting unique rows from both the resultset (work_job_status_id in (5609, 6934) & work_job_status_id in (5128, 5118)). Looks like both queries are fetching distinct resultset according to their rules, so UNION ALL should perform better.

Another alternative to UNION / UNION ALL is doing a FULL OUTER JOIN and using a COALESCE to pick not null values. If both the tables (WARNINGS & BUREAU) have the same PI then data won't be distributed and should be faster.

You have to try and test which one will serve better for your data set :)

Enthusiast

Re: To UNION OR Not to UNION

Hi,

First of all does the INCIDENT_ID from the Warning table and the BUREAU_ID from the Bureau table are the same, if they are the same. You can use only one query alone. Or else you can use this query.

SELECT DISTINCT‘ 
 ON GOING’ AS RPT_TYPE , CURRENT_DATE – CAST(b.TIMESTMP AS DATE) AS AGING ,
  D.NAME AS CATEGORY , B.STATUS_ID AS “TYPE” ,INCIDENT_ID  AS INCIDENT_ID,
  E.RISK AS RISK_REASON , C.STATUS_NAME AS STAT_DESCRIPTION , I.COMP_UNT_NM AS DIVISION ,
  F.AGT_FRST_NM AS FIRST_NAME ,F.AGT_LAST_NM AS LAST_NAME ,(CURRENT_TIMESTAMP (FORMAT 'MM/DD/YYYY HH:MI:SS')(CHAR(20))) AS REPORT_RUN_DATE  
FROM  
(
 SELECT WARNING_ID AS  INCIDENT_ID, SRC_WARNING_KEY   FROM WARNINGS
 UNION
 SELECT BUREAU_ID AS  INCIDENT_ID, SRC_WARNING_KEY FROM BUREAU
  ) A INNER JOIN   WORK_HISTORY B
 ON   A.SRC_WARNING_KEY = B.ARTFCT_KEY
 INNER JOIN   WORK_JOB_STATUS C
 ON   B.WORK_JOB_STATUS ID = C.WORK_JOB_STATUS_ID
  INNER JOIN   ORGANIZATION_HIER D
 ON   D.ORG_ID = B.ORG_ID
  LEFT OUTER JOIN   WARNINGS_DTL_MEMO E
 ON   E.SRC_WARNING_KEY = A.SRC_WARNING_KEY
 LEFT OUTER JOIN   AGT_PRFL F
 ON   F.AGTID = CAST (B.CREAT_BY_AGTID AS DECIMAL(38,0))
 LEFT OUTER JOIN   AGNT_GROUP_RELATION G
 ON   F.AGTID  = CAST(G.AGTID AS DECIMAL(38,0)) 
  LEFT OUTER JOIN   GROUP_ORGANIZATION_HIER H
 ON   G.GRP_ID = H.GRP_ID
  LEFT OUTER JOIN   ORGANIZATION_DIVISION I
 ON   I.SRC_ORG_UNT = H.SRC_ORG_UNT  
WHERE   WORK_JOB_STATUS ID IN ('5609','6934','5128','5118' )   

Thanks & Regards,

Adharssh.

Enthusiast

Re: To UNION OR Not to UNION

Hi Adharssh!

I apologize for the delay in responding.   This query works.  The Warnings and Bureau table do not have the same Incident ID.  Thanks a lot for helping me out!  I have IT department that will run these queries and they want everything optimized as not to impact their spools. So I thank you and IT thanks you!