Topic
  • 10 replies
  • Latest Post - ‏2013-07-24T17:13:47Z by Arvind_Gupta
sl99
sl99
17 Posts

Pinned topic Performance of generated Odata queries might be better ?

‏2013-06-06T06:30:16Z |

Hello,

during the use of odata for Informix I found that the query generating for tables wich 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 if it would be possible to change the query generation in this way  ?

 

 

I use Win 7 ,  Odata 5.2 , Informix for Linux 11.7 UC7

 

Thank you very much

 

  • Arvind_Gupta
    Arvind_Gupta
    58 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-06-10T13:47:26Z  

    Hi,

    As per the OData context mentioned,  you are expanding many entities. Please note that all the columns from all the specified tables gets selected by Expand option. It is not be possible to change the query generation.

    To investigate furtherin order to provide our recommendations, we request you to provide few details i.e. all the tables definition with their relationships, OData URI and generated sql.

    Thanks,

    Arvind

  • sl99
    sl99
    17 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-06-13T06:04:29Z  

    Hi,

    As per the OData context mentioned,  you are expanding many entities. Please note that all the columns from all the specified tables gets selected by Expand option. It is not be possible to change the query generation.

    To investigate furtherin order to provide our recommendations, we request you to provide few details i.e. all the tables definition with their relationships, OData URI and generated sql.

    Thanks,

    Arvind

    Hi Arvind,

    thank you for your answer. With the attachment to this answer I provide you an original generated Query. I also attach a Schema for this.

    Maybe the schema clarifies that the tempdb will be heavily working on the exists clauses with the multiple columns.

    Thanks

    Sascha

    Attachments

  • Arvind_Gupta
    Arvind_Gupta
    58 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-06-13T14:41:37Z  
    • sl99
    • ‏2013-06-13T06:04:29Z

    Hi Arvind,

    thank you for your answer. With the attachment to this answer I provide you an original generated Query. I also attach a Schema for this.

    Maybe the schema clarifies that the tempdb will be heavily working on the exists clauses with the multiple columns.

    Thanks

    Sascha

    Hi Sascha,

    Thank you for providing the generated SQL and schema information. In the interested of time, could you please provide the DDL of these tables. This would help me to quickly setup environment to investigate. Also please confirm in addition to expand option, whether you are applying any filter or using any other Odata option. I want to be sure that I am following proper reproduction steps.

    Thanks,

    Arvind

  • sl99
    sl99
    17 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-06-27T12:42:54Z  

    Hi Sascha,

    Thank you for providing the generated SQL and schema information. In the interested of time, could you please provide the DDL of these tables. This would help me to quickly setup environment to investigate. Also please confirm in addition to expand option, whether you are applying any filter or using any other Odata option. I want to be sure that I am following proper reproduction steps.

    Thanks,

    Arvind

    Hi Arvind,

    sorry for answering so late, I had to do several other things in between.

    with this post I provide to you a snippet which should be able to generate the tables .

    Attention : the Relations should be set up like in the Image. They are not included in the script because the original database also has not

    defined foreign keys.  

    In my opinion the Problem is in the query Generation that in the exists branches of the queries the Generator does not filter the unneeded columns. which is not a Problem of especially my schema. You also might be able to produce the Problem using any table which contains many rows and many colums  and expand this to a context with some surrounding tables.

    Thanks

    Sascha

    Attachments

  • Arvind_Gupta
    Arvind_Gupta
    58 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-06-30T08:49:40Z  
    • sl99
    • ‏2013-06-27T12:42:54Z

    Hi Arvind,

    sorry for answering so late, I had to do several other things in between.

    with this post I provide to you a snippet which should be able to generate the tables .

    Attention : the Relations should be set up like in the Image. They are not included in the script because the original database also has not

    defined foreign keys.  

    In my opinion the Problem is in the query Generation that in the exists branches of the queries the Generator does not filter the unneeded columns. which is not a Problem of especially my schema. You also might be able to produce the Problem using any table which contains many rows and many colums  and expand this to a context with some surrounding tables.

    Thanks

    Sascha

    Thanks Sascha providing SQL to creates tables. Since DDL for tables "hansm".dosorgan, "hansm".zelle and "hansm".mischan are missing I was not able to create view. Could you please send me DDL for these tables as well.

    Thanks

    Arvind

     

  • sl99
    sl99
    17 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-07-01T06:02:13Z  

    Thanks Sascha providing SQL to creates tables. Since DDL for tables "hansm".dosorgan, "hansm".zelle and "hansm".mischan are missing I was not able to create view. Could you please send me DDL for these tables as well.

    Thanks

    Arvind

     

    Hi Arvind,

    in this answer I attach the missing create script for the tables from the view.

    As a hint some of the Relations:

    dosfl>fl_korr: Prinzipal: dosfl, df_re_nr , dependent: fl_nr

    pliniel>pranw: Prinzipal: plinie, nr , dependent: prod_linie

    dosflkorr_mit_namen>flkorr: Prinzipal: dosflkorr_mit_namen, df_re_nr , dependent: fl_nr

    pranw>artikel: Prinzipal: artikel, a_nr dependent: art_nr

    parnw>aktivemischan Prinzipal aktivemischan nr,dependent : sp_maw_nr

    parnw>aktivemischan Prinzipal aktivemischan nr,dependent : maw_nr

    plinie>pranw Prinzipal plinie nr, dependent prod_linie

     

     

    Thanks

    Sascha

    Attachments

  • Arvind_Gupta
    Arvind_Gupta
    58 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-07-08T06:19:31Z  
    • sl99
    • ‏2013-07-01T06:02:13Z

    Hi Arvind,

    in this answer I attach the missing create script for the tables from the view.

    As a hint some of the Relations:

    dosfl>fl_korr: Prinzipal: dosfl, df_re_nr , dependent: fl_nr

    pliniel>pranw: Prinzipal: plinie, nr , dependent: prod_linie

    dosflkorr_mit_namen>flkorr: Prinzipal: dosflkorr_mit_namen, df_re_nr , dependent: fl_nr

    pranw>artikel: Prinzipal: artikel, a_nr dependent: art_nr

    parnw>aktivemischan Prinzipal aktivemischan nr,dependent : sp_maw_nr

    parnw>aktivemischan Prinzipal aktivemischan nr,dependent : maw_nr

    plinie>pranw Prinzipal plinie nr, dependent prod_linie

     

     

    Thanks

    Sascha

    Hi Sascha,

    I was able to create views after adding the missing tables.

    I have below questions:

    As you mentioned "Attention : the Relations should be set up like in the Image. They are not included in the script because the original database also has not defined foreign keys.". Have you added table relationships in Entity Data Model or in your database?

    Would it be possible for you to share the edmx file? I tried by adding associations in model, but for some reason it did not work.

    As per your context Expand("artikel,artikel/pranw_ziel/verbotenezelle,aktivemischan ,plinie,artikel/art_press/pressanw")
    I need DDLs and foreign key relationships details for verbotenezelle, art_press and for any other dependent tables/views that I will need.

    Do you have a temporary table space configured in Informix server?

    Thanks,
    Arvind

  • sl99
    sl99
    17 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-07-22T08:18:07Z  

    Hi Sascha,

    I was able to create views after adding the missing tables.

    I have below questions:

    As you mentioned "Attention : the Relations should be set up like in the Image. They are not included in the script because the original database also has not defined foreign keys.". Have you added table relationships in Entity Data Model or in your database?

    Would it be possible for you to share the edmx file? I tried by adding associations in model, but for some reason it did not work.

    As per your context Expand("artikel,artikel/pranw_ziel/verbotenezelle,aktivemischan ,plinie,artikel/art_press/pressanw")
    I need DDLs and foreign key relationships details for verbotenezelle, art_press and for any other dependent tables/views that I will need.

    Do you have a temporary table space configured in Informix server?

    Thanks,
    Arvind

    Hi Arvind,

    Yes there is a tebpdbs space defined,.

    to make it easier for you, I now post to you the generated query, the optimized query and the two visual explain files saved from

    IBM Datastudio. So You might see easier what I want to say.

     

    By commenting out the unneeded columns /*  --- */ 

    the time has been reduced from 1min :15 sec  min to

    31 sec .

    If in the IBM driver the selection of the columns is not done ( maybe this is part of the Microsoft part in the odata Driver ?)

    It also would be possible for me to put this topic into connect .

     

    Thanks

    Sascha

  • sl99
    sl99
    17 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-07-22T08:59:35Z  

    Hi Sascha,

    I was able to create views after adding the missing tables.

    I have below questions:

    As you mentioned "Attention : the Relations should be set up like in the Image. They are not included in the script because the original database also has not defined foreign keys.". Have you added table relationships in Entity Data Model or in your database?

    Would it be possible for you to share the edmx file? I tried by adding associations in model, but for some reason it did not work.

    As per your context Expand("artikel,artikel/pranw_ziel/verbotenezelle,aktivemischan ,plinie,artikel/art_press/pressanw")
    I need DDLs and foreign key relationships details for verbotenezelle, art_press and for any other dependent tables/views that I will need.

    Do you have a temporary table space configured in Informix server?

    Thanks,
    Arvind

    Hi Arvind,

    Yes there is a tebpdbs space defined,.

    to make it easier for you, I now post to you the generated query, the optimized query and the two visual explain files saved from

    IBM Datastudio. So You might see easier what I want to say.

     

    By commenting out the unneeded columns /*  --- */ 

    the time has been reduced from 1min :15 sec  min to

    31 sec .

    If in the IBM driver the selection of the columns is not done ( maybe this is part of the Microsoft part in the odata Driver ?)

    It also would be possible for me to put this topic into connect .

     

    Thanks

    Sascha

  • Arvind_Gupta
    Arvind_Gupta
    58 Posts

    Re: Performance of generated Odata queries might be better ?

    ‏2013-07-24T17:13:47Z  
    • sl99
    • ‏2013-07-22T08:59:35Z

    Hi Arvind,

    Yes there is a tebpdbs space defined,.

    to make it easier for you, I now post to you the generated query, the optimized query and the two visual explain files saved from

    IBM Datastudio. So You might see easier what I want to say.

     

    By commenting out the unneeded columns /*  --- */ 

    the time has been reduced from 1min :15 sec  min to

    31 sec .

    If in the IBM driver the selection of the columns is not done ( maybe this is part of the Microsoft part in the odata Driver ?)

    It also would be possible for me to put this topic into connect .

     

    Thanks

    Sascha

    Hi Sascha,

    I looked at generated SQL and optimized SQL and understood your point. We can't control EF SQL generation in this way.

    If you have not tried already,  I suggest to update statistics in database and see if that can improve query performance.

    Thanks,

    Arvind