0 Replies Latest reply on Nov 26, 2018 1:34 AM by Leslie Lee

    join from two different source tables is slow

    Leslie Lee New Member

      I create a data service with two tables from different source,and write a sql as

      "select ple.ipl_id,sdt.entity_id,count(1) from V_ISS_PACK_LIST_E_TI ple  inner join V_ISS_SHIPMENT_DELIVERY_T1 sdt ON ple.entity_id = sdt.entity_id

      where ple.ipl_id >=1999000 AND ple.ipl_id <= 2000000

      group by ple.ipl_id,sdt.entity_id"

       

      and from the log below shows join and aggs are very slow,I want to know how to tuning the performance?

      thanks

       

      2018-11-26 17:05:27.437 <TASK_47694135596800-READER_1_1_1> INFO: [BLKR_16019] Read [1001] rows, read [0] error rows for source table [ISS_PACK_LIST_E_TI] instance name [读取ISS_PACK_LIST_E_TI]

      2018-11-26 17:05:27.437 <TASK_47694131394304-TRANSF_1_1_1> INFO: [DBG_21077] Create joiner cache on master relation for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1]

      2018-11-26 17:05:27.437 <TASK_47694131394304-TRANSF_1_1_1> INFO: [DBG_21714] Variable Length Data Cache Row Storage Mode is disabled for Joiner Transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1].

      2018-11-26 17:05:27.438 <TASK_47694131394304-TRANSF_1_1_1> INFO: [TE_7212] Increasing [Index Cache] size for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1] from [1048576] to [1051200].

      2018-11-26 17:05:27.438 <TASK_47694131394304-TRANSF_1_1_1> INFO: [TE_7212] Increasing [Data Cache] size for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1] from [2097152] to [2097600].

      2018-11-26 17:05:27.439 <TASK_47694131394304-TRANSF_1_1_1> INFO: [CMN_1690] Created new data file [/data01/Informatica/10.2.1/tomcat/bin/cache/PMJNR2_153_0_33213.dat] and index file [/data01/Informatica/10.2.1/tomcat/bin/cache/PMJNR2_153_0_33213.idx] for Joiner [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1].

      2018-11-26 17:05:27.453 <TASK_47694131394304-TRANSF_1_1_1> INFO: [DBG_21214] Finished joiner cache on master relation for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1]

      2018-11-26 17:05:27.502 <TASK_47694129293056-READER_1_2_1> INFO: [RR_4050] First row returned from database to reader

      2018-11-26 17:05:27.502 <TASK_47694131394304-TRANSF_1_2_1> INFO: [DBG_21603] Open master relation cache for detail joiner for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1]

      2018-11-26 17:05:27.807 <TASK_47694129293056-READER_1_2_1> INFO: [BLKR_16019] Read [60091] rows, read [0] error rows for source table [ISS_SHIPMENT_DELIVERY_T] instance name [读取ISS_SHIPMENT_DELIVERY_T1]

      2018-11-26 17:05:44.620 <TASK_47694131394304-TRANSF_1_2_1> INFO: [DBG_21215] Finished processing detail relation

       

       

      2018-11-26 17:05:27.437 <TASK_47694135596800-READER_1_1_1> INFO: [BLKR_16019] Read [1001] rows, read [0] error rows for source table [ISS_PACK_LIST_E_TI] instance name [读取ISS_PACK_LIST_E_TI]2018-11-26 17:05:27.437 <TASK_47694131394304-TRANSF_1_1_1> INFO: [DBG_21077] Create joiner cache on master relation for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1]2018-11-26 17:05:27.437 <TASK_47694131394304-TRANSF_1_1_1> INFO: [DBG_21714] Variable Length Data Cache Row Storage Mode is disabled for Joiner Transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1].2018-11-26 17:05:27.438 <TASK_47694131394304-TRANSF_1_1_1> INFO: [TE_7212] Increasing [Index Cache] size for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1] from [1048576] to [1051200].2018-11-26 17:05:27.438 <TASK_47694131394304-TRANSF_1_1_1> INFO: [TE_7212] Increasing [Data Cache] size for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1] from [2097152] to [2097600].2018-11-26 17:05:27.439 <TASK_47694131394304-TRANSF_1_1_1> INFO: [CMN_1690] Created new data file [/data01/Informatica/10.2.1/tomcat/bin/cache/PMJNR2_153_0_33213.dat] and index file [/data01/Informatica/10.2.1/tomcat/bin/cache/PMJNR2_153_0_33213.idx] for Joiner [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1].2018-11-26 17:05:27.453 <TASK_47694131394304-TRANSF_1_1_1> INFO: [DBG_21214] Finished joiner cache on master relation for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1]2018-11-26 17:05:27.502 <TASK_47694129293056-READER_1_2_1> INFO: [RR_4050] First row returned from database to reader2018-11-26 17:05:27.502 <TASK_47694131394304-TRANSF_1_2_1> INFO: [DBG_21603] Open master relation cache for detail joiner for transformation [jnr_V_ISS_PACK_LIST_E_TI_V_ISS_SHIPMENT_DELIVERY_T1]2018-11-26 17:05:27.807 <TASK_47694129293056-READER_1_2_1> INFO: [BLKR_16019] Read [60091] rows, read [0] error rows for source table [ISS_SHIPMENT_DELIVERY_T] instance name [读取ISS_SHIPMENT_DELIVERY_T1]2018-11-26 17:05:44.620 <TASK_47694131394304-TRANSF_1_2_1> INFO: [DBG_21215] Finished processing detail relationI