Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SELECT statement and CPU usage

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

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

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

Assisted Solution

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

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


Kent
0
 
LVL 5

Author Comment

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

Open in new window

0
 
LVL 48

Assisted Solution

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

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

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

WHERE County_Code.IncludeThis = True
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Assisted Solution

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

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

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

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


Kent
0
 
LVL 5

Author Comment

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

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

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

Assisted Solution

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

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


Kent
0
 
LVL 16

Accepted Solution

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

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

e.g.

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


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


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

Author Comment

by:25112
ID: 38836418
thanks a lot.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

609 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