0

I am running a job where in source, i have oracle sql query to read the rows, followed by sorter transformation. Below are session logs where we can see after the start of sorter transformation [srt] it takes more than 3 hours to process 410516 rows. I am failing to understand if sorter is taking time or source query? Appreciate your response.

READER_1_1_1> RR_4049 [2022-06-26 23:40:13.888] SQL Query issued to database : (Sun Jun 26 23:40:13 2022)

READER_1_1_1> RR_4050 [2022-06-26 23:49:01.147] First row returned from database to reader : (Sun Jun 26 23:49:01 2022)

TRANSF_1_1_1> SORT_40420 [2022-06-26 23:49:01.148] Start of input for Transformation [srt]. : (Sun Jun 26 23:49:01 2022)

READER_1_1_1> BLKR_16019 [2022-06-27 03:04:51.901] Read [410516] rows, read [0] error rows for source table [DUMMY_src] instance name [DUMMY_src]

READER_1_1_1> BLKR_16008 [2022-06-27 03:04:51.902] Reader run completed.

TRANSF_1_1_1> SORT_40421 [2022-06-27 03:04:51.909] End of input for Transformation [srt]. : (Mon Jun 27 03:04:51 2022)

TRANSF_1_1_1> SORT_40422 [2022-06-27 03:04:52.180] End of output from Sorter Transformation [srt]. Processed 410516 rows (6568256 input bytes; 0 temp I/O bytes). : (Mon Jun 27 03:04:52 2022)

TRANSF_1_1_1> SORT_40423 [2022-06-27 03:04:52.181] End of sort for Sorter Transformation [srt]. : (Mon Jun 27 03:04:52 2022)

WRITER_1_*_1> WRT_8167 [2022-06-27 03:04:52.201] Start loading table

WRT_8035 [2022-06-27 03:09:47.457] Load complete time: Mon Jun 27 03:09:47 2022

Ankush
  • 1
  • 1
  • 1
    You probably need to look at the performance of your source query - how complex is it, is it using indices, etc. Also, it's probably more performant to to use the DBMS to sort the data (using ORDER BY) rather than using a Sorter transformation - if your overall mapping logic allows you to – NickW Jun 27 '22 at 12:58
  • Thanks Nick for the response. I am using readily available Incremental load template in IICS. – Ankush Jun 28 '22 at 07:24
  • Hi - apologies if I've missed something but I'm not sure of the point of your last comment. It doesn't seem to be relevant to anything I put in my comment, it doesn't seem to add any useful information (the fact that you are using an incremental load template provides no information that can help anyone with this issue) and it's not asking any additional questions. Have you looked at the performance of the source query - how does it perform if you run it directly in the DBMS rather than with IICS? Can you alter your mapping to order by in the source query rather than use a SRT transformation? – NickW Jun 28 '22 at 08:24
  • 1
    This is clearly the source query that takes 3h25, from the message `23:40:13.888] SQL Query issued to database` to the message `03:04:51.902] Reader run completed.`. Once the Sorter has all lines, it's fast, from the message `03:04:51.909] End of input for Transformation [srt]` to the message `03:04:52.181] End of sort for Sorter Transformation [srt]`. As NickW pointed out, check the performance of your source SQL query ! 3 hours to process 410516 rows seems slow to me. – Mickaël Bucas Jun 28 '22 at 09:07

0 Answers0