Re: Enable Memory Restriction for Lookup Transformation
- From: jhofmeyr@xxxxxxxxxxxxxx
- Date: Fri, 2 May 2008 04:31:32 -0700 (PDT)
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
.
- References:
- Prev by Date: RE: SSIS - it's magic really
- Next by Date: RE: ActiveX Script in DTS Package
- Previous by thread: Enable Memory Restriction for Lookup Transformation
- Next by thread: Re: changing SQL Agent 'Logon as' to move backups off same drive,j
- Index(es):
Relevant Pages
|
