locked
Exists clauses in Odata generated Queries may contain unneeded columns RRS feed

  • Question

  • Hello,

    during the use of odata for Informix I found that the query
    generating for tables which have many columns and contexts for more tables  could
    be more performant.

    My context is like :

    Expand("artikel,artikel/pranw_ziel/verbotenezelle,aktivemischan
    ,plinie,artikel/art_press/pressanw")

    What I found is that the genration pattern for the queries makes use
    of Exists clauses which are all generated like :

    select col1,col2,col3

    from xy

    where EXISTS (SELECT     CAST(1 AS int) AS C1    FROM 
        (SELECT
    Extent17.art_nr AS art_nr, Extent17.zelle AS zelle, Join16.a_nr,
       
    Join16.a_name, Join16.a_such, Join16.a_dichte, Join16.a_pgr_nr,
       
    Join16.a_stueck_kg, Join16.a_vpe, Join16.a_pe_nr, Join16.a_preis,
       
    Join16.a_dekl_nr, Join16.a_agnr, Join16.a_gruppe, Join16.a_bestzul,
       
    Join16.a_haltbarkeit, Join16.a_melde, Join16.a_abteilungs_nr,
       
    Join16.a_ref_nr, Join16.a_pr_nr, Join16.a_zusatz_anr,
       
    Join16.a_zusatz_proz, Join16.a_sack_anr, Join16.a_bemerkung,
       
    Join16.a_ab_nr, Join16.a_inh_menge, Join16.a_inh_krit,
    Join16.a_pr_anzahl,
        Join16.a_aktiv, Join16.a_dispo_nr,
    Join16.a_dispo_wahl, Join16.a_bediener,
        Join16.a_datum, Join16.a_iris_nr,
    Join16.a_ean, Join16.a_keko_best_nr,
        Join16.ifx_insert_checksum,
    Join16.ifx_row_version, Join16.ap_artikel_nr,
        Join16.ap_pa_nr,
    Join16.ap_misch_fett, Join16.ap_mehl_dichte,
        Join16.ap_pelletlaenge,
    Extent20.z_intern AS z_intern, Extent20.z_extern
        AS z_extern,
    Extent20.z_p_linie AS z_p_linie, Extent20.z_art AS z_art, ....

    As you see ( this is a fragment of an original query from a bigger
    context ...

    What happens is that these queries cause heavy use of the tempdb
    because they also join Multi column subselects whith each other.  The first most
    inexpensve way to optimize these gind of generated queries seems to be to
    eliminate the generation of innecessary columns in the inner part of the exists
    brackets . Except from the columns which are needed for join or filter purposes
    the other columns of the tables are not needed within the exists part. Here they
    cause load on the tempdb because during the process there are tables generated
    in the tempdb which contain all of the columns of the select list.

     I have one query optimized manually from 1 Minute 11sec  down to  17
    seconds by eliminationg the unneeded columns in the exists part of the
     generated query.

    So my question is  which part of odata is responsible for generating the columns in the exists part of the query ?

    Is it the Driver  or the odata - module ?

    Would it be possible to reduce the number of columns in the exists part of the generated queries ?

    Thank you very much .

     

     

    Wednesday, July 24, 2013 9:44 AM

All replies