December 4, 2009 at 2:48 am
We use Sqlserver 2005 and we try to boost some performance in a SSIS package which contains lookups. The lookups regarding dimension tables, which contain timeframes (SCD type 2).
The lookups therefore are adjusted by modifying the sql statement (by default a lookup can only handle equi joins).
When we set the cachetype of the lookup to "Full" the modified sql statement is overruled (greyed out) and the default sql statement has become leading. Only the cachetypes partial or none are available but despite defining the right indexes the performance with these cachetype is poor.
The default statement is like:
select customerid, customernumber, customerstartdate, customerenddate
from DimCustomer
The modified (the one should be used) sql statement is like:
select * from (select customerid, customernumber, customerstartdate, customerenddate from DimCustomer )as reftable
where 1=1
and reftable.customernumber = ?
and ? between reftable.customerstartdate and reftable.customerenddate
first ? = input customernumber
second ? = input date
Can anybody tell us if we overlook something or is this a feature of 2005 and hopefully solved in 2008 (For Example in the ETL tool Powercenter this is not a problem)
Thanks in advance for your help.
Joost
December 6, 2009 at 1:43 pm
The way SSIS handles look ups, is basically store all in memory and try to equi-join on values stored in mem. This never enables you to create in between results..
The link below explains in more detail the difference between full, partial and no cache of the lookup component.
http://www.ssistalk.com/2009/09/04/ssis-lookup-cache-modes-full-partial-none/
http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range,
Above link tells you how to accomplish this. The major disadvantage is now each entered row (in the flow) triggers the execution of the SQL query, with the given parameters. Query execution time now slows down the overall performance. This is not a SSIS 2005 issue this is how it works; the intention of partially or no caching was to overcome memory issues when dealing with large lookup sets.
It is similar to your construct, and by the way you should remove
'where 1=1' in your SQL construct.
Find out this blog to see alternatives:
http://blogs.msdn.com/mattm/archive/2008/11/25/lookup-pattern-range-lookups.aspx
This is 2008 stuff but most of it is still valid for 2005. The C.script has to be realtered to 2005 format in VB.NET
Any questions feel free to ask.
Regards Kees
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply