Solved

SELECT statement and CPU usage

Posted on 2013-01-18
8
327 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 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 300 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 (Access MVP)
Dale Fye (Access MVP) earned 100 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
Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 300 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 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 300 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 100 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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

691 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