Hey all, I'm working to integrate 4.2 into my code base and I've run into a snag. It appears that some queries are slower and what I'm finding is that when I have CHAR columns, that it ends up doing a join that is RTRIM(column) = RTRIM(other column). This results in the query optimizer not using an index (because it has to rtrim all values before comparison).
Here is an example from a disjointed prefetch: 4.1.B2-SNAPSHOT: 👍 SELECT DISTINCT [t0].[average_cost], [t0].[backorder_flag], [t0].[break_match_code], [t0].[case_location], [t0].[case_qty], [t0].[category_code], [t0].[cgs_gl_account], [t0].[charges_group_code], [t0].[composition_family], [t0].[composition_output_definition], [t0].[custom_vendor], [t0].[description], [t0].[drop_ship_code], [t0].[duties_percent], [t0].[duties_tax_cost_percent], [t0].[envelope_item_number], [t0].[expect_date], [t0].[first_sale_date], [t0].[freight_cost_percent], [t0].[inventory_gl_account], [t0].[lead_time], [t0].[license_required], [t0].[market], [t0].[material], [t0].[merchandise_cost_percent], [t0].[operator_message], [t0].[origin], [t0].[part_number], [t0].[personalization_flag], [t0].[primary_location], [t0].[print_specification], [t0].[print_template], [t0].[procurement_code], [t0].[qty_expected], [t0].[qty_on_backorder], [t0].[qty_on_hand], [t0].[qty_reserved], [t0].[qty_available], [t0].[return_gl_account], [t0].[sales_gl_account], [t0].[sales_unit], [t0].[serial_number_flag], [t0].[special_process], [t0].[status], [t0].[tax_flag], [t0].[tesla_qty_on_backorder], [t0].[tesla_qty_reserved], [t0].[unit_of_measure], [t0].[vap_cost_percent], [t0].[vendor_code], [t0].[weight], [t0].[root_part_number] FROM [production].[dbo].[part] [t0] JOIN [production].[dbo].[order_detail_sales] [t1] ON *([t0].[part_number] = [t1].[part_number])* JOIN [production].[dbo].[order_header] [t2] ON ([t1].[order_number] = [t2].[order_number]) WHERE ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) OR ([t2].[order_number] = ?) [bind: 1->order_number:57874832, 2->order_number:57874792, 3->order_number:57874789, 4->order_number:57874783, 5->order_number:57874781, 6->order_number:57874779, 7->order_number:57874777, 8->order_number:57874715, 9->order_number:57874714, 10->order_number:57874713, 11->order_number:57874712, 12->order_number:57874708, 13->order_number:57874707, 14->order_number:57874704, 15->order_number:57874657] 4.2.M1-SNAPSHOT: 👎 SELECT DISTINCT [t0].[average_cost], [t0].[backorder_flag], [t0].[break_match_code], [t0].[case_location], [t0].[case_qty], [t0].[category_code], [t0].[cgs_gl_account], RTRIM([t0].[charges_group_code]), [t0].[composition_family], [t0].[composition_output_definition], [t0].[custom_vendor], [t0].[description], RTRIM([t0].[drop_ship_code]), [t0].[duties_percent], [t0].[duties_tax_cost_percent], RTRIM([t0].[envelope_item_number]), [t0].[expect_date], [t0].[first_sale_date], [t0].[freight_cost_percent], [t0].[inventory_gl_account], [t0].[lead_time], [t0].[license_required], RTRIM([t0].[market]), [t0].[material], [t0].[merchandise_cost_percent], [t0].[operator_message], [t0].[origin], RTRIM([t0].[part_number]), [t0].[personalization_flag], [t0].[primary_location], [t0].[print_specification], [t0].[print_template], RTRIM([t0].[procurement_code]), [t0].[qty_expected], [t0].[qty_on_backorder], [t0].[qty_on_hand], [t0].[qty_reserved], [t0].[qty_available], [t0].[return_gl_account], [t0].[sales_gl_account], [t0].[sales_unit], [t0].[serial_number_flag], [t0].[special_process], [t0].[status], [t0].[tax_flag], [t0].[tesla_qty_on_backorder], [t0].[tesla_qty_reserved], [t0].[unit_of_measure], [t0].[vap_cost_percent], RTRIM([t0].[vendor_code]), [t0].[weight], RTRIM([t0].[root_part_number]) FROM [production.dbo.part] [t0] JOIN [production.dbo.order_detail_sales] [t1] ON *RTRIM([t0].[part_number]) = RTRIM([t1].[part_number])* JOIN [production.dbo.order_header] [t2] ON [t1].[order_number] = [t2].[order_number] WHERE ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) OR ( [t2].[order_number] = ? ) [bind: 1->order_number:57874832, 2->order_number:57874792, 3->order_number:57874789, 4->order_number:57874783, 5->order_number:57874781, 6->order_number:57874779, 7->order_number:57874777, 8->order_number:57874715, 9->order_number:57874714, 10->order_number:57874713, 11->order_number:57874712, 12->order_number:57874708, 13->order_number:57874707, 14->order_number:57874704, 15->order_number:57874657] Thoughts? I also don't like that it's not writing the "ON" in parenthesis, but I'm sure that's just me be a persnickety old man. 👴 -Lon
