Hi everybody,

we have a VDB which aggregates 4 Sybase ASA 9.0 databases. When executing the following query:

 
select this_.id as id18_2_, this_.dt_creation as dt2_18_2_, this_.dt_update as dt3_18_2_, this_.id_user as id4_18_2_, this_.st_note as st5_18_2_, this_.st_stato
as st6_18_2_, this_.nr_stato as nr7_18_2_, this_.nr_stepId as nr8_18_2_, this_.id_dbsource as id23_18_2_, this_.id_amb_veicolo_nuovo as id18_18_2_, this_.id_amb_veicolo_usato
as id19_18_2_, this_.st_barcode as st9_18_2_, this_.id_bollaIngresso as id39_18_2_, this_.id_bollaUscita as id40_18_2_, this_.id_cfl_veicolo_nuovo as id20_18_2_, this_.id_cfl_veicolo_usato
as id21_18_2_, this_.id_cliente as id22_18_2_, this_.st_conforme as st10_18_2_, this_.nr_cx as nr11_18_2_, this_.dt_dataArrivo as dt12_18_2_, this_.dt_data_stampa_foglio_lavoraz
as dt13_18_2_, this_.dt_data_stato as dt14_18_2_, this_.dt_data_ultima_mapo as dt15_18_2_, this_.dt_DataUltimaStock as dt16_18_2_, this_.dt_dataUscita as dt17_18_2_, this_.id_fattura
as id24_18_2_, this_.id_mit_veicolo_nuovo as id25_18_2_, this_.id_mit_veicolo_usato as id26_18_2_, this_.id_moc_veicolo_nuovo as id27_18_2_, this_.id_moc_veicolo_usato
as id28_18_2_, this_.id_tipoVeicolo as id29_18_2_, this_.st_maps as st30_18_2_, this_.st_notePiazzalista as st31_18_2_, this_.fg_ricalcola_costi as fg32_18_2_, this_.fg_rientro
as fg33_18_2_, this_.st_telaioInfinity as st34_18_2_, this_.st_telaioTarga as st35_18_2_, this_.nr_totaleServizi as nr36_18_2_, this_.st_trovatoSuInfinity as st37_18_2_, this_.st_uuid
as st38_18_2_, this_.id_veicolo_rientro as id41_18_2_, ambveicolo1_.id_veicolo as id1_42_0_, ambveicolo1_.colore_ext as colore2_42_0_, ambveicolo1_.marca as marca42_0_, ambveicolo1_.modello
as modello42_0_, ambveicolo1_.versione as versione42_0_, ambveicolo1_.data_apertura_mapo as data6_42_0_, ambveicolo1_.data_arrivo as data7_42_0_, ambveicolo1_.data_arrivo_sede
as data8_42_0_, ambveicolo1_.data_fattura_a as data9_42_0_, ambveicolo1_.data_immatricolazione as data10_42_0_, ambveicolo1_.data_ini_preparazione as data11_42_0_, ambveicolo1_.data_prev_arr_sede
as data12_42_0_, ambveicolo1_.data_prev_ritiro_conf as data13_42_0_, ambveicolo1_.data_rich_mapo as data14_42_0_, ambveicolo1_.data_rientro_da_lavoraz as data15_42_0_, ambveicolo1_.data_uscita
as data16_42_0_, ambveicolo1_.data_var_statusterminal as data17_42_0_, ambveicolo1_.ubicazione as ubicazione42_0_, ambveicolo1_.lavorazione_urgente
as lavoraz19_42_0_, ambveicolo1_.note_preparazione as note20_42_0_, ambveicolo1_.num_ordine_commerciale as num21_42_0_, ambveicolo1_.num_ordine_fabbrica as num22_42_0_, ambveicolo1_.status
as status42_0_, ambveicolo1_.status_terminal as status24_42_0_, ambveicolo1_.targa as targa42_0_, ambveicolo1_.telaio as telaio42_0_, ambveicolo1_.colore_int
as colore27_42_0_, ambveicolo1_.num_contratto as num28_42_0_, ambveicolo1_.tipo_ritiro as tipo29_42_0_, ambveicolo1_.ubicazione_consegna
as ubicazione30_42_0_, ambveicolo1_.linea as linea42_0_, ambtestata4_.id_contratto as id1_44_1_, ambtestata4_.id_cliente as id2_44_1_, ambtestata4_.id_cliente_utilizzatore
as id3_44_1_, ambtestata4_.numero_contratto as numero4_44_1_, ambtestata4_.numero_ocf as numero5_44_1_, ambtestata4_.agente as agente44_1_, ambtestata4_.id_agenzia_disb
as id7_44_1_, ambtestata4_.cod_punto_vendita as cod8_44_1_, ambtestata4_.id_gruppo_agenti as id9_44_1_
from Veicolo this_
inner join MocVeicoloNuovo ambveicolo1_ on this_.id_moc_veicolo_nuovo=ambveicolo1_.id_veicolo
left outer join MocTestataContrattoNuovo ambtestata4_ on ambveicolo1_.num_contratto=ambtestata4_.id_contratto
where this_.id_cliente=7 and this_.dt_data_stampa_foglio_lavoraz is null
and this_.id_tipoVeicolo='N' and this_.nr_stepId<>0 and this_.nr_stepId<>1
and this_.nr_stepId<>2 and this_.nr_stepId<>13 and this_.nr_stepId<>14
and this_.nr_stepId<>15
and (ambveicolo1_.num_contratto is not null or ambveicolo1_.data_ini_preparazione is not null)

