You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Lon Varscsak <lo...@gmail.com> on 2019/05/02 00:53:43 UTC
Possible regression in 4.2
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
Re: Possible regression in 4.2
Posted by Lon Varscsak <lo...@gmail.com>.
Also, I verified (with Sybase) that if the source column is a varchar and
the destination column is a char, that the optimizer is doing the right
thing and picking the right index (I was worried it might be doing some
conversion internally that would prevent it from using the right index). I
don't really have production data for the reverse.
I've confirmed on SQL Server that the RTRIM isn't needed to join char and
varchar.
On Thu, May 2, 2019 at 11:41 AM Andrus Adamchik <an...@objectstyle.org>
wrote:
> Thanks for the data point. Will save us some research.
>
> Andrus
>
> > On May 2, 2019, at 9:25 PM, Lon Varscsak <lo...@gmail.com> 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 <an...@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 <ntimofeev@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 <lo...@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
> >>
> >>
>
>
Re: Possible regression in 4.2
Posted by Andrus Adamchik <an...@objectstyle.org>.
Thanks for the data point. Will save us some research.
Andrus
> On May 2, 2019, at 9:25 PM, Lon Varscsak <lo...@gmail.com> 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 <an...@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 <nt...@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 <lo...@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
>>
>>
Re: Possible regression in 4.2
Posted by Lon Varscsak <lo...@gmail.com>.
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 <an...@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 <nt...@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 <lo...@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
>
>
Re: Possible regression in 4.2
Posted by Andrus Adamchik <an...@objectstyle.org>.
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 <nt...@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 <lo...@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
Re: Possible regression in 4.2
Posted by Nikita Timofeev <nt...@objectstyle.com>.
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 <lo...@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