BODS Script and SQL Transform - Performance

[Former Member]

For select ... result, I'm unable to pull data directly from tables due to few complexities. For this, I have used SQL transform, which is working fine, but impacting in terms of performance as I guess SQL transform cannot be completely pushed down. This result set is then order by with the help of data transfer push down.

As a workaround, I have created a BODS script for the complex select query(used in above stated SQL Xm) and inserted it into a separate table, which is then used a source in next DF as a join with few more source tables and then the Order BY is performed. Estimated record count:80 Million

Even the BODS script is taking almost same time. Does this mean that both the SQL transform and Script does not push down operations to Database ?

As an alternate approach, if a Package/Function is created to do this complex select and insert operation, and then BODS uses that new table as a source for further Order by mechanism, will it show significant performance improvement...

For reference, PFB queries:

Original SQL Transform:

Select '0000'|| Schema1_TBL_A.act_key as OBJ_KEY, tokenize.* from(

select comment_lines,element_no,P_NMBR,comment_typ,comment_typ_seq,load_date,load_type,row_num from (

WITH ilv AS (

        SELECT COMMENT_LINE || ';'                                   AS COMMENT_LINE

        ,     (LENGTH(COMMENT_LINE) - LENGTH(REPLACE(COMMENT_LINE, ';'))) + 1 AS no_of_elements

        , P_NMBR,comment_typ,comment_typ_seq,load_date,load_type,row_num

        FROM   Schema2_TBL_B

        )

    SELECT

    --RTRIM(COMMENT_LINE, ';')                              AS original_string

          SUBSTR(COMMENT_LINE, start_pos, (next_pos-start_pos)) AS comment_lines

    ,      element_no,P_NMBR,comment_typ,comment_typ_seq,load_date,load_type,row_num

    FROM  (

          SELECT ilv.COMMENT_LINE

          ,per_nmbr,comment_typ,comment_typ_seq,load_date,load_type,row_num

          ,      nt.column_value AS element_no

          ,      INSTR(

                    ilv.COMMENT_LINE,

                    ';',

                    DECODE(nt.column_value, 1, 0, 1),

                    DECODE(nt.column_value, 1, 1, nt.column_value-1)) + 1 AS start_pos

          ,      INSTR(

                    ilv.COMMENT_LINE,

                    ';',

                    1,

                    DECODE(nt.column_value, 1, 1, nt.column_value)) AS next_pos

          FROM   ilv

          ,      TABLE(

                    CAST(

                       MULTISET(

                          SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= ilv.no_of_elements

                          ) AS Schema2.number_ct)) nt

         )

) where comment_lines is not null

)

tokenize

   left outer join Schema1_TBL_A on tokenize.P_NMBR = Schema1_TBL_A.P_NMBR and tokenize.COMMENT_TYP = Schema1_TBL_A.COMMENT_TYP

[former_member187605 · Accepted solution]

Do not blame DS for this . In both cases, SQL transform and script, the full SQL is pushed down for execution to the database. If it's slow, it's due to the database.

[Former Member · Accepted solution]

Hi Dirk,

I'm little confused . In one your posts (see below link and snapshot), conversely, you have suggested that DS cannot generate a full SQL-pushdown and should be avoided. Maybe,I'm missing something..

Let the database do the hard work! Better performance in SAP Data Services thanks to full SQL-Pushdo...

...

Regards,

Rv

[Former Member]

Hi Dirk,

I'm little confused . In one your posts (see below link and snapshot), conversely, you have suggested that DS cannot generate a full SQL-pushdown and should be avoided. Maybe,I'm missing something..

...

Regards,

Rv

[Former Member]

Hi RV,

Can you run the query @ the database level and record the running time. Is this time same or faster to time taken by DS to fetch the data.

As a performance tuning technique can you create a view with the above SQL and import it in DS job and then check the performance of the job. I am sure this would improve the performance

Regards

Arun Sasi

[Former Member]

Hi Arun,

Owing to number of records(almost 80 million), i did not specifically fire a query on DB, as it would take at least an hour to fetch the data.

Also, I did think about using Views, can it be directly imported under BODS Oracle data store ?

