I am fairly certain RTRIM will degrade join performance on any DB, causing the DB to bypass the index. So if we did RTRIM on the join conditions on SQLServer, we probably shouldn't.
Fixed-size space-padded columns (aka CHAR columns) are fundamentally stupid and unfriendly. How are they even supposed to work? Can we join them with VARCHARs? So many questions :) Andrus > On May 2, 2019, at 5:54 PM, Nikita Timofeev <ntimof...@objectstyle.com> wrote: > > 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