Using Teiid 7.3.0 we get the following timings:

Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 2.035, SQL query: 2.035, Building output: 0
Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 1.614, SQL query: 1.614, Building output: 0
Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 1.479, SQL query: 1.479, Building output: 0
Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 1.485, SQL query: 1.485, Building output: 0
Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 1.611, SQL query: 1.611, Building output: 0

This is how the query is sent to Sybase:

08/24 13:18:19.998 ** EXTRA   conn: 11    SELECT g_0.id_moc_veicolo_nuovo AS c_0, g_0.id AS c_1, g_0.dt_creation AS c_2, g_0.dt_update AS c_3, g_0.id_user AS c_4, g_0.st_note AS c_5, g_0.st_stato AS c_6, g_0.nr_stato AS c_7, g_0.nr_stepId AS c_8, g_0.id_dbsource AS c_9, g_0.id_amb_veicolo_nuovo AS c_10, g_0.id_amb_veicolo_usato AS c_11, g_0.st_barcode AS c_12, g_0.id_bollaIngresso AS c_13, g_0.id_bollaUscita AS c_14, g_0.id_cfl_veicolo_nuovo AS c_15, g_0.id_cfl_veicolo_usato AS c_16, g_0.id_cliente AS c_17, g_0.st_conforme AS c_18, g_0.nr_cx AS c_19, g_0.dt_dataArrivo AS c_20, g_0.dt_data_stampa_foglio_lavoraz AS c_21, g_0.dt_data_stato AS c_22, g_0.dt_data_ultima_mapo AS c_23, g_0.dt_DataUltimaStock AS c_24, g_0.dt_dataUscita AS c_25, g_0.id_fattura AS c_26, g_0.id_mit_veicolo_nuovo AS c_27, g_0.id_mit_veicolo_usato AS c_28, g_0.id_moc_veicolo_usato AS c_29, g_0.id_tipoVeicolo AS c_30, g_0.st_maps AS c_31, g_0.st_notePiazzalista AS c_32, g_0.fg_ricalcola_costi AS c_33, g_0.fg_rientro AS c_34, g_0.st_telaioInfinity AS c_35, g_0.st_telaioTarga AS c_36, g_0.nr_totaleServizi AS c_37, g_0.st_trovatoSuInfinity AS c_38, g_0.st_uuid AS c_39, g_0.id_veicolo_rientro AS c_40 FROM infinity12.newMit.Veicolo g_0 WHERE g_0.id_cliente = @p0 AND g_0.dt_data_stampa_foglio_lavoraz IS NULL AND g_0.id_tipoVeicolo = @p1 AND g_0.nr_stepId <> @p2 AND g_0.nr_stepId <> @p3 AND g_0.nr_stepId <> @p4 AND g_0.nr_stepId <> @p5 AND g_0.nr_stepId <> @p6 AND g_0.nr_stepId <> @p7 ORDER BY c_0
08/24 13:18:19.998 ** DONE    conn: 11    STMT_PREPARE            Stmt=65544
08/24 13:18:19.998 ** REQUEST conn: 11    CURSOR_OPEN             Stmt=65544
08/24 13:18:19.999 ** DONE    conn: 11    CURSOR_OPEN             Crsr=65545
08/24 13:18:20.001 ** REQUEST conn: 12    STMT_PREPARE            [large string unavailable]
08/24 13:18:20.001 ** EXTRA   conn: 12    SELECT g_0.id_contratto AS c_0, g_0.id_cliente AS c_1, g_0.id_cliente_utilizzatore AS c_2, g_0.numero_contratto AS c_3, g_0.numero_ocf AS c_4, g_0.agente AS c_5, g_0.id_agenzia_disb AS c_6, g_0.cod_punto_vendita AS c_7, g_0.id_gruppo_agenti AS c_8 FROM infinity11.DBA.n_testata_contratto g_0 ORDER BY c_0
08/24 13:18:20.001 ** DONE    conn: 12    STMT_PREPARE            Stmt=65544
08/24 13:18:20.001 ** REQUEST conn: 12    CURSOR_OPEN             Stmt=65544
08/24 13:18:20.001 ** DONE    conn: 12    CURSOR_OPEN             Crsr=65545
08/24 13:18:20.043 ** WARNING conn: 11    code: 100 "Row not found"
08/24 13:18:20.047 ** REQUEST conn: 11    CURSOR_CLOSE            Crsr=65545
08/24 13:18:20.047 ** DONE    conn: 11    CURSOR_CLOSE           
08/24 13:18:20.047 ** REQUEST conn: 11    STMT_DROP               Stmt=65544
08/24 13:18:20.047 ** DONE    conn: 11    STMT_DROP              
08/24 13:18:20.054 ** REQUEST conn: 15    STMT_PREPARE            [large string unavailable]
08/24 13:18:20.054 ** EXTRA   conn: 15    SELECT g_0.id_veicolo AS c_0, g_0.num_contratto AS c_1, g_0.colore_ext AS c_2, g_0.marca AS c_3, g_0.modello AS c_4, g_0.versione AS c_5, g_0.data_apertura_mapo AS c_6, g_0.data_arrivo AS c_7, g_0.data_arrivo_sede AS c_8, g_0.data_fattura_a AS c_9, g_0.data_immatricolazione AS c_10, g_0.data_ini_preparazione AS c_11, g_0.data_prev_arr_sede AS c_12, g_0.data_prev_ritiro_conf AS c_13, g_0.data_rich_mapo AS c_14, g_0.data_rientro_da_lavoraz AS c_15, g_0.data_uscita AS c_16, g_0.data_var_statusterminal AS c_17, g_0.ubicazione AS c_18, g_0.lavorazione_urgente AS c_19, g_0.note_preparazione AS c_20, g_0.num_ordine_commerciale AS c_21, g_0.num_ordine_fabbrica AS c_22, g_0.status AS c_23, g_0.status_terminal AS c_24, g_0.targa AS c_25, g_0.telaio AS c_26, g_0.colore_int AS c_27, g_0.tipo_ritiro AS c_28, g_0.ubicazione_consegna AS c_29, g_0.linea AS c_30 FROM infinity11.DBA.n_veicoli g_0 WHERE (g_0.num_contratto IS NOT NULL OR g_0.data_ini_preparazione IS NOT NULL) AND g_0.id_veicolo IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100, @p101, @p102, @p103, @p104, @p105, @p106, @p107, @p108, @p109, @p110, @p111, @p112, @p113, @p114, @p115, @p116, @p117, @p118, @p119, @p120, @p121, @p122, @p123, @p124, @p125, @p126, @p127, @p128, @p129, @p130, @p131, @p132, @p133, @p134, @p135, @p136, @p137, @p138, @p139, @p140, @p141, @p142, @p143, @p144, @p145, @p146, @p147, @p148, @p149, @p150, @p151, @p152, @p153, @p154, @p155, @p156, @p157, @p158, @p159, @p160, @p161, @p162, @p163, @p164, @p165, @p166, @p167, @p168, @p169, @p170, @p171, @p172, @p173, @p174, @p175, @p176, @p177, @p178, @p179, @p180, @p181, @p182, @p183, @p184, @p185, @p186, @p187, @p188, @p189, @p190, @p191, @p192, @p193, @p194, @p195, @p196, @p197, @p198, @p199, @p200, @p201, @p202, @p203, @p204, @p205, @p206, @p207, @p208, @p209, @p210, @p211, @p212, @p213, @p214, @p215, @p216, @p217, @p218, @p219, @p220, @p221, @p222, @p223, @p224, @p225, @p226, @p227, @p228, @p229, @p230, @p231, @p232, @p233, @p234, @p235, @p236, @p237, @p238, @p239, @p240, @p241, @p242, @p243, @p244, @p245, @p246, @p247, @p248, @p249)
08/24 13:18:20.055 ** DONE    conn: 15    STMT_PREPARE            Stmt=65556
08/24 13:18:20.056 ** REQUEST conn: 15    CURSOR_OPEN             Stmt=65556
08/24 13:18:20.060 ** DONE    conn: 15    CURSOR_OPEN             Crsr=65557
08/24 13:18:20.066 ** WARNING conn: 15    code: 100 "Row not found"
08/24 13:18:20.072 ** REQUEST conn: 16    STMT_PREPARE            [large string unavailable]
08/24 13:18:20.072 ** REQUEST conn: 15    CURSOR_CLOSE            Crsr=65557
08/24 13:18:20.072 ** DONE    conn: 15    CURSOR_CLOSE           
08/24 13:18:20.072 ** REQUEST conn: 15    STMT_DROP               Stmt=65556
08/24 13:18:20.072 ** DONE    conn: 15    STMT_DROP              
08/24 13:18:20.072 ** EXTRA   conn: 16    SELECT g_0.id_veicolo AS c_0, g_0.num_contratto AS c_1, g_0.colore_ext AS c_2, g_0.marca AS c_3, g_0.modello AS c_4, g_0.versione AS c_5, g_0.data_apertura_mapo AS c_6, g_0.data_arrivo AS c_7, g_0.data_arrivo_sede AS c_8, g_0.data_fattura_a AS c_9, g_0.data_immatricolazione AS c_10, g_0.data_ini_preparazione AS c_11, g_0.data_prev_arr_sede AS c_12, g_0.data_prev_ritiro_conf AS c_13, g_0.data_rich_mapo AS c_14, g_0.data_rientro_da_lavoraz AS c_15, g_0.data_uscita AS c_16, g_0.data_var_statusterminal AS c_17, g_0.ubicazione AS c_18, g_0.lavorazione_urgente AS c_19, g_0.note_preparazione AS c_20, g_0.num_ordine_commerciale AS c_21, g_0.num_ordine_fabbrica AS c_22, g_0.status AS c_23, g_0.status_terminal AS c_24, g_0.targa AS c_25, g_0.telaio AS c_26, g_0.colore_int AS c_27, g_0.tipo_ritiro AS c_28, g_0.ubicazione_consegna AS c_29, g_0.linea AS c_30 FROM infinity11.DBA.n_veicoli g_0 WHERE (g_0.num_contratto IS NOT NULL OR g_0.data_ini_preparazione IS NOT NULL) AND g_0.id_veicolo IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100, @p101, @p102, @p103, @p104, @p105, @p106, @p107, @p108, @p109, @p110, @p111, @p112, @p113, @p114, @p115, @p116, @p117, @p118, @p119, @p120, @p121, @p122, @p123, @p124, @p125, @p126, @p127, @p128, @p129, @p130, @p131, @p132, @p133, @p134, @p135, @p136, @p137, @p138, @p139, @p140, @p141, @p142, @p143, @p144, @p145, @p146, @p147, @p148, @p149, @p150, @p151, @p152, @p153, @p154, @p155, @p156, @p157, @p158, @p159, @p160, @p161, @p162, @p163, @p164, @p165, @p166, @p167, @p168, @p169, @p170, @p171, @p172, @p173, @p174, @p175, @p176, @p177, @p178, @p179, @p180, @p181, @p182, @p183, @p184, @p185, @p186, @p187, @p188, @p189, @p190, @p191, @p192, @p193, @p194, @p195, @p196, @p197, @p198, @p199, @p200, @p201, @p202, @p203, @p204, @p205, @p206, @p207, @p208, @p209, @p210, @p211, @p212, @p213, @p214, @p215, @p216, @p217, @p218, @p219, @p220, @p221, @p222, @p223, @p224, @p225, @p226, @p227, @p228, @p229, @p230, @p231, @p232, @p233, @p234, @p235, @p236, @p237, @p238, @p239, @p240, @p241, @p242, @p243, @p244, @p245, @p246, @p247, @p248, @p249)
08/24 13:18:20.073 ** DONE    conn: 16    STMT_PREPARE            Stmt=65548
08/24 13:18:20.073 ** REQUEST conn: 16    CURSOR_OPEN             Stmt=65548
08/24 13:18:20.076 ** DONE    conn: 16    CURSOR_OPEN             Crsr=65549
08/24 13:18:20.084 ** WARNING conn: 16    code: 100 "Row not found"
08/24 13:18:20.085 ** REQUEST conn: 16    CURSOR_CLOSE            Crsr=65549
08/24 13:18:20.085 ** DONE    conn: 16    CURSOR_CLOSE           
08/24 13:18:20.085 ** REQUEST conn: 16    STMT_DROP               Stmt=65548
08/24 13:18:20.085 ** DONE    conn: 16    STMT_DROP              
08/24 13:18:20.090 ** REQUEST conn: 16    STMT_PREPARE            [large string unavailable]
08/24 13:18:20.090 ** EXTRA   conn: 16    SELECT g_0.id_veicolo AS c_0, g_0.num_contratto AS c_1, g_0.colore_ext AS c_2, g_0.marca AS c_3, g_0.modello AS c_4, g_0.versione AS c_5, g_0.data_apertura_mapo AS c_6, g_0.data_arrivo AS c_7, g_0.data_arrivo_sede AS c_8, g_0.data_fattura_a AS c_9, g_0.data_immatricolazione AS c_10, g_0.data_ini_preparazione AS c_11, g_0.data_prev_arr_sede AS c_12, g_0.data_prev_ritiro_conf AS c_13, g_0.data_rich_mapo AS c_14, g_0.data_rientro_da_lavoraz AS c_15, g_0.data_uscita AS c_16, g_0.data_var_statusterminal AS c_17, g_0.ubicazione AS c_18, g_0.lavorazione_urgente AS c_19, g_0.note_preparazione AS c_20, g_0.num_ordine_commerciale AS c_21, g_0.num_ordine_fabbrica AS c_22, g_0.status AS c_23, g_0.status_terminal AS c_24, g_0.targa AS c_25, g_0.telaio AS c_26, g_0.colore_int AS c_27, g_0.tipo_ritiro AS c_28, g_0.ubicazione_consegna AS c_29, g_0.linea AS c_30 FROM infinity11.DBA.n_veicoli g_0 WHERE (g_0.num_contratto IS NOT NULL OR g_0.data_ini_preparazione IS NOT NULL) AND g_0.id_veicolo IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100, @p101, @p102, @p103, @p104, @p105, @p106, @p107, @p108, @p109, @p110, @p111, @p112, @p113, @p114, @p115, @p116, @p117, @p118, @p119, @p120, @p121, @p122, @p123, @p124, @p125, @p126, @p127, @p128, @p129, @p130, @p131, @p132, @p133, @p134, @p135, @p136, @p137, @p138, @p139, @p140, @p141, @p142, @p143, @p144, @p145, @p146, @p147, @p148, @p149, @p150, @p151, @p152, @p153, @p154, @p155, @p156, @p157, @p158, @p159, @p160, @p161, @p162, @p163, @p164, @p165, @p166, @p167, @p168, @p169, @p170, @p171, @p172, @p173, @p174, @p175, @p176, @p177, @p178, @p179, @p180, @p181, @p182, @p183, @p184, @p185, @p186, @p187, @p188, @p189, @p190, @p191, @p192, @p193, @p194, @p195, @p196, @p197, @p198, @p199, @p200, @p201, @p202, @p203, @p204, @p205, @p206, @p207, @p208, @p209, @p210, @p211, @p212, @p213, @p214, @p215, @p216, @p217, @p218, @p219, @p220, @p221, @p222, @p223, @p224, @p225, @p226, @p227, @p228, @p229, @p230, @p231, @p232, @p233, @p234, @p235, @p236, @p237, @p238, @p239, @p240, @p241, @p242, @p243, @p244, @p245, @p246, @p247, @p248, @p249)
08/24 13:18:20.091 ** DONE    conn: 16    STMT_PREPARE            Stmt=65550
08/24 13:18:20.091 ** REQUEST conn: 16    CURSOR_OPEN             Stmt=65550
08/24 13:18:20.094 ** DONE    conn: 16    CURSOR_OPEN             Crsr=65551
08/24 13:18:20.103 ** WARNING conn: 16    code: 100 "Row not found"
08/24 13:18:20.104 ** REQUEST conn: 16    CURSOR_CLOSE            Crsr=65551
08/24 13:18:20.104 ** DONE    conn: 16    CURSOR_CLOSE           
08/24 13:18:20.104 ** REQUEST conn: 16    STMT_DROP               Stmt=65550
08/24 13:18:20.104 ** DONE    conn: 16    STMT_DROP              
08/24 13:18:20.108 ** REQUEST conn: 16    STMT_PREPARE            [large string unavailable]
08/24 13:18:20.110 ** EXTRA   conn: 16    SELECT g_0.id_veicolo AS c_0, g_0.num_contratto AS c_1, g_0.colore_ext AS c_2, g_0.marca AS c_3, g_0.modello AS c_4, g_0.versione AS c_5, g_0.data_apertura_mapo AS c_6, g_0.data_arrivo AS c_7, g_0.data_arrivo_sede AS c_8, g_0.data_fattura_a AS c_9, g_0.data_immatricolazione AS c_10, g_0.data_ini_preparazione AS c_11, g_0.data_prev_arr_sede AS c_12, g_0.data_prev_ritiro_conf AS c_13, g_0.data_rich_mapo AS c_14, g_0.data_rientro_da_lavoraz AS c_15, g_0.data_uscita AS c_16, g_0.data_var_statusterminal AS c_17, g_0.ubicazione AS c_18, g_0.lavorazione_urgente AS c_19, g_0.note_preparazione AS c_20, g_0.num_ordine_commerciale AS c_21, g_0.num_ordine_fabbrica AS c_22, g_0.status AS c_23, g_0.status_terminal AS c_24, g_0.targa AS c_25, g_0.telaio AS c_26, g_0.colore_int AS c_27, g_0.tipo_ritiro AS c_28, g_0.ubicazione_consegna AS c_29, g_0.linea AS c_30 FROM infinity11.DBA.n_veicoli g_0 WHERE (g_0.num_contratto IS NOT NULL OR g_0.data_ini_preparazione IS NOT NULL) AND g_0.id_veicolo IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100, @p101, @p102, @p103, @p104, @p105, @p106, @p107, @p108, @p109, @p110, @p111, @p112, @p113, @p114, @p115, @p116, @p117, @p118, @p119, @p120, @p121, @p122, @p123, @p124, @p125, @p126, @p127, @p128, @p129, @p130, @p131, @p132, @p133, @p134, @p135, @p136, @p137, @p138, @p139)
08/24 13:18:20.110 ** DONE    conn: 16    STMT_PREPARE            Stmt=65552
08/24 13:18:20.110 ** REQUEST conn: 16    CURSOR_OPEN             Stmt=65552
08/24 13:18:20.112 ** DONE    conn: 16    CURSOR_OPEN             Crsr=65553
08/24 13:18:20.118 ** WARNING conn: 16    code: 100 "Row not found"
08/24 13:18:20.119 ** REQUEST conn: 16    CURSOR_CLOSE            Crsr=65553
08/24 13:18:20.119 ** DONE    conn: 16    CURSOR_CLOSE           
08/24 13:18:20.119 ** REQUEST conn: 16    STMT_DROP               Stmt=65552
08/24 13:18:20.119 ** DONE    conn: 16    STMT_DROP              
08/24 13:18:21.583 ** WARNING conn: 12    code: 100 "Row not found"
08/24 13:18:21.586 ** REQUEST conn: 12    CURSOR_CLOSE            Crsr=65545
08/24 13:18:21.587 ** DONE    conn: 12    CURSOR_CLOSE           
08/24 13:18:21.587 ** REQUEST conn: 12    STMT_DROP               Stmt=65544
08/24 13:18:21.587 ** DONE    conn: 12    STMT_DROP               

