Hi,

We can enable memory restriction (limit cache size) for “Lookup
Transformation" in Data Flow Task, can someone tell me how exactly it
effects the performance of the package?

I’m fetching 35 million records from source database and having lookup
against table which has almost 45 million records. So when I’m
executing the package, for first couple of minutes (approx 5-10 mins)
it caches the lookup table and then fetches the records from source
db, perform the real lookup and send it to destination.

Can someone tell me if I enable memory restriction on lookup
transformation how exactly it will perform lookup on record set which
is not cached and is there any risk in enabling the memory
restriction, I mean it may not be able to perform lookup up on non
cached data or something like that, is there any performance benefit/
drawback while enabling it?

As I'm new to SSIS & so may be my question sound very basic, silly or
repetitive so sorry for that.

Thanks in advance.

Cheers,
Manan

Hi Manan,

Using "Enable memory restriction" is generally aVery Bad Idea, unless
you are mapping very few rows against a very large lookup table. The
reason for this is that when you have this option checked, SSIS will
lookup and cache the lookup information row-at-a-time as it reads in
the source data.

In limited scenarios (e.g. large lookup table, but only a small number
of distinct lookup values expected in the source data) then using
memory restriction might not be a bad idea.

There are a few things you could try:
1) Use a SQL statement to define your Lookup source. Selecting "Table
or View" instead of a SQL statement prepares the statement in a
slightly less efficient way.
2) Restrict the returned columns. "SELECT *" is bad .. all the rows
in the lookup table are returned and cached even if they are not
actually used. Ideally you should only include the lookup key and
join columns in your query.
3) Index the lookup table.
4) Use a MergeJoin task. With large volumes of data this can
sometimes be quicker than a lookup.

Good luck!
J

Hi Manan,

Using "Enable memory restriction" is generally aVery Bad Idea, unless
you are mapping very few rows against a very large lookup table. The
reason for this is that when you have this option checked, SSIS will
lookup and cache the lookup information row-at-a-time as it reads in
the source data.

In limited scenarios (e.g. large lookup table, but only a small number
of distinct lookup values expected in the source data) then using
memory restriction might not be a bad idea.

There are a few things you could try:
1) Use a SQL statement to define your Lookup source. Selecting "Table
or View" instead of a SQL statement prepares the statement in a
slightly less efficient way.
2) Restrict the returned columns. "SELECT *" is bad .. all the rows
in the lookup table are returned and cached even if they are not
actually used. Ideally you should only include the lookup key and
join columns in your query.
3) Index the lookup table.
4) Use a MergeJoin task. With large volumes of data this can
sometimes be quicker than a lookup.

Good luck!
J

Hi Manan,

Using "Enable memory restriction" is generally aVery Bad Idea, unless
you are mapping very few rows against a very large lookup table. The
reason for this is that when you have this option checked, SSIS will
lookup and cache the lookup information row-at-a-time as it reads in
the source data.

In limited scenarios (e.g. large lookup table, but only a small number
of distinct lookup values expected in the source data) then using
memory restriction might not be a bad idea.

There are a few things you could try:
1) Use a SQL statement to define your Lookup source. Selecting "Table
or View" instead of a SQL statement prepares the statement in a
slightly less efficient way.
2) Restrict the returned columns. "SELECT *" is bad .. all the rows
in the lookup table are returned and cached even if they are not
actually used. Ideally you should only include the lookup key and
join columns in your query.
3) Index the lookup table.
4) Use a MergeJoin task. With large volumes of data this can
sometimes be quicker than a lookup.

Good luck!
J

Hi Manan,

Using "Enable memory restriction" is generally aVery Bad Idea, unless
you are mapping very few rows against a very large lookup table. The
reason for this is that when you have this option checked, SSIS will
lookup and cache the lookup information row-at-a-time as it reads in
the source data.

In limited scenarios (e.g. large lookup table, but only a small number
of distinct lookup values expected in the source data) then using
memory restriction might not be a bad idea.

