Hi, This is again about new translator. Seems like I was too optimistic unifying SQLServer and Sybase adapters. This RTRIM() behavior is from SQLServer and I wonder if it will cause performance issues there too.
On Thu, May 2, 2019 at 3:54 AM Lon Varscsak <lon.varsc...@gmail.com> wrote: > 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 > -- Best regards, Nikita Timofeev
