?
Solved

SELECT statement and CPU usage

Posted on 2013-01-18
8
Medium Priority
?
329 Views
Last Modified: 2013-01-30
in what circumstances can SELECT statement trigger huge & persistent CPU Usage?
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1200 total points
ID: 38793396
The performance of the query is completely dependent upon how much data is being processed and the amount of work necessary.  That is, double the number of rows inspected and you approximately double the amount of work required.

Tuning is a huge part of this.  Correct page size/blocking, correct indexing, etc.


Kent
0
 
LVL 5

Author Comment

by:25112
ID: 38793398
in the below reporting self-generated code, i need to see what may be the point of contention for CPU... we are also looking at index, but beyond that we want to tell the vendor if the code is indeed causing a cpu hike. the report does bring lot of records back.. but is there any inherent bad elementss that would cause spu usage more in the code? (for this app, at this time, the vendor only generates the code, which we are fighting)
select "T0"."C0" AS "C0", "T0"."C2" AS "C1", "T0"."C3" AS "C2", "T0"."C1" AS "C3", "T0"."C4" AS "C4", "T0"."C5" AS "C5", "T0"."C6" AS "C6", "T0"."C7" AS "C7", "T0"."C8" AS "C8", "T0"."C9" AS "C9", "T0"."C10" AS "C10", "T0"."C11" AS "C11", "T0"."C12" AS "C12", "T0"."C13" AS "C13", "T0"."C14" AS "C14", "T0"."C15" AS "C15", "T0"."C16" AS "C16", "T0"."C17" AS "C17", "T0"."C18" AS "C18", "T0"."C19" AS "C19", "T0"."C20" AS "C20", "T0"."C21" AS "C21", "T0"."C22" AS "C22", "T0"."C23" AS "C23", "T0"."C24" AS "C24", "T0"."C25" AS "C25", "T0"."C26" AS "C26", "T0"."C27" AS "C27", "T0"."C28" AS "C28", "T0"."C29" AS "C29", "T0"."C30" AS "C30", "T0"."C31" AS "C31", "T0"."C32" AS "C32", "T0"."C33" AS "C33", "T0"."C34" AS "C34", "T0"."C35" AS "C35", "T0"."C36" AS "C36" from (select "Base1"."Branch_Year" AS "C0", max("Base1"."Branch_Year") AS "C1", "Base1"."Employee_ID" AS "C2", "Base1"."County_Code" AS "C3", "Base1"."Attributed_County_Type" AS "C4", "Base1"."Attributed_County_Code" AS "C5", "Base1"."Attributed_County_Name" AS "C6", "Base1"."Attributed_Branch_Type" AS "C7", "Base1"."Attributed_Branch_ID" AS "C8", "Base1"."Attributed_Branch_Name" AS "C9", "Base1"."Employee_Exp_Level" AS "C10", "Base1"."Employee_Gender_Code" AS "C11", "Base1"."MaxEdu_Code" AS "C12", "Base1"."c11" AS "C13", "Base1"."BIL_Program_Entrance_Code" AS "C14", "WorkVisa"."c7" AS "C15", "Base1"."Train_Req_Status_Code" AS "C16", "Base1"."Promotion_Status" AS "C17", "Base1"."Cert_Type_Code" AS "C18", "Base1"."Date_of_Entry_to_CurrZip" AS "C19", "Base1"."c38" AS "C20", "Base1"."Organization_Type" AS "C21", "Base1"."County_Name" AS "C22", "Base1"."Branch_Type" AS "C23", "Base1"."ZIP_ID" AS "C24", "Base1"."Full_ZIP_Name" AS "C25", "Base1"."Program_Name" AS "C26", "Base1"."Sales_Status_Code" AS "C27", "Base1"."Sales_Status" AS "C28", "Base1"."Visa_Employee" AS "C29", "Base1"."Employee_Status_Code" AS "C30", "Base1"."Employee_Name" AS "C31", "Base1"."Employee_Middle_Initial" AS "C32", "Base1"."Employee_Last_Name" AS "C33", "Base1"."EMP_BIRTHDATE" AS "C34", "Base1"."Employee_Birth_Date" AS "C35", "Base1"."Employee_First_Name" AS "C36" from (select "Base2"."Organization_Type" AS "Organization_Type", "Base2"."County_Code" AS "County_Code", "Base2"."County_Name" AS "County_Name", "Base2"."Branch_Type" AS "Branch_Type", "Base2"."ZIP_ID" AS "ZIP_ID", "Base2"."Full_ZIP_Code" AS "Full_ZIP_Code", "Base2"."Employee_ID" AS "Employee_ID", "Base2"."Employee_Exp_Level" AS "Employee_Exp_Level", "Base2"."Employee_Gender_Code" AS "Employee_Gender_Code", "Base2"."MaxEdu_Code" AS "MaxEdu_Code", "Base2"."c11" AS "c11", "Base2"."BIL_Program_Entrance_Code" AS "BIL_Program_Entrance_Code", "Base2"."WorkVisa_Status_Code" AS "WorkVisa_Status_Code", "Base2"."Train_Req_Status_Code" AS "Train_Req_Status_Code", "Base2"."Promotion_Status" AS "Promotion_Status", "Base2"."Branch_Year" AS "Branch_Year", "Base2"."Cert_Type_Code" AS "Cert_Type_Code", "Base2"."Date_of_Entry_to_CurrZip" AS "Date_of_Entry_to_CurrZip", "Base2"."Visa_Employee" AS "Visa_Employee", "Base2"."Employee_Status_Code" AS "Employee_Status_Code", "Base2"."LOC_Type_Code" AS "LOC_Type_Code", "Base2"."Branch_Type_Code" AS "Branch_Type_Code", "Base2"."Review_Status_Code" AS "Review_Status_Code", "Base2"."ZIP_Code_of_Sales" AS "ZIP_Code_of_Sales", "Base2"."ZIP_of_Sales" AS "ZIP_of_Sales", "Base2"."ZIP_of_Sales_Type" AS "ZIP_of_Sales_Type", "Base2"."County_Code_of_Sales" AS "County_Code_of_Sales", "Base2"."Reporting_County_Name" AS "Reporting_County_Name", "Base2"."Reporting_County_Type" AS "Reporting_County_Type", "Sales_Status"."Employee_ID" AS "Institutionalized_ID", "Base2"."Review_ID" AS "Review_ID", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."County_Code_of_Sales" else "Base2"."County_Code" end  AS "Attributed_County_Code", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."ZIP_Code_of_Sales" else "Base2"."ZIP_ID" end  AS "Attributed_Branch_ID", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."Reporting_County_Name" else "Base2"."County_Name" end  AS "Attributed_County_Name", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."ZIP_of_Sales" else "Base2"."Full_ZIP_Code" end  AS "Attributed_Branch_Name", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."Reporting_County_Type" else "Base2"."LOC_Type_Code" end  AS "Attributed_County_Type", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."ZIP_of_Sales_Type" else "Base2"."Branch_Type_Code" end  AS "Attributed_Branch_Type", "Base2"."c31" AS "c38", "Base2"."Program_Name" AS "Program_Name", "Sales_Status"."Sales_Status_Code" AS "Sales_Status_Code", "Sales_Status"."Sales_Status" AS "Sales_Status", "Base2"."Employee_Name" AS "Employee_Name", "Base2"."Employee_Middle_Initial" AS "Employee_Middle_Initial", "Base2"."Employee_Last_Name" AS "Employee_Last_Name", "Base2"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", "Base2"."Employee_First_Name" AS "Employee_First_Name", "Base2"."Employee_Birth_Date" AS "Employee_Birth_Date" from (select "Base3"."Organization_Type" AS "Organization_Type", "Base3"."County_Code" AS "County_Code", "Base3"."County_Name" AS "County_Name", "Base3"."Branch_Type" AS "Branch_Type", "Base3"."ZIP_ID" AS "ZIP_ID", "Base3"."Full_ZIP_Code" AS "Full_ZIP_Code", "Base3"."Employee_ID" AS "Employee_ID", "Base3"."Employee_Exp_Level" AS "Employee_Exp_Level", "Base3"."Employee_Gender_Code" AS "Employee_Gender_Code", "Base3"."MaxEdu_Code" AS "MaxEdu_Code", "Base3"."c11" AS "c11", "Base3"."BIL_Program_Entrance_Code" AS "BIL_Program_Entrance_Code", "Base3"."WorkVisa_Status_Code" AS "WorkVisa_Status_Code", "Base3"."Train_Req_Status_Code" AS "Train_Req_Status_Code", "Base3"."Promotion_Status" AS "Promotion_Status", "Base3"."Branch_Year" AS "Branch_Year", "Base3"."Cert_Type_Code" AS "Cert_Type_Code", "Base3"."Date_of_Entry_to_CurrZip" AS "Date_of_Entry_to_CurrZip", "Base3"."Visa_Employee" AS "Visa_Employee", "Base3"."Employee_Status_Code" AS "Employee_Status_Code", "Base3"."LOC_Type_Code" AS "LOC_Type_Code", "Base3"."Branch_Type_Code" AS "Branch_Type_Code", "Base3"."Review_Status_Code" AS "Review_Status_Code", "Programs"."Employee_ID" AS "Review_ID", "Base3"."ZIP_Code_of_Sales" AS "ZIP_Code_of_Sales", "Base3"."ZIP_of_Sales" AS "ZIP_of_Sales", "Base3"."ZIP_of_Sales_Type" AS "ZIP_of_Sales_Type", "Base3"."County_Code_of_Sales" AS "County_Code_of_Sales", "Base3"."Reporting_County_Name" AS "Reporting_County_Name", "Base3"."Reporting_County_Type" AS "Reporting_County_Type", "Base3"."c23" AS "c31", "Programs"."Program_Name" AS "Program_Name", "Base3"."Employee_Name" AS "Employee_Name", "Base3"."Employee_Middle_Initial" AS "Employee_Middle_Initial", "Base3"."Employee_Last_Name" AS "Employee_Last_Name", "Base3"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", "Base3"."Employee_Birth_Date" AS "Employee_Birth_Date", "Base3"."Employee_First_Name" AS "Employee_First_Name" from (select "Base4"."Organization_Type" AS "Organization_Type", "Base4"."County_Code" AS "County_Code", "Base4"."County_Name" AS "County_Name", "Base4"."Branch_Type" AS "Branch_Type", "Base4"."ZIP_ID" AS "ZIP_ID", "Base4"."Full_ZIP_Code" AS "Full_ZIP_Code", "Base4"."Employee_ID" AS "Employee_ID", "Base4"."Employee_Exp_Level" AS "Employee_Exp_Level", "Base4"."Employee_Gender_Code" AS "Employee_Gender_Code", "Base4"."MaxEdu_Code" AS "MaxEdu_Code", "Base4"."c11" AS "c11", "Base4"."BIL_Program_Entrance_Code" AS "BIL_Program_Entrance_Code", "Base4"."WorkVisa_Status_Code" AS "WorkVisa_Status_Code", "Base4"."Train_Req_Status_Code" AS "Train_Req_Status_Code", "Base4"."Promotion_Status" AS "Promotion_Status", "Base4"."Branch_Year" AS "Branch_Year", "Base4"."Cert_Type_Code" AS "Cert_Type_Code", "Base4"."Date_of_Entry_to_CurrZip" AS "Date_of_Entry_to_CurrZip", "Base4"."Visa_Employee" AS "Visa_Employee", "Base4"."Employee_Status_Code" AS "Employee_Status_Code", "Base4"."LOC_Type_Code" AS "LOC_Type_Code", "Base4"."Branch_Type_Code" AS "Branch_Type_Code", "Base4"."c28" AS "c23", "Base4"."Review_Status_Code" AS "Review_Status_Code", "Base4"."ZIP_Code_of_Sales" AS "ZIP_Code_of_Sales", "Base4"."ZIP_of_Sales" AS "ZIP_of_Sales", "Base4"."ZIP_of_Sales_Type" AS "ZIP_of_Sales_Type", "Base4"."County_Code_of_Sales" AS "County_Code_of_Sales", "Reporting_County"."Reporting_County_Name" AS "Reporting_County_Name", "Reporting_County"."Reporting_County_Type" AS "Reporting_County_Type", "Base4"."Employee_Name" AS "Employee_Name", "Base4"."Employee_Middle_Initial" AS "Employee_Middle_Initial", "Base4"."Employee_Last_Name" AS "Employee_Last_Name", "Base4"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", "Base4"."Employee_First_Name" AS "Employee_First_Name", "Base4"."Employee_Birth_Date" AS "Employee_Birth_Date" from (select "Base5"."Organization_Type" AS "Organization_Type", "Base5"."County_Code" AS "County_Code", "Base5"."County_Name" AS "County_Name", "Base5"."Branch_Type" AS "Branch_Type", "Base5"."ZIP_ID" AS "ZIP_ID", "Base5"."Full_ZIP_Code" AS "Full_ZIP_Code", "Base5"."Employee_ID" AS "Employee_ID", "Base5"."Employee_Exp_Level" AS "Employee_Exp_Level", "Base5"."Employee_Gender_Code" AS "Employee_Gender_Code", "Base5"."MaxEdu_Code" AS "MaxEdu_Code", "Base5"."c11" AS "c11", "Base5"."BIL_Program_Entrance_Code" AS "BIL_Program_Entrance_Code", "Base5"."WorkVisa_Status_Code" AS "WorkVisa_Status_Code", "Base5"."Train_Req_Status_Code" AS "Train_Req_Status_Code", "Base5"."Promotion_Status" AS "Promotion_Status", "Base5"."Branch_Year" AS "Branch_Year", "Base5"."Cert_Type_Code" AS "Cert_Type_Code", "Base5"."Date_of_Entry_to_CurrZip" AS "Date_of_Entry_to_CurrZip", "Base5"."Visa_Employee" AS "Visa_Employee", "Base5"."Employee_Status_Code" AS "Employee_Status_Code", "Base5"."LOC_Type_Code" AS "LOC_Type_Code", "Base5"."Branch_Type_Code" AS "Branch_Type_Code", "Base5"."Review_Status_Code" AS "Review_Status_Code", "Base5"."ZIP_Code_of_Sales" AS "ZIP_Code_of_Sales", "Branchs"."Full_ZIP_Code" AS "ZIP_of_Sales", "Branchs"."Organization_Type_Code" AS "ZIP_of_Sales_Type", "Base5"."County_Code_of_Sales" AS "County_Code_of_Sales", "Base5"."c26" AS "c28", "Base5"."Employee_Name" AS "Employee_Name", "Base5"."Employee_Middle_Initial" AS "Employee_Middle_Initial", "Base5"."Employee_Last_Name" AS "Employee_Last_Name", "Base5"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", "Base5"."Employee_Birth_Date" AS "Employee_Birth_Date", "Base5"."Employee_First_Name" AS "Employee_First_Name" from (select "bupa00"."ARM_TYPE_LONG" AS "Organization_Type", "bupa00"."COUNTY_CODE" AS "County_Code", "bupa00"."COUNTY_NAME" AS "County_Name", "bupa01"."ARM_TYPE_LONG" AS "Branch_Type", case  when "bupa01"."ZIP_ID" = '6554' and "bupa00"."COUNTY_CODE" = '127040504’ then '8086' when "bupa01"."ZIP_ID" = '0654' and "bupa00"."COUNTY_CODE" = '127041904’ then '8044' when "bupa01"."ZIP_ID" = '0685' and "bupa00"."COUNTY_CODE" = '127045454’ then '8042' when "bupa01"."ZIP_ID" = '7156' and "bupa00"."COUNTY_CODE" = '103021904’ then '8094' when "bupa01"."ZIP_ID" = '6946' and "bupa00"."COUNTY_CODE" = '103022104’ then '8087' when "bupa01"."ZIP_ID" = '3277' and "bupa00"."COUNTY_CODE" = '123463804’ then '8049' when "bupa01"."ZIP_ID" = '6711' and "bupa00"."COUNTY_CODE" = '105257602' then '8071' when "bupa01"."ZIP_ID" = '2064’ and "bupa00"."COUNTY_CODE" = '105257602' then '8071' when "bupa01"."ZIP_ID" = '1062' and "bupa00"."COUNTY_CODE" = '122097204’ then '8114' when "bupa01"."ZIP_ID" = '3858' and "bupa00"."COUNTY_CODE" = '126515001' then '8119' when "bupa01"."ZIP_ID" = '0859' and "bupa00"."COUNTY_CODE" = '114067002' then '8116' when "bupa01"."ZIP_ID" = '0712' and "bupa00"."COUNTY_CODE" = '127046904’ then '8000' when "bupa01"."ZIP_ID" = '5246' and "bupa00"."COUNTY_CODE" = '104435604’ then '8094’ when "bupa01"."ZIP_ID" = '0911' and "bupa00"."COUNTY_CODE" = '108070502' then '0914’ when "bupa01"."ZIP_ID" = '3406' and "bupa00"."COUNTY_CODE" = '123468604’ then '5081' when "bupa01"."ZIP_ID" = '6300' and "bupa00"."COUNTY_CODE" = '113365304’ then '2649' when "bupa01"."ZIP_ID" = '6906' and "bupa00"."COUNTY_CODE" = '103028754’ then '0467' when "bupa01"."ZIP_ID" = '6930' and "bupa00"."COUNTY_CODE" = '118403302' then '7348' when "bupa01"."ZIP_ID" = '7074’ and "bupa00"."COUNTY_CODE" = '123468604’ then '5081' when "bupa01"."ZIP_ID" = '7201' and "bupa00"."COUNTY_CODE" = '116191504’ then '1619' when "bupa01"."ZIP_ID" = '7264' and "bupa00"."COUNTY_CODE" = '123468604’ then '5081' when "bupa01"."ZIP_ID" = '8042' and "bupa00"."COUNTY_CODE" = '127045454’ then ' 8044' else "bupa01"."ZIP_ID" end  AS "ZIP_ID", "bupa01"."ZIP_CODE" AS "Full_ZIP_Code", "bupa02"."EMPLOYEE_ID" AS "Employee_ID", "bupa02"."CURR_EXP_LVL" AS "Employee_Exp_Level", "bupa02"."EMPLOYEE_GENDER_CD" AS "Employee_Gender_Code", "bupa02"."ETHNIC_CODE" AS "MaxEdu_Code", "bupa02"."MASTERY_CODE" AS "c11", "bupa02"."LEP_PART_CODE" AS "BIL_Program_Entrance_Code", "bupa02"."WORKVISA_CODE" AS "WorkVisa_Status_Code", "bupa02"."TRAIN_REQ_CODE" AS "Train_Req_Status_Code", left("bupa02"."GRAD_STATUS", 4) AS "Promotion_Status", cast("bupa02"."BRANCH_YEAR" as date) AS "Branch_Year", "bupa02"."CERT_TYPE_CODE" AS "Cert_Type_Code", "bupa02"."ENTRY_DATE_FIRSTZIP" AS "Date_of_Entry_to_CurrZip", "bupa02"."FOREIGN_EMPLOYEE" AS "Visa_Employee", "bupa02"."STATUS_CODE" AS "Employee_Status_Code", "bupa00"."LEVEL_AGGR" AS "LOC_Type_Code", "bupa01"."ARM_TYPE_CODE" AS "Branch_Type_Code", "bupa02"."REVIEW_CODE" AS "Review_Status_Code", "bupa02"."SALES_ZIP" AS "ZIP_Code_of_Sales", "bupa02"."SALES_COUNTY" AS "County_Code_of_Sales", case  when cast("bupa02"."BRANCH_YEAR" as date) = {d '2012-04-15'} then "bupa02"."EMPLOYEE_ID" else NULL end  AS "c26", "bupa02"."EMPLOYEE_NAME" AS "Employee_Name", "bupa02"."EMPLOYEE_MID_INIT" AS "Employee_Middle_Initial", "bupa02"."EMPLOYEE_LAST_NM" AS "Employee_Last_Name", "bupa02"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", upper (left(replace(replace("bupa02"."EMPLOYEE_FIRST_NM", '.', ''), ',', ''), 20)) AS "Employee_First_Name", case  when  NOT "bupa02"."EMP_BIRTHDATE" is null then rtrim(case  when month("bupa02"."EMP_BIRTHDATE") < 10 then '0' else '' end +convert(char,(month("bupa02"."EMP_BIRTHDATE") * 1000000 + day("bupa02"."EMP_BIRTHDATE") * 10000) + year("bupa02"."EMP_BIRTHDATE"))) else NULL end  AS "Employee_Birth_Date" from "DBO"."COUNTY" "bupa00", "DBO"."ZIP" "bupa01", "CONSTRCT"."HOD"."tblEmployeeMLEVEL0987" "bupa02", (select distinct "Except1"."Employee_ID" AS "Employee_ID" from (select distinct "Employee"."EMPLOYEE_ID" AS "Employee_ID" from "DBO"."COUNTY" "County", "DBO"."ZIP" "ZIP", "CONSTRCT"."HOD"."tblEmployeeMLEVEL0987" "Employee" where (CAST( CONVERT( CHAR(10), cast("Employee"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -3, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("Employee"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -2, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("Employee"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -1, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("Employee"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), convert(DATE, {d '2012-04-15'}), 121 ) AS VARCHAR(20) )) and "Employee"."CURR_EXP_LVL" in (‘09’, ‘00’, ‘0H’, ‘0G’, ‘SG’) and ("Employee"."STATUS_CODE" is null or "Employee"."STATUS_CODE" <> 'A') and ("County"."ARM_TYPE_LONG" in (‘SR’, ‘U1’, ‘TS’, 'SJCI') or "ZIP"."ARM_TYPE_CODE" = 'TOMCKT') and "County"."LEVEL_AGGR"  NOT  in (‘AS’, ‘PI’,’RI’) and "County"."COUNTY_CODE" in ('100510000', '101260304’, '101260804’, '101261302', '101262507', '101262904’, '101264004’, '101266007', '101268004’, '101301304’, '101301404’, '101302607', '101303504’, '101306504’, '101308504’, '101630504', '101630904’, '101631004’, '101631204’, '101631504’, '101631704’, '101631804’, '101631904’, ‘301632404’, ‘301633904’, ‘301634207', ‘301636504’, ‘301637002', ‘301638004’, ‘301638804’, ‘301638907', ‘301833400', ‘302020001', ‘302020002', ‘302020004’, ‘302023030', ‘302023080', ‘302023180', ‘302025007', ‘302027451', ‘303020001', ‘303020002', ‘303020004’, ‘303020004', ‘303020005', ‘303020407', ‘303020604’, ‘303020754’, ‘303021004’, ‘303021102', ‘303021252', ‘303021454’, ‘303021604’, ‘303021752', ‘303021904’, ‘303022104’, ‘303022254’, ‘303022481', ‘303022504’, ‘303022804’, ‘303023090', ‘303023154’, ‘303023410', ‘303023807', ‘303023912', ‘303024102', ‘303024604’, ‘303024754’, ‘303025002', ‘303025206', ‘303026002', ‘303026037', ‘303026304’, ‘303026344’, ‘303026402', ‘303026852', ‘303026874’, ‘303026902', ‘303027307', ‘303027352', ‘303027504’, ‘303027754’, ‘303028192', ‘303028204’, ‘303028246', ‘303028302', ‘303028654’, ‘303028704’, ‘303028754’, ‘303028807', ‘303028834’, ‘303028854’, ‘303029204’, ‘303029404’, ‘303029554’, ‘303029604’, ‘303029804’, ‘303029902', ‘303519376', ‘304101252', ‘304101307', ‘304103604’, ‘304105004’, ‘304105354’, ‘304107504’, ‘304107804’, ‘304107904’, ‘304372004’, ‘304374004’, ‘304374207', ‘304375004’, ‘304375204’, ‘304375302', ‘304376204’, ‘304377004’, ‘304378004’, ‘304431304', ‘304432504’, ‘304432804’, ‘304432830', ‘304432904’, ‘304433304’, ‘304433604', ‘304433904’, ‘304435004’, ‘304435107', ‘304435304’, ‘304435604’, ‘304435704’, ‘304437504’, ‘304510394', ‘305201034’, ‘305201352', ‘305201407', ‘305204704’, ‘305250001', ‘305250004', ‘305251454’, ‘305252507', ‘305252602', ‘305252807', ‘305252920', ‘305253304’, ‘305253554’, ‘305253904’, ‘305254054’, ‘305254354’, ‘305256554’, ‘305257512', ‘305257602', ‘305258304’, ‘305258504’, ‘305259104’, ‘305259704’, ‘305620001', ‘305628007', ‘305628302', ‘306160304’, ‘306161204’, ‘306161357', ‘306161704’, ‘306166504’, ‘306167504', ‘306168004’, ‘306169004’, ‘306172004’, ‘306272004’, ‘306330704’, ‘306330804’, ‘306333407', ‘306338004’, ‘306611304’, ‘306612204’, ‘306616204’, ‘306617204’, ‘306618604’, ‘306619107', ‘307650604’, ‘307650704’, ‘307651207', ‘307651604’, ‘307652207', ‘307652604’, ‘307653040', ‘307653102', ‘307653204’, ‘307653802', ‘307654104’, ‘307654404’, ‘307654904’, ‘307655804’, ‘307655904’, ‘307656304’, ‘307656407', ‘307656502', ‘307657104’, ‘307657504’, ‘307658904’, ‘308051004’, ‘308051307', ‘308051504’, ‘308053004’, ‘308056004', ‘308057079', ‘308058004’, ‘308070001', ‘308070502', ‘308070607', ‘308071004’, ‘308071504', ‘308073504’, ‘308077504’, ‘308078004’, ‘308079004', ‘308110307', ‘308110604’, ‘308111204’, ‘308111304’, ‘308111404’, ‘308112004’, ‘308112204’, ‘308112502', ‘308112607', ‘308114504’, ‘308116004’, ‘308116304’, ‘308116504’, ‘308118504’, ‘308515107', ‘308561004’, ‘308561804’, ‘308565204’, ‘308565504’, ‘308566304’, ‘308567004', ‘308567204', ‘308567404', ‘308567704’, ‘308567807', ‘308568404', ‘308569104’, ‘309122704’, ‘309243504’, ‘309246004’, ‘309248004’, ‘309420107', ‘309420804’, ‘309422304’, ‘309426004’, ‘309426304’, ‘309427504’, ‘309530304', ‘309531304', ‘309532804', ‘309535504', ‘309537504', ‘310140001', ‘310141004’, ‘310141104’, ‘310141607', ‘310143060', ‘310143120', ‘310143310', ‘310147004’, ‘310148002', ‘310171004’, ‘310171607', ‘310171804’, ‘310173004’, ‘310173504', ‘310175004’, ‘310177004’, ‘310179004’, ‘310183602', ‘310183707', ‘311291304', ‘311292304', ‘311292507', ‘311297504', ‘311312504’, ‘311312607', ‘311312804', ‘311315438', ‘311316004’, ‘311317504’, ‘311343604’, ‘311440001', ‘311444207', ‘311444602', ‘312009104’, ‘312009604’, ‘312013054', ‘312013754’, ‘312015204’, ‘312018524’, ‘312281302', ‘312282004', ‘312282307', ‘312283004’, ‘312286004’, ‘312289004’, ‘312671304’, ‘312671604’, ‘312671804’, ‘312672204’, ‘312672804’, ‘312673300', ‘312673500', ‘312674404’, ‘312675504’, ‘312676204’, ‘312676404’, ‘312676504’, ‘312676704’, ‘312678504’, ‘312679002', ‘312679107', ‘312679404’, ‘313361304’, ‘313361504’, ‘313361704’, ‘313362204’, ‘313362304’, ‘313362404’, ‘313362604’, ‘313362940', ‘313363104’, ‘313363604’, ‘313363807', ‘313364002', ‘313364404’, ‘313364504’, ‘313365204’, ‘313365304’, ‘313367004’, ‘313369004’, ‘313380304’, ‘313381304’, ‘313382304’, ‘313384307', ‘313384604’, ‘313385004’, ‘313385304’, ‘314060392', ‘314060504’, ‘314060557', ‘314060754’, ‘314060854’, ‘314061104’, ‘314061504’, ‘314062004’, ‘314062504’, ‘314063004’, ‘314063504’, ‘314064004’, ‘314065504’, ‘314066504’, ‘314067002', ‘314067107', ‘314067504’, ‘314068004’, ‘314068104’, ‘314069104’, ‘314069354’, ‘314514135', ‘315210504’, ‘315211004’, ‘315211104’, ‘315211604’, ‘315211657', ‘315212504’, ‘315216504’, ‘315218004’, ‘315218304’, ‘315219002', ‘315220001', ‘315220002', ‘315220004’, ‘315221402', ‘315221607', ‘315221754’, ‘315222504', ‘315222752', ‘315223050', ‘315224004’, ‘315226004’, ‘315226104’, ‘315228004’, ‘315228304’, ‘315229004’, ‘315503004', ‘315504004’, ‘315506004’, ‘315508004’, ‘315674604’, ‘316191004', ‘316191104’, ‘316191204’, ‘316191504’, ‘316191757', ‘316195004', ‘316197504’, ‘316471804’, ‘316493130', ‘316493504’, ‘316495004’, ‘316495104’, ‘316495207', ‘316496504’, ‘316496604’, ‘316498004’, ‘316555004’, ‘316557104’, ‘316604004’, ‘316605004’, ‘316606707', ‘317080504’, ‘317080607', ‘317081004’, ‘317083004', ‘317086004’, ‘317086504’, ‘317086654’, ‘317089004’, ‘317412004’, ‘317414004’, ‘317414204’, ‘317414807', ‘317415004', ‘317415104’, ‘317415304’, ‘317416104’, ‘317417202', ‘317576304’, ‘317596004’, ‘317597004’, ‘317598504’, ‘318400001', ‘318401404’, ‘318401604’, ‘318402604’, ‘318403004’, ‘318403207', ‘318403302', ‘318403904’, ‘318406004’, ‘318406602', ‘318408607', ‘318408707', ‘318408852', ‘318409204’, ‘318409302', ‘318667504’, ‘319350001', ‘319350304’, ‘319351304’, ‘319352204’, ‘319354207', ‘319354604’, ‘319355504’, ‘319356504’, ‘319356604’, ‘319357004’, ‘319357402', ‘319358404’, ‘319581004’, ‘319582504’, ‘319583004’, ‘319584504’, ‘319584604’, ‘319584707', ‘319586504’, ‘319648304’, ‘319648704’, ‘319648904’, ‘319665004’, ‘320450002', ‘320450004’, ‘320452004’, ‘320454507', ‘320455204’, ‘320455404’, ‘320456004’, ‘320480001', ‘320480002', ‘320480804’, ‘320481002', ‘320481107', ‘320483007', ‘320483170', ‘320483302', ‘320484804’, ‘320484904’, ‘320485604’, ‘320486004’, ‘320488604’, ‘320522004’, ‘321131507', ‘321135004’, ‘321135504’, ‘321136504’, ‘321136604’, ‘321139004', ‘321390302', ‘321391304’, ‘321392304’, ‘321393007', ‘321393330', ‘321394504’, ‘321394604’, ‘321395104’, ‘321395604’, ‘321395704’, ‘321397804’, ‘322090001', ‘322091002', ‘322091304’, ‘322091352', ‘322091457', ‘322092002', ‘322092102', ‘322092354’, ‘322093140', ‘322093460', ‘322097007', ‘322097204’, ‘322097502', ‘322097604', ‘322098004’, ‘322098104’, ‘322098202', ‘322098404’, ‘322099007', ‘323460001', ‘323460302', ‘323460504', ‘323460957', ‘323461302', ‘323461602', ‘323463370', ‘323463507', ‘323463604’, ‘323463804’, ‘323464502', ‘323464604’, ‘323465304’, ‘323465507', ‘323465602', ‘323465702', ‘323466104’, ‘323466304’, ‘323466404’, ‘323467104’, ‘323467204’, ‘323467304’, ‘323468304’, ‘323468402', ‘323468504’, ‘323468604’, ‘323469007', ‘323469304’, ‘324150001', ‘324150002', ‘324150004’, ‘324150004', ‘324150005', ‘324150504’, ‘324151607', ‘324151902', ‘324152004’, ‘324152880', ‘324153320', ‘324153350', ‘324153504’, ‘324153510', ‘324154004’, ‘324156504’, ‘324156604’, ‘324156704’, ‘324157204’, ‘324157802', ‘324158504’, ‘324159002', ‘325230001', ‘325230002', ‘325231232', ‘325231304’, ‘325232407', ‘325232900', ‘325232950', ‘325234104’, ‘325234502', ‘325235104’, ‘325235502', ‘325236904’, ‘325237604’, ‘325237702', ‘325237904’, ‘325238402', ‘325238502', ‘325239452', ‘325239604’, ‘325239652', ‘326510001', ‘326510002', ‘326510004’, ‘326510004', ‘326510005', ‘326510006', ‘326510007', ‘326510008', ‘326510009', ‘326510010', ‘326510011', ‘326510014’, ‘326510014', ‘326510015', ‘326510016', ‘326510017', ‘326510018', ‘326510019', ‘326510020', ‘326510021', ‘326510022', ‘326510024’, ‘326511748', ‘326512840', ‘326512850', ‘326512860', ‘326512870', ‘326512960', ‘326512980', ‘326512990', ‘326513000', ‘326513020', ‘326513070', ‘326513100', ‘326513110', ‘326513150', ‘326513160', ‘326513190', ‘326513200', ‘326513210', ‘326513220', ‘326513230', ‘326513240', ‘326513250', ‘326513260', ‘326513270', ‘326513280', ‘326513290', ‘326513380', ‘326513400', ‘326513415', ‘326513420', ‘326513440', ‘326513450', ‘326513452', ‘326513470', ‘326513480', ‘326513490', ‘326513510', ‘326513734', ‘326514007', ‘326515001', ‘326516457', ‘326517286', ‘326517648', ‘326518547', ‘326519434’, ‘326519434', ‘326519476', ‘327040001', ‘327040002', ‘327040504’, ‘327040704’, ‘327041204’, ‘327041307', ‘327041504’, ‘327041604’, ‘327041904’, ‘327042004’, ‘327042854’, ‘327043430', ‘327044104’, ‘327045304’, ‘327045454’, ‘327045654’, ‘327045854’, ‘327046904’, ‘327047404', ‘327049304’, ‘328030604’, ‘328030852', ‘328033054’, ‘328034504’, ‘328034607', ‘328321104’, ‘328323304’, ‘328323704’, ‘328324207', ‘328325204’, ‘328326304’, ‘328327304’, ‘328328004’, ‘329540804’, ‘329544504’, ‘329544704’, ‘329544907', ‘329545004’, ‘329546004’, ‘329546104’, ‘329546804’, ‘329546907', ‘329547204’, ‘329547304’, ‘329547604’, ‘329547804’, ‘329548804’, ‘333513315', ‘334677866', ‘339481451', ‘341019741', ‘347513704’, ‘351514721', ‘360028259', ‘368513758', ‘368518014’, ‘371510294’, ‘372510794’, ‘373515368', ‘375390169', ‘377518712', ‘381519176', ‘381677919', ‘382514568', ‘385515524’, ‘388392660', ‘389670676', ‘392518422', ‘397010542', ‘399025446') and "Employee"."ENTRY_DATE_FIRSTZIP" > convert(datetime2, '2009-04-15 00:00:00.000000000') and "Employee"."ENTRY_DATE_FIRSTZIP" <= convert(datetime2, '2009-04-15 00:00:00.000000000') and "County"."COUNTY_KEY" = "Employee"."COUNTY_KEY" and "ZIP"."ZIP_KEY" = "Employee"."ZIP_KEY" and (235005004 = 99999999999 or 235005004 = 235005004 or "County"."COUNTY_CODE" = 315220093) except select distinct "Union1"."Employee_ID" AS "Employee_ID" from (select distinct "Employee"."EMPLOYEE_ID" AS "Employee_ID" from "CONSTRCT"."HOD"."tblEmployeeMLEVEL0987" "Employee", "DBO"."KNOWLEDGE_INFO" "Knowledge_Attributes", "DBO"."KNOWLEDGE_FACT" "Knowledge_Passdown_Scores" where "Knowledge_Attributes"."PEER_GROUP" = 'NASDUR' and (cast("Knowledge_Passdown_Scores"."BRANCH_YEAR" as date) = DATEADD( YEAR, -3, convert(DATE, {d '2012-04-15'}) ) or cast("Knowledge_Passdown_Scores"."BRANCH_YEAR" as date) = DATEADD( YEAR, -2, convert(DATE, {d '2012-04-15'}) ) or cast("Knowledge_Passdown_Scores"."BRANCH_YEAR" as date) = DATEADD( YEAR, -1, convert(DATE, {d '2012-04-15'}) ) or cast("Knowledge_Passdown_Scores"."BRANCH_YEAR" as date) = {d '2012-04-15'}) and "Employee"."EMPLOYEE_KEY" = "Knowledge_Passdown_Scores"."EMPLOYEE_KEY" and "Knowledge_Attributes"."TEST_KEY" = "Knowledge_Passdown_Scores"."TEST_KEY" union  select "MLEVEL_PROMOTION_RATE_FINAL"."EMPLOYEE_ID" AS "Employee_ID" from "CONSTRCT"."HOD"."tblEmployeeMLEVEL0987" "MLEVEL_PROMOTION_RATE_FINAL" where "MLEVEL_PROMOTION_RATE_FINAL"."CURR_EXP_LVL" in (‘09’, ‘00’, ‘0H’, ‘0G’, ‘SG’) and "MLEVEL_PROMOTION_RATE_FINAL"."STATUS_CODE" in (‘R’, ‘C’) and "MLEVEL_PROMOTION_RATE_FINAL"."ENTRY_DATE_FIRSTZIP" > convert(datetime2, '2009-04-15 00:00:00.000000000') and "MLEVEL_PROMOTION_RATE_FINAL"."ENTRY_DATE_FIRSTZIP" <= convert(datetime2, '2009-04-15 00:00:00.000000000') and (cast("MLEVEL_PROMOTION_RATE_FINAL"."BRANCH_YEAR" as date) = DATEADD( YEAR, -3, convert(DATE, {d '2012-04-15'}) ) or cast("MLEVEL_PROMOTION_RATE_FINAL"."BRANCH_YEAR" as date) = DATEADD( YEAR, -2, convert(DATE, {d '2012-04-15'}) ) or cast("MLEVEL_PROMOTION_RATE_FINAL"."BRANCH_YEAR" as date) = DATEADD( YEAR, -1, convert(DATE, {d '2012-04-15'}) ) or cast("MLEVEL_PROMOTION_RATE_FINAL"."BRANCH_YEAR" as date) = {d '2012-04-15'})) "Union1"("Employee_ID")) "Except1"("Employee_ID")) "Avoid_IDs" where "bupa02"."EMPLOYEE_ID" = "Avoid_IDs"."Employee_ID" and (CAST( CONVERT( CHAR(10), cast("bupa02"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -3, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("bupa02"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -2, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("bupa02"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -1, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("bupa02"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), convert(DATE, {d '2012-04-15'}), 121 ) AS VARCHAR(20) )) and "bupa02"."CURR_EXP_LVL" in (‘09’, ‘00’, ‘0H’, ‘0G’, ‘SG’) and ("bupa02"."STATUS_CODE" is null or "bupa02"."STATUS_CODE" <> 'A') and ("bupa00"."ARM_TYPE_LONG" in (‘SR’, ‘U1’, ‘TS’, 'SJCI') or "bupa01"."ARM_TYPE_CODE" = 'TOMCKT') and "bupa00"."LEVEL_AGGR"  NOT  in (‘AS’, ‘PI’,’RI’) and "bupa00"."COUNTY_CODE" in (‘300510000', ‘301260304’, ‘301260804’, ‘301261302', ‘301262507', ‘301262904’, ‘301264004’, ‘301266007', ‘301268004’, ‘301301304’, ‘301301404’, ‘301302607', ‘301303504’, ‘301306504’, ‘301308504’, ‘301630504', ‘301630904’, ‘301631004’, ‘301631204’, ‘301631504’, ‘301631704’, ‘301631804’, ‘301631904’, ‘301632404’, ‘301633904’, ‘301634207', ‘301636504’, ‘301637002', ‘301638004’, ‘301638804’, ‘301638907', ‘301833400', ‘302020001', ‘302020002', ‘302020004’, ‘302023030', ‘302023080', ‘302023180', ‘302025007', ‘302027451', ‘303020001', ‘303020002', ‘303020004’, ‘303020004', ‘303020005', ‘303020407', ‘303020604’, ‘303020754’, ‘303021004’, ‘303021102', ‘303021252', ‘303021454’, ‘303021604’, ‘303021752', ‘303021904’, ‘303022104’, ‘303022254’, ‘303022481', ‘303022504’, ‘303022804’, ‘303023090', ‘303023154’, ‘303023410', ‘303023807', ‘303023912', ‘303024102', ‘303024604’, ‘303024754’, ‘303025002', ‘303025206', ‘303026002', ‘303026037', ‘303026304’, ‘303026344’, ‘303026402', ‘303026852', ‘303026874’, ‘303026902', ‘303027307', ‘303027352', ‘303027504’, ‘303027754’, ‘303028192', ‘303028204’, ‘303028246', ‘303028302', ‘303028654’, ‘303028704’, ‘303028754’, ‘303028807', ‘303028834’, ‘303028854’, ‘303029204’, ‘303029404’, ‘303029554’, ‘303029604’, ‘303029804’, ‘303029902', ‘303519376', ‘304101252', ‘304101307', ‘304103604’, ‘304105004’, ‘304105354’, ‘304107504’, ‘304107804’, ‘304107904’, ‘304372004’, ‘304374004’, ‘304374207', ‘304375004’, ‘304375204’, ‘304375302', ‘304376204’, ‘304377004’, ‘304378004’, ‘304431304', ‘304432504’, ‘304432804’, ‘304432830', ‘304432904’, ‘304433304’, ‘304433604', ‘304433904’, ‘304435004’, ‘304435107', ‘304435304’, ‘304435604’, ‘304435704’, ‘304437504’, ‘304510394', ‘305201034’, ‘305201352', ‘305201407', ‘305204704’, ‘305250001', ‘305250004', ‘305251454’, ‘305252507', ‘305252602', ‘305252807', ‘305252920', ‘305253304’, ‘305253554’, ‘305253904’, ‘305254054’, ‘305254354’, ‘305256554’, ‘305257512', ‘305257602', ‘305258304’, ‘305258504’, ‘305259104’, ‘305259704’, ‘305620001', ‘305628007', ‘305628302', ‘306160304’, ‘306161204’, ‘306161357', ‘306161704’, ‘306166504’, ‘306167504', ‘306168004’, ‘306169004’, ‘306172004’, ‘306272004’, ‘306330704’, ‘306330804’, ‘306333407', ‘306338004’, ‘306611304’, ‘306612204’, ‘306616204’, ‘306617204’, ‘306618604’, ‘306619107', ‘307650604’, ‘307650704’, ‘307651207', ‘307651604’, ‘307652207', ‘307652604’, ‘307653040', ‘307653102', ‘307653204’, ‘307653802', ‘307654104’, ‘307654404’, ‘307654904’, ‘307655804’, ‘307655904’, ‘307656304’, ‘307656407', ‘307656502', ‘307657104’, ‘307657504’, ‘307658904’, ‘308051004’, ‘308051307', ‘308051504’, ‘308053004’, ‘308056004', ‘308057079', ‘308058004’, ‘308070001', ‘308070502', ‘308070607', ‘308071004’, ‘308071504', ‘308073504’, ‘308077504’, ‘308078004’, ‘308079004', ‘308110307', ‘308110604’, ‘308111204’, ‘308111304’, ‘308111404’, ‘308112004’, ‘308112204’, ‘308112502', ‘308112607', ‘308114504’, ‘308116004’, ‘308116304’, ‘308116504’, ‘308118504’, ‘308515107', ‘308561004’, ‘308561804’, ‘308565204’, ‘308565504’, ‘308566304’, ‘308567004', ‘308567204', ‘308567404', ‘308567704’, ‘308567807', ‘308568404', ‘308569104’, ‘309122704’, ‘309243504’, ‘309246004’, ‘309248004’, ‘309420107', ‘309420804’, ‘309422304’, ‘309426004’, ‘309426304’, ‘309427504’, ‘309530304', ‘309531304', ‘309532804', ‘309535504', ‘309537504', ‘310140001', ‘310141004’, ‘310141104’, ‘310141607', ‘310143060', ‘310143120', ‘310143310', ‘310147004’, ‘310148002', ‘310171004’, ‘310171607', ‘310171804’, ‘310173004’, ‘310173504', ‘310175004’, ‘310177004’, ‘310179004’, ‘310183602', ‘310183707', ‘311291304', ‘311292304', ‘311292507', ‘311297504', ‘311312504’, ‘311312607', ‘311312804', ‘311315438', ‘311316004’, ‘311317504’, ‘311343604’, ‘311440001', ‘311444207', ‘311444602', ‘312009104’, ‘312009604’, ‘312013054', ‘312013754’, ‘312015204’, ‘312018524’, ‘312281302', ‘312282004', ‘312282307', ‘312283004’, ‘312286004’, ‘312289004’, ‘312671304’, ‘312671604’, ‘312671804’, ‘312672204’, ‘312672804’, ‘312673300', ‘312673500', ‘312674404’, ‘312675504’, ‘312676204’, ‘312676404’, ‘312676504’, ‘312676704’, ‘312678504’, ‘312679002', ‘312679107', ‘312679404’, ‘313361304’, ‘313361504’, ‘313361704’, ‘313362204’, ‘313362304’, ‘313362404’, ‘313362604’, ‘313362940', ‘313363104’, ‘313363604’, ‘313363807', ‘313364002', ‘313364404’, ‘313364504’, ‘313365204’, ‘313365304’, ‘313367004’, ‘313369004’, ‘313380304’, ‘313381304’, ‘313382304’, ‘313384307', ‘313384604’, ‘313385004’, ‘313385304’, ‘314060392', ‘314060504’, ‘314060557', ‘314060754’, ‘314060854’, ‘314061104’, ‘314061504’, ‘314062004’, ‘314062504’, ‘314063004’, ‘314063504’, ‘314064004’, ‘314065504’, ‘314066504’, ‘314067002', ‘314067107', ‘314067504’, ‘314068004’, ‘314068104’, ‘314069104’, ‘314069354’, ‘314514135', ‘315210504’, ‘315211004’, ‘315211104’, ‘315211604’, ‘315211657', ‘315212504’, ‘315216504’, ‘315218004’, ‘315218304’, ‘315219002', ‘315220001', ‘315220002', ‘315220004’, ‘315221402', ‘315221607', ‘315221754’, ‘315222504', ‘315222752', ‘315223050', ‘315224004’, ‘315226004’, ‘315226104’, ‘315228004’, ‘315228304’, ‘315229004’, ‘315503004', ‘315504004’, ‘315506004’, ‘315508004’, ‘315674604’, ‘316191004', ‘316191104’, ‘316191204’, ‘316191504’, ‘316191757', ‘316195004', ‘316197504’, ‘316471804’, ‘316493130', ‘316493504’, ‘316495004’, ‘316495104’, ‘316495207', ‘316496504’, ‘316496604’, ‘316498004’, ‘316555004’, ‘316557104’, ‘316604004’, ‘316605004’, ‘316606707', ‘317080504’, ‘317080607', ‘317081004’, ‘317083004', ‘317086004’, ‘317086504’, ‘317086654’, ‘317089004’, ‘317412004’, ‘317414004’, ‘317414204’, ‘317414807', ‘317415004', ‘317415104’, ‘317415304’, ‘317416104’, ‘317417202', ‘317576304’, ‘317596004’, ‘317597004’, ‘317598504’, ‘318400001', ‘318401404’, ‘318401604’, ‘318402604’, ‘318403004’, ‘318403207', ‘318403302', ‘318403904’, ‘318406004’, ‘318406602', ‘318408607', ‘318408707', ‘318408852', ‘318409204’, ‘318409302', ‘318667504’, ‘319350001', ‘319350304’, ‘319351304’, ‘319352204’, ‘319354207', ‘319354604’, ‘319355504’, ‘319356504’, ‘319356604’, ‘319357004’, ‘319357402', ‘319358404’, ‘319581004’, ‘319582504’, ‘319583004’, ‘319584504’, ‘319584604’, ‘319584707', ‘319586504’, ‘319648304’, ‘319648704’, ‘319648904’, ‘319665004’, ‘320450002', ‘320450004’, ‘320452004’, ‘320454507', ‘320455204’, ‘320455404’, ‘320456004’, ‘320480001', ‘320480002', ‘320480804’, ‘320481002', ‘320481107', ‘320483007', ‘320483170', ‘320483302', ‘320484804’, ‘320484904’, ‘320485604’, ‘320486004’, ‘320488604’, ‘320522004’, ‘321131507', ‘321135004’, ‘321135504’, ‘321136504’, ‘321136604’, ‘321139004', ‘321390302', ‘321391304’, ‘321392304’, ‘321393007', ‘321393330', ‘321394504’, ‘321394604’, ‘321395104’, ‘321395604’, ‘321395704’, ‘321397804’, ‘322090001', ‘322091002', ‘322091304’, ‘322091352', ‘322091457', ‘322092002', ‘322092102', ‘322092354’, ‘322093140', ‘322093460', ‘322097007', ‘322097204’, ‘322097502', ‘322097604', ‘322098004’, ‘322098104’, ‘322098202', ‘322098404’, ‘322099007', ‘323460001', ‘323460302', ‘323460504', ‘323460957', ‘323461302', ‘323461602', ‘323463370', ‘323463507', ‘323463604’, ‘323463804’, ‘323464502', ‘323464604’, ‘323465304’, ‘323465507', ‘323465602', ‘323465702', ‘323466104’, ‘323466304’, ‘323466404’, ‘323467104’, ‘323467204’, ‘323467304’, ‘323468304’, ‘323468402', ‘323468504’, ‘323468604’, ‘323469007', ‘323469304’, ‘324150001', ‘324150002', ‘324150004’, ‘324150004', ‘324150005', ‘324150504’, ‘324151607', ‘324151902', ‘324152004’, ‘324152880', ‘324153320', ‘324153350', ‘324153504’, ‘324153510', ‘324154004’, ‘324156504’, ‘324156604’, ‘324156704’, ‘324157204’, ‘324157802', ‘324158504’, ‘324159002', ‘325230001', ‘325230002', ‘325231232', ‘325231304’, ‘325232407', ‘325232900', ‘325232950', ‘325234104’, ‘325234502', ‘325235104’, ‘325235502', ‘325236904’, ‘325237604’, ‘325237702', ‘325237904’, ‘325238402', ‘325238502', ‘325239452', ‘325239604’, ‘325239652', ‘326510001', ‘326510002', ‘326510004’, ‘326510004', ‘326510005', ‘326510006', ‘326510007', ‘326510008', ‘326510009', ‘326510010', ‘326510011', ‘326510014’, ‘326510014', ‘326510015', ‘326510016', ‘326510017', ‘326510018', ‘326510019', ‘326510020', ‘326510021', ‘326510022', ‘326510024’, ‘326511748', ‘326512840', ‘326512850', ‘326512860', ‘326512870', ‘326512960', ‘326512980', ‘326512990', ‘326513000', ‘326513020', ‘326513070', ‘326513100', ‘326513110', ‘326513150', ‘326513160', ‘326513190', ‘326513200', ‘326513210', ‘326513220', ‘326513230', ‘326513240', ‘326513250', ‘326513260', ‘326513270', ‘326513280', ‘326513290', ‘326513380', ‘326513400', ‘326513415', ‘326513420', ‘326513440', ‘326513450', ‘326513452', ‘326513470', ‘326513480', ‘326513490', ‘326513510', ‘326513734', ‘326514007', ‘326515001', ‘326516457', ‘326517286', ‘326517648', ‘326518547', ‘326519434’, ‘326519434', ‘326519476', ‘327040001', ‘327040002', ‘327040504’, ‘327040704’, ‘327041204’, ‘327041307', ‘327041504’, ‘327041604’, ‘327041904’, ‘327042004’, ‘327042854’, ‘327043430', ‘327044104’, ‘327045304’, ‘327045454’, ‘327045654’, ‘327045854’, ‘327046904’, ‘327047404', ‘327049304’, ‘328030604’, ‘328030852', ‘328033054’, ‘328034504’, ‘328034607', ‘328321104’, ‘328323304’, ‘328323704’, ‘328324207', ‘328325204’, ‘328326304’, ‘328327304’, ‘328328004’, ‘329540804’, ‘329544504’, ‘329544704’, ‘329544907', ‘329545004’, ‘329546004’, ‘329546104’, ‘329546804’, ‘329546907', ‘329547204’, ‘329547304’, ‘329547604’, ‘329547804’, ‘329548804’, ‘333513315', ‘334677866', ‘339481451', ‘341019741', ‘347513704’, ‘351514721', ‘360028259', ‘368513758', ‘368518014’, ‘371510294’, ‘372510794’, ‘373515368', ‘375390169', ‘377518712', ‘381519176', ‘381677919', ‘382514568', ‘385515524’, ‘388392660', ‘389670676', ‘392518422', ‘397010542', ‘399025446') and "bupa02"."ENTRY_DATE_FIRSTZIP" > convert(datetime2, '2009-04-15 00:00:00.000000000') and "bupa02"."ENTRY_DATE_FIRSTZIP" <= convert(datetime2, '2009-04-15 00:00:00.000000000') and "bupa00"."COUNTY_KEY" = "bupa02"."COUNTY_KEY" and "bupa01"."ZIP_KEY" = "bupa02"."ZIP_KEY" and (235005004 = 111111111 or 235005004 = 235005004 or "bupa00"."COUNTY_CODE" = 315220093)) "Base5" LEFT OUTER JOIN (select distinct "County"."COUNTY_CODE" AS "County_Code", case  when "ZIP"."ZIP_ID" = '6554' and "County"."COUNTY_CODE" = ‘327040504’ then '8086' when "ZIP"."ZIP_ID" = '0654' and "County"."COUNTY_CODE" = ‘327041904’ then '8044' when "ZIP"."ZIP_ID" = '0685' and "County"."COUNTY_CODE" = ‘327045454’ then '8042' when "ZIP"."ZIP_ID" = '7156' and "County"."COUNTY_CODE" = ‘303021904’ then '8094' when "ZIP"."ZIP_ID" = '6946' and "County"

Open in new window

0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 38793459
Your extensive use of case statements and IN () clauses will significantly slow down this query.

For one, I would create a table that contains your Zip, County Codes, and the Zip_ID, which you can join to one of your tables to get the Zip_ID associated with the zipcode and County_Code.

I would also create a table of County Codes that you want to include in the result set, and join that table to one of your "bupa00"."COUNTY_CODE" to determine which counties to include in your result set, rather than using the IN clause.  The IN clause can be very slow, especially when you have so many values.  To do this, I generally add a column (IncludeThis - Yes/No) in my tables that might be used to filter query results, then the user identifies each column to include by changing IncludeThis to True.  Then, when you join the table to bupa00.County_Code, you simply have to use a WHERE clause similar to:

WHERE County_Code.IncludeThis = True
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1200 total points
ID: 38793542
Interesting.  I've never seen a 42K query source on one line.  I'm assuming that the query was produced by a "query generator"?

The first thing that I'd want is an explain plan.  Let's see what the query is doing and where it's spending its time.

- The IN() clause can be expensive, depending on several factors.
- All of the recasting in the filter clause can nullifying the use of indexes.
- There are a lot of sub-selects in the filter and implied joins in the filter.

My instincts tell me that there's a better way to write this query.  But without knowing the schema and your needs, it's just conjecture.


Kent
0
 
LVL 5

Author Comment

by:25112
ID: 38794511
thanks for the tip to over come the IN issue..

Yes, this is generated. That is what we would like changed and working with the vendor on that.

You mentioned 3 things that are done in the filter: recast, sub-selects, implied joins. Would the idea be to bring these 3 before the FILTER (where condition) and not after it?
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1200 total points
ID: 38794555
The best would be to have the data types of the objects in the filter identical to the object times in the index(es).

But that's not always possible, so recast the items in the query to match the data type of the indexed columns.


Kent
0
 
LVL 16

Accepted Solution

by:
theo kouwenhoven earned 400 total points
ID: 38798731
Hi  25112,

Kdo is right, the IN() clause (build by a select) can assume a lot of time.
Most of the times a count is much faster:

e.g.

Select field1 from my table1
 where field1 in (Select field2 from table2 where  field2 = field1)


Faster is:
Select field1 from my table1
 where  (Select count(*) from table2 where field2 = field1) > 0


All other stuff that require joining, selecting and sequencing without a usable DbKey is slowdown the process dramatically (but you only aware of that in case of larger tables)
0
 
LVL 5

Author Comment

by:25112
ID: 38836418
thanks a lot.
0

Featured Post

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question