locked
Pre-execute is taking forever RRS feed

  • Question

  • I have put all the fact packages in one master package and this packages has lot of lookups to join with dimension key. When I try to execute this package this master package it takes forever to start execution and in progress tab I can see that pre-execute is were it is really struck at. Each fact package transfers millions of rows the highest is 133 million.

    What could be the problem because this is making system to hang and takes lot of time time to execute.


    sree

    Monday, March 30, 2015 9:21 PM

Answers

  • No, you can't. What you can do is to divide the package so you load the first N Lookups to a staging table (or a Raw File, which is extremely fast) and then you load the rest in a different Data Flow (or even a different package). This way you would alleviate memory pressure. 

    You can also reduce the data size of your Lookups just querying the columns needed (usuallly, your business key and your surrogate key). Even if you are just querying these needed columns, reducing its width (casting varchar(100) into varchar(10) if possible, for example) would save you a lot of memory space. If you are loading 10 million rows in a Lookup task and you reduce 90 bytes (as in the example) per row, you'll save 900 million bytes of RAM, which is around 850 MB. And that's just for one column and for one Lookup task. 

    Regards.

    Pau

    Monday, March 30, 2015 9:45 PM

All replies

  • Hi Sree, 

    having that package architecture, I'd go for Lookups that load A LOT of rows in memory or which query is very complicated. Both aspects of the package are load (or resolved, in the case of a complex query) during the Pre Execute phase. 

    I'd check the source query (or queries) too, paying attention on how much time the relational engine takes to solve it and start returning rows. 

    Regards.

    Pau

    Monday, March 30, 2015 9:32 PM
  • Source queries are not that complex , but I think lot of memory is consumed by lookup transformation. Is there any way I can avoid this?

    sree

    Monday, March 30, 2015 9:37 PM
  • I mean can I avoid pre execute phase?

    sree

    Monday, March 30, 2015 9:38 PM
  • No, you can't. What you can do is to divide the package so you load the first N Lookups to a staging table (or a Raw File, which is extremely fast) and then you load the rest in a different Data Flow (or even a different package). This way you would alleviate memory pressure. 

    You can also reduce the data size of your Lookups just querying the columns needed (usuallly, your business key and your surrogate key). Even if you are just querying these needed columns, reducing its width (casting varchar(100) into varchar(10) if possible, for example) would save you a lot of memory space. If you are loading 10 million rows in a Lookup task and you reduce 90 bytes (as in the example) per row, you'll save 900 million bytes of RAM, which is around 850 MB. And that's just for one column and for one Lookup task. 

    Regards.

    Pau

    Monday, March 30, 2015 9:45 PM
  • Thank you, i'll give it a try.

    sree

    Monday, March 30, 2015 10:01 PM