Executing the same query using Teiid 7.4.0 we get the following measurements:

Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 6.09, SQL query: 6.09, Building output: 0
Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 5.659, SQL query: 5.659, Building output: 0
Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 6.74, SQL query: 6.74, Building output: 0
Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 8.03, SQL query: 8.03, Building output: 0
Query 1 of 1, Rows read: 485, Elapsed time (seconds) - Total: 8.561, SQL query: 8.561, Building output: 0

And here is the query being sent to Sybase:

08/24 13:27:22.280 ** REQUEST conn: 21    STMT_PREPARE            [large string unavailable]
08/24 13:27:22.280 ** EXTRA   conn: 21    SELECT g_0.id_moc_veicolo_nuovo AS c_0, g_0.id AS c_1, g_0.dt_creation AS c_2, g_0.dt_update AS c_3, g_0.id_user AS c_4, g_0.st_note AS c_5, g_0.st_stato AS c_6, g_0.nr_stato AS c_7, g_0.nr_stepId AS c_8, g_0.id_dbsource AS c_9, g_0.id_amb_veicolo_nuovo AS c_10, g_0.id_amb_veicolo_usato AS c_11, g_0.st_barcode AS c_12, g_0.id_bollaIngresso AS c_13, g_0.id_bollaUscita AS c_14, g_0.id_cfl_veicolo_nuovo AS c_15, g_0.id_cfl_veicolo_usato AS c_16, g_0.id_cliente AS c_17, g_0.st_conforme AS c_18, g_0.nr_cx AS c_19, g_0.dt_dataArrivo AS c_20, g_0.dt_data_stampa_foglio_lavoraz AS c_21, g_0.dt_data_stato AS c_22, g_0.dt_data_ultima_mapo AS c_23, g_0.dt_DataUltimaStock AS c_24, g_0.dt_dataUscita AS c_25, g_0.id_fattura AS c_26, g_0.id_mit_veicolo_nuovo AS c_27, g_0.id_mit_veicolo_usato AS c_28, g_0.id_moc_veicolo_usato AS c_29, g_0.id_tipoVeicolo AS c_30, g_0.st_maps AS c_31, g_0.st_notePiazzalista AS c_32, g_0.fg_ricalcola_costi AS c_33, g_0.fg_rientro AS c_34, g_0.st_telaioInfinity AS c_35, g_0.st_telaioTarga AS c_36, g_0.nr_totaleServizi AS c_37, g_0.st_trovatoSuInfinity AS c_38, g_0.st_uuid AS c_39, g_0.id_veicolo_rientro AS c_40 FROM infinity12.newMit.Veicolo g_0 WHERE g_0.id_cliente = 7 AND g_0.dt_data_stampa_foglio_lavoraz IS NULL AND g_0.id_tipoVeicolo = 'N' AND g_0.nr_stepId <> 0 AND g_0.nr_stepId <> 1 AND g_0.nr_stepId <> 2 AND g_0.nr_stepId <> 13 AND g_0.nr_stepId <> 14 AND g_0.nr_stepId <> 15 ORDER BY c_0
08/24 13:27:22.280 ** DONE    conn: 21    STMT_PREPARE            Stmt=65552
08/24 13:27:22.280 ** REQUEST conn: 21    CURSOR_OPEN             Stmt=65552
08/24 13:27:22.281 ** DONE    conn: 21    CURSOR_OPEN             Crsr=65553
08/24 13:27:22.327 ** WARNING conn: 21    code: 100 "Row not found"
08/24 13:27:22.330 ** REQUEST conn: 21    CURSOR_CLOSE            Crsr=65553
08/24 13:27:22.331 ** DONE    conn: 21    CURSOR_CLOSE           
08/24 13:27:22.331 ** REQUEST conn: 21    STMT_DROP               Stmt=65552
08/24 13:27:22.331 ** DONE    conn: 21    STMT_DROP              
08/24 13:27:22.340 ** REQUEST conn: 26    STMT_PREPARE            [large string unavailable]
08/24 13:27:22.340 ** EXTRA   conn: 26    SELECT g_0.id_veicolo AS c_0, g_0.num_contratto AS c_1, g_0.colore_ext AS c_2, g_0.marca AS c_3, g_0.modello AS c_4, g_0.versione AS c_5, g_0.data_apertura_mapo AS c_6, g_0.data_arrivo AS c_7, g_0.data_arrivo_sede AS c_8, g_0.data_fattura_a AS c_9, g_0.data_immatricolazione AS c_10, g_0.data_ini_preparazione AS c_11, g_0.data_prev_arr_sede AS c_12, g_0.data_prev_ritiro_conf AS c_13, g_0.data_rich_mapo AS c_14, g_0.data_rientro_da_lavoraz AS c_15, g_0.data_uscita AS c_16, g_0.data_var_statusterminal AS c_17, g_0.ubicazione AS c_18, g_0.lavorazione_urgente AS c_19, g_0.note_preparazione AS c_20, g_0.num_ordine_commerciale AS c_21, g_0.num_ordine_fabbrica AS c_22, g_0.status AS c_23, g_0.status_terminal AS c_24, g_0.targa AS c_25, g_0.telaio AS c_26, g_0.colore_int AS c_27, g_0.tipo_ritiro AS c_28, g_0.ubicazione_consegna AS c_29, g_0.linea AS c_30 FROM infinity11.DBA.n_veicoli g_0 WHERE (g_0.num_contratto IS NOT NULL OR g_0.data_ini_preparazione IS NOT NULL) AND 1 = 1 ORDER BY c_0
08/24 13:27:22.341 ** DONE    conn: 26    STMT_PREPARE            Stmt=65568
08/24 13:27:22.341 ** REQUEST conn: 26    CURSOR_OPEN             Stmt=65568
08/24 13:27:22.342 ** DONE    conn: 26    CURSOR_OPEN             Crsr=65569
08/24 13:27:27.748 ** WARNING conn: 26    code: 100 "Row not found"
08/24 13:27:27.911 ** REQUEST conn: 26    CURSOR_CLOSE            Crsr=65569
08/24 13:27:27.912 ** DONE    conn: 26    CURSOR_CLOSE           
08/24 13:27:27.912 ** REQUEST conn: 26    STMT_DROP               Stmt=65568
08/24 13:27:27.912 ** DONE    conn: 26    STMT_DROP              
08/24 13:27:27.927 ** REQUEST conn: 26    STMT_PREPARE            [large string unavailable]
08/24 13:27:27.927 ** EXTRA   conn: 26    SELECT g_0.id_contratto AS c_0, g_0.id_cliente AS c_1, g_0.id_cliente_utilizzatore AS c_2, g_0.numero_contratto AS c_3, g_0.numero_ocf AS c_4, g_0.agente AS c_5, g_0.id_agenzia_disb AS c_6, g_0.cod_punto_vendita AS c_7, g_0.id_gruppo_agenti AS c_8 FROM infinity11.DBA.n_testata_contratto g_0 ORDER BY c_0
08/24 13:27:27.927 ** DONE    conn: 26    STMT_PREPARE            Stmt=65570
08/24 13:27:27.927 ** REQUEST conn: 26    CURSOR_OPEN             Stmt=65570
08/24 13:27:27.927 ** DONE    conn: 26    CURSOR_OPEN             Crsr=65571
08/24 13:27:29.558 ** WARNING conn: 26    code: 100 "Row not found"
08/24 13:27:29.558 ** REQUEST conn: 26    CURSOR_CLOSE            Crsr=65571
08/24 13:27:29.558 ** DONE    conn: 26    CURSOR_CLOSE           
08/24 13:27:29.558 ** REQUEST conn: 26    STMT_DROP               Stmt=65570
08/24 13:27:29.558 ** DONE    conn: 26    STMT_DROP               

As you can see the timings are VERY different: Teiid 7.3 using the IN strategy is much faster than Teeid 7.4 which uses the JOIN strategy.

How do we configure Teiid 7.4+ so that we get the previous behaviour?

I attached scripts to create tables involved in this query and 2 xmi model

Thanks for your help

Juri