Sql-server – Improve CASE select performance on view creation

caseperformancesql server

I am new to SQL, and the below view query is taking a long time to return values because of the case statement, can someone help in identifying how I can speed things up?

CREATE OR REPLACE FORCE VIEW "ARBANCDB"."V_GDW_REPLICA" 
          ("WWID", 
           "LAST_NAME", 
           "FIRST_NAME", 
           "MIDDLE_NAME", 
           "FULL_NAME", 
           "SUFFIX_NAME", 
           "CONTINUOUS_SERVICE_DATE", 
           "COST_CENTER_CODE", 
           "COST_CENTER_DESC", 
           "EMPLOYMENT_STATUS", 
           "EMPLOYMENT_STATUS_CODE", 
           "FLSA_CODE", 
           "FTE_PCT", 
           "JOB_FUNCTION_CODE", 
           "JOB_SUBFUNCTION_CODE", 
           "HIRE_DATE", 
           "JOB_FUNCTION", 
           "JOB_GROUP", 
           "JOB_HIERARCHY", 
           "JOB_HIER_CD", 
           "JOB_ID", 
           "JOB_SUBFUNCTION", 
           "LATEST_JOB_EFFECTIVE_DATE", 
           "LEGAL_ENTITY", 
           "LEGAL_ENTITY_CODE", 
           "MANAGER_FIRST_NAME", 
           "MANAGER_FULL_NAME", 
           "MANAGER_LAST_NAME", 
           "MANAGER_POSITION_TITLE", 
           "MANAGER_SUPERVISORY_ORG", 
           "MANAGER_WWID", 
           "MRC", 
           "MRC_CODE", 
           "ORIGINAL_HIRE_DATE", 
           "PAY_FREQUENCY_CODE", 
           "PAY_RATE_TYPE", 
           "POSITION_TITLE", 
           "REPORTING_HIERARCHY", 
           "LEVEL01", 
           "LEVEL02", 
           "LEVEL03", 
           "LEVEL04", 
           "LEVEL05", 
           "LEVEL06", 
           "LEVEL07", 
           "LEVEL08", 
           "LEVEL09", 
           "LEVEL10", 
           "LEVEL11", 
           "LEVEL12", 
           "LEVEL13", 
           "LEVEL14", 
           "LEVEL15", 
           "LEVEL16", 
           "LEVEL17", 
           "LNAME01", 
           "LNAME02", 
           "LNAME03", 
           "LNAME04", 
           "LNAME05", 
           "LNAME06", 
           "LNAME07", 
           "LNAME08", 
           "LNAME09", 
           "LNAME10", 
           "LNAME11", 
           "LNAME12", 
           "LNAME13", 
           "LNAME14", 
           "LNAME15", 
           "LNAME16", 
           "LNAME17", 
           "SALES_INCENTIVE_IND", 
           "SCHEDULED_WEEKLY_HOURS", 
           "SECTOR", 
           "SECTOR_CODE", 
           "SUPERVISORY_ORG", 
           "TERMINATION_DATE", 
           "TIME_TYPE", 
           "WORK_ADDRESS_1", 
           "WORK_ADDRESS_2", 
           "WORK_ADDRESS_3", 
           "WORK_ADDRESS_4", 
           "WORK_CITY", 
           "WORK_COUNTRY", 
           "WORK_COUNTRY_CODE", 
           "WORK_EMAIL", 
           "WORK_POSTAL_CODE", 
           "WORK_REGION", 
           "WORK_STATE_CODE", 
           "WORKER_SUBTYPE", 
           "WORKER_TYPE", 
           "REGION_CODE", 
           "REGION_NAME", 
           "MOBILE_PHONE", 
           "BUSINESS_PHONE", 
           "USER_NAME", 
           "DOMAIN_NAME", 
           "WINDOWS_UPN", 
           "ACQUISITION_CODE", 
           "ACQUISITION_DATE", 
           "DEFAULT_WEEKLY_HOURS", 
           "HR_SOURCE_SYSTEM", 
           "HR_SOURCE_SYSTEM_CODE", 
           "CAMPUS_CODE", 
           "MGR_EMAIL_ADDR", 
           "WORK_STATE_CODE_2", 
           "FIN_CC_CD", 
           "BUS_AREA_CD", 
           "LAST_UPDATE_DATE", 
           "NUMBER_DIRECTREPORTS", 
           "BUSN_UN_CD", 
           "SITE", 
           "JOB_HIER_ID_LVL", 
           "JOB_HIER_NAME_LVL", 
           "ACTV_DIRY_EMAIL_ADDR", 
           "ACTV_DIRY_USRNM", 
           "ROW_ADD_TMS", 
           "ROW_ADD_PGM_NO", 
           "ROW_UPDT_TMS", 
           "ROW_UPDT_PGM_NO") AS 
  SELECT A.wwid, A.last_name, 
         A.first_name, 
         A.middle_name, 
         A.full_name,       
         A.suffix_name, 
         A.continuous_service_date, 
         A.cost_center_code,       
         A.cost_center_desc, 
         A.employment_status,       
         A.employment_status_code, 
         A.flsa_code, A.fte_pct,       
         A.job_function_code, 
         A.job_subfunction_code, 
         A.hire_date,       
         A.job_function, 
         A.job_group, 
         A.job_hierarchy, 
         A.job_hier_cd,       
         A.job_id, 
         A.job_subfunction, 
         A.latest_job_effective_date,       
         A.legal_entity, 
         A.legal_entity_code,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.MANAGER_FIRST_NAME
         ELSE (SELECT B.FIRST_NAME FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS MANAGER_FIRST_NAME,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.MANAGER_FULL_NAME
         ELSE (SELECT B.FULL_NAME FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS MANAGER_FULL_NAME,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.MANAGER_LAST_NAME
         ELSE (SELECT B.LAST_NAME FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS MANAGER_LAST_NAME,    
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.manager_position_title
         ELSE (SELECT B.position_title FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS manager_position_title,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.manager_supervisory_org
         ELSE (SELECT B.supervisory_org FROM arbancdb.gdw_replica B WHERE B.wwid=A.HOST_MANAGER_WWID)END) AS manager_supervisory_org,
        (CASE 
         WHEN A.IA_TYPE IS NULL THEN A.MANAGER_WWID
         ELSE HOST_MANAGER_WWID END) AS MANAGER_WWID,
        A.mrc, 
        A.mrc_code, 
        A.original_hire_date,       
        A.pay_frequency_code, 
        A.pay_rate_type, 
        A.position_title,       
        A.reporting_hierarchy, 
        A.level01, 
        A.level02, 
        A.level03,       
        A.level04, 
        A.level05, 
        A.level06, 
        A.level07, 
        A.level08, 
        A.level09,       
        A.level10, 
        A.level11, 
        A.level12, 
        A.level13, 
        A.level14, 
        A.level15,       
        A.level16, 
        A.level17, 
        A.lname01, 
        A.lname02, 
        A.lname03, 
        A.lname04,       
        A.lname05, 
        A.lname06, 
        A.lname07, 
        A.lname08, 
        A.lname09, 
        A.lname10,       
        A.lname11, 
        A.lname12, 
        A.lname13, 
        A.lname14, 
        A.lname15, 
        A.lname16,       
        A.lname17, 
        A.sales_incentive_ind, 
        A.scheduled_weekly_hours,       
        A.sector, 
        A.sector_code, 
        A.supervisory_org, 
        A.termination_date,       
        A.time_type, 
        A.work_address_1, 
        A.work_address_2,       
        A.work_address_3, 
        A.work_address_4, 
        A.work_city, 
        A.work_country,       
        A.work_country_code, 
        (CASE 
         WHEN A.work_email IS NULL THEN A.actv_diry_email_addr 
         ELSE A.work_email END) AS work_email, 
        A.work_postal_code,      
        A.work_region, 
        A.work_state_code, 
        A.worker_subtype,       
        A.worker_type, 
        A.region_code, 
        A.region_name, 
        A.mobile_phone,       
        A.business_phone, 
        A.USER_NAME, 
        A.domain_name, 
        A.windows_upn,       
        A.acquisition_code, 
        A.acquisition_date, 
        A.default_weekly_hours,       
        A.hr_source_system, 
        A.hr_source_system_code, 
        A.campus_code,       
        A.mgr_email_addr, 
        A.work_state_code_2, 
        A.fin_cc_cd,       
        A.bus_area_cd, 
        A.last_update_date, 
        A.number_directreports, 
        (SELECT B.BUSN_UN_CD FROM sapncdb.V_LYNX_COST_CTR_STGEG@sapncdb B WHERE lpad(cost_ctr_cd,10,'0') =A.fin_cc_cd )AS BUSN_UN_CD, 
        (SELECT C.default_site FROM sapncdb.V_LYNX_COST_CTR_STGEG@sapncdb B, arbancdb.BU C WHERE lpad(cost_ctr_cd,10,'0') =A.fin_cc_cd AND B.BUSN_UN_CD = C.bu_cd) AS SITE,
        A.job_hier_id_lvl,
        A.job_hier_name_lvl,
        A.actv_diry_email_addr,
        A.actv_diry_usrnm, 
        A.row_add_tms, 
        A.row_add_pgm_no, 
        A.row_updt_tms,      
        A.row_updt_pgm_no  
   FROM arbancdb.gdw_replica A;

Best Answer

Try joining to arbancdb.gdw_replica in your FROM clause (use a LEFT JOIN to avoid filtering. That way you’ll have access to those columns you use in your CASE statements.