Solved

SELECT statement and CPU usage

Posted on 2013-01-18
8
326 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 47

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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

733 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