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 <and...@objectstyle.org>
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 <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
>
>