There are a few things you could try:
1) Use a SQL statement to define your Lookup source. Selecting "Table
or View" instead of a SQL statement prepares the statement in a
slightly less efficient way.
2) Restrict the returned columns. "SELECT *" is bad .. all the rows
in the lookup table are returned and cached even if they are not
actually used. Ideally you should only include the lookup key and
join columns in your query.
3) Index the lookup table.
4) Use a MergeJoin task. With large volumes of data this can
sometimes be quicker than a lookup.

Good luck!
J

Hi Manan,

Using "Enable memory restriction" is generally aVery Bad Idea, unless
you are mapping very few rows against a very large lookup table. The
reason for this is that when you have this option checked, SSIS will
lookup and cache the lookup information row-at-a-time as it reads in
the source data.

In limited scenarios (e.g. large lookup table, but only a small number
of distinct lookup values expected in the source data) then using
memory restriction might not be a bad idea.

There are a few things you could try:
1) Use a SQL statement to define your Lookup source. Selecting "Table
or View" instead of a SQL statement prepares the statement in a
slightly less efficient way.
2) Restrict the returned columns. "SELECT *" is bad .. all the rows
in the lookup table are returned and cached even if they are not
actually used. Ideally you should only include the lookup key and
join columns in your query.
3) Index the lookup table.
4) Use a MergeJoin task. With large volumes of data this can
sometimes be quicker than a lookup.

Good luck!
J

Hi Manan,

Using "Enable memory restriction" is generally aVery Bad Idea, unless
you are mapping very few rows against a very large lookup table. The
reason for this is that when you have this option checked, SSIS will
lookup and cache the lookup information row-at-a-time as it reads in
the source data.

In limited scenarios (e.g. large lookup table, but only a small number
of distinct lookup values expected in the source data) then using
memory restriction might not be a bad idea.

There are a few things you could try:
1) Use a SQL statement to define your Lookup source. Selecting "Table
or View" instead of a SQL statement prepares the statement in a
slightly less efficient way.
2) Restrict the returned columns. "SELECT *" is bad .. all the rows
in the lookup table are returned and cached even if they are not
actually used. Ideally you should only include the lookup key and
join columns in your query.
3) Index the lookup table.
4) Use a MergeJoin task. With large volumes of data this can
sometimes be quicker than a lookup.

Good luck!
J

Hi Manan,

Using "Enable memory restriction" is generally aVery Bad Idea, unless
you are mapping very few rows against a very large lookup table. The
reason for this is that when you have this option checked, SSIS will
lookup and cache the lookup information row-at-a-time as it reads in
the source data.

In limited scenarios (e.g. large lookup table, but only a small number
of distinct lookup values expected in the source data) then using
memory restriction might not be a bad idea.

There are a few things you could try:
1) Use a SQL statement to define your Lookup source. Selecting "Table
or View" instead of a SQL statement prepares the statement in a
slightly less efficient way.
2) Restrict the returned columns. "SELECT *" is bad .. all the rows
in the lookup table are returned and cached even if they are not
actually used. Ideally you should only include the lookup key and
join columns in your query.
3) Index the lookup table.
4) Use a MergeJoin task. With large volumes of data this can
sometimes be quicker than a lookup.

Good luck!
J

Hi Manan,

Using "Enable memory restriction" is generally aVery Bad Idea, unless
you are mapping very few rows against a very large lookup table. The
reason for this is that when you have this option checked, SSIS will
lookup and cache the lookup information row-at-a-time as it reads in
the source data.

In limited scenarios (e.g. large lookup table, but only a small number
of distinct lookup values expected in the source data) then using
memory restriction might not be a bad idea.

There are a few things you could try:
1) Use a SQL statement to define your Lookup source. Selecting "Table
or View" instead of a SQL statement prepares the statement in a
slightly less efficient way.
2) Restrict the returned columns. "SELECT *" is bad .. all the rows
in the lookup table are returned and cached even if they are not
actually used. Ideally you should only include the lookup key and
join columns in your query.
3) Index the lookup table.
4) Use a MergeJoin task. With large volumes of data this can
sometimes be quicker than a lookup.

Good luck!
J