If yes,what would be advisable use - View or table as Source in BODS Job for better performance..

Thanks..

[Former Member]

Hi RV,  I would advise you to use a view as it can be imported from Oracle datatype.

When you need to alter the view please make sure to import the view after altering the view.

Let me know how it works

Regards

Arun Sasi

[former_member187605]

A full pushdown means DS will push the complete code, including the insert operation, to the database. And that's not possible when a SQL transform is used as a source.

[former_member187605]

A view will only make a difference when DS can generate a full pushdown.

[Former Member]

So, would it be correct to say that SQL transform in itself is full SQL pushed down (executed at DB). However, in DF when used as source, for inserting data into target table, full push-down cannot be achieved.

[former_member187605]

That's it!

[Former Member]

Hi Sasi, i tried with View(used as a Source), everything seems to be stuck now (0 rows being processed ). Not sure, if it is View causing issue owing to large data set.

[Former Member]

Hi RV,

Can you think of optimizing this query such that it only retrieves few records in the view. Like you can apply a filter if applicable. Dont expect the view to be faster if you are extracting 80 Million rows. Lets see how it extracts limited rows. You can also set the Cache of the Source to No(Under the Source table options) as it is a View with lot of records.

The last option would be to create a Materialized view(MV) but again that would be a schema object in Oracle which has its own structure.

Go through this thread below

https://scn.sap.com/thread/3680745

Regards

Arun Sasi

[Former Member]

Hi RV,

Can you think of optimizing this query such that it only retrieves few records in the view. Like you can apply a filter if applicable. Dont expect the view to be faster if you are extracting 80 Million rows. Lets see how it extracts limited rows. You can also set the Cache of the Source to No(Under the Source table options) as it is a View with lot of records.

The last option would be to create a Materialized view(MV) but again that would be a schema object in Oracle which has its own structure.

Go through this thread below

https://scn.sap.com/thread/3680745

Regards

Arun Sasi

[Former Member]

Hi Dirk,

I have a doubt regarding binding between SQL_Transform and Query_Transform.

I have 200 columns in my source table. so I used (select * from Source table with other joins to multiple tables) in SQL transform. This is mapped to Query transform and its fine.

After some time, there are changes in my source schema. (some columns added, some modified , some deleted)

when I refresh the same SQL and mapped only new columns and deleted those extinct columns in query transform (other columns untouched) and executed

here I am getting weird results. the data is getting swapped across the columns (even though the physical mapping still says column 1 mapped to column 1,column 2 mapped to column 2 and so on)

I really wonder how binding happens between SQL Transform and Query transform. How exactly DS maps each column internally?

please let me know

Thanks,

Balaji

[former_member187605]

Do you mean that after updating the schema of the SQL transform, the mapping is not correct anymore? If that's the case, all I can say that's not intended behaviour. Mapping is done from input schema to output schema based on the mapping definitions. Always by name!

[Former Member]

yes exactly. you got it right. I am pretty sure, mapping is not done purely on name.

may be mapping holds good between two Query transforms but not between a SQL transform and query transform.

let me explain you in detail.

suppose there are 5 columns initially(abc,def, ghi, jkl,mno) . which worked fine

now source schema added one more column 6th at the end (alphabetically last in the table, i.e. PQR) then the code might work. but if the newly added column comes in between the original 5 columns (say BCD)  then I can guarantee you it will swap the result set.

we have faced lot of issues on this. please revisit this once.

[former_member187605]

There' s no functional difference between output schema of any transform. Inserting a column between 2 other columns of an output schema of a query transform is exactly the same operation.

[Former Member]

what could be the reason for the problem we have been facing when ever there are any schema changes in the source?

[Former Member]

Can you please check on your target table, General Settings, how the Input columns are mapped to output ? Is it by name or position..

[Former Member]

Hi Ravi,

In the target table, we always use map by column name.

first when we faced this issue, it took lot of time (few days) to figure that the columns are getting swapped right after SQL Transform. we confirm that by placing a temp table after Query transform

that is joined with SQL transform.

Thanks,

Balaji

[Former Member]

Both of them are push down operations.Did you tried creating a procedure to output the data and see if the actual issue is with your network or with the db itself?