Thanks for the data point. Will save us some research. Andrus
> On May 2, 2019, at 9:25 PM, Lon Varscsak <[email protected]> wrote: > > Agree about CHAR columns....but legacy, you know. :P btw, unifying the > SQLServer and Sybase Adaptors is probably a good thing...SQLServer was > actually Sybase at one point (they licensed it and then went on from > there)...so most things will probably be compatible. > > RTRIM shouldn't be needed because if both columns are CHAR they will > match. I wrote a quick test in Sybase and confirmed that joining from a > CHAR to a VARCHAR does work (without RTRIM): > > SQL: > > create table #test1 (char_data char(15) not null) > create table #test2 (varchar_data varchar(15) not null) > > insert into #test1 values ('TEST') > insert into #test2 values ('TEST') > > SELECT "'" + char_data + "'" as char_date_quoted from #test1 --just to show > that it is padded > SELECT "'" + varchar_data + "'" as varchar_date_quoted from #test2 > > select * from #test1 t1 join #test2 t2 on (t1.char_data = t2.varchar_data) > > drop table #test1 > drop table #test2 > > Results: > > char_date_quoted > ----------------- > 'TEST ' > > [1 row affected] > > varchar_date_quoted > ------------------- > 'TEST' > > [1 row affected] > > char_data varchar_data > --------------- ------------ > TEST TEST > > [1 row affected] > > Give me a minute and I'll find a SQLServer to test on. > > > On Thu, May 2, 2019 at 8:01 AM Andrus Adamchik <[email protected]> > wrote: > >> 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 <[email protected]> >> 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 <[email protected]> >> 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 >> >>
