Skip navigation
Contact Us
2532 Views 7 Replies Latest reply: Dec 31, 2014 6:06 AM by Srikanth Vakalapudi RSS
Srikanth Vakalapudi Newbie 6 posts since
Nov 9, 2012
Currently Being Moderated

Nov 14, 2012 4:37 PM

SQL Transformation performance issue

Hi All,

 

I have created a mapping and at the end I have SQL transformation which executes the SQL query (very simple by taking only 1 column) and created the Target table.  If I try to dump the data into Target table from SQL Transformation it is taking too long.  Could anybody help me how i can over come this problem?

 

Here is how i used.

 

It's a SQL transformation with Dynamic Query option (Query build from the table, so it's very simple query gives about 3.1 million records output, when i try to load them into Target table is taking too much time (may be more than 8 hrs).  Please help me how to run this quickly?  btw. there is no SQL override for this.

 

regards,

Srikanth

  • Felix Ho Expert 342 posts since
    Aug 23, 2011
    Currently Being Moderated
    Nov 14, 2012 6:26 PM (in response to Srikanth Vakalapudi)
    SQL Transformation performance issue

    Hi Srikanth,

     

    I have some questinos for your case.

     

    1.) Do you want to get one column from SQL Transformation (Like the function of lookup) ?

     

    2.) Do you mean SQL transformation query give about 3.1 million records output ??

     

    3.) How many source row do you have in your ETL?

     

    4.) For this sentense,

    **when i try to load them into Target table is taking too much time (may be more than 8 hrs).**

    Do you mean your ETL use 8 hrs? or use script or others??

  • Prasad Mundewadi Newbie 2 posts since
    Dec 30, 2014
    Currently Being Moderated
    Dec 30, 2014 6:16 AM (in response to Srikanth Vakalapudi)
    SQL Transformation performance issue

    Hi

    I am facing a very similar issue like you. DId you find any solution for this problem?

     

    Thanks

    Prasad

      • Prasad Mundewadi Newbie 2 posts since
        Dec 30, 2014
        Currently Being Moderated
        Dec 30, 2014 1:50 PM (in response to Srikanth Vakalapudi)
        SQL Transformation performance issue

        I have used SQL transformation in a mapping. I have used it to because i am deriving the column name dynamically.

        the SQL statement is like:

        Select parent_id, Child_id

        from Table

        where fieldid = ~variable~ (need to use variable as this value is diff for dev/test/prod)

         

        If i run this query it runs in 5 seconds on database. But while loading the data through ETL it takes 2 Hrs. The throughput on target table is 50 rows.

         

        I also created another mapping using the query with hardcoded value. This runs in 2 min with throughput of 2500 rows on same target table.

         

        I am pretty sure that the performance is slow because of using the SQL transformation. But not sure why? and how to improve the performance.

        Can you help here?

More Like This

  • Retrieving data ...

Bookmarked By (0)