Date Range Lookups
Posted: Mon Sep 11, 2006 2:52 am
Hi All
I want to perform a date range lookup Something like
Now we are doing a lookup directly with the database. But this affects the performance very much. we are getting 10+ rows per second only.
And based upon the suggestions from this forum, i tried using Universe Stage also, but it doesnt improve the performance much.
And in some other posts viewtopic.php?t=102858, DSguru2B has mentioned that we can do this with the Hashed File by intorducing a dummy Key Filed and match it with @INROWNUM
My Question is, how this logic will work.
1) If we mention KEY1 and KEY2 alone as key columns in the Hashed file , then the values can be overwritten.
We will have the Second row only in the Hashed File if we make KEY1 and KEY2 as keycolumns.
2) If we make the Date fields also as KeyColumns and if we pass EFF_DT form the source, the lookup wont match because it will try to do a equivalent match.
3) This will be same case, if we introduce a dummy sequential key field in Hashed file and match it with the @INROWNUM.
Please Suggest me how to proceed with this.
Note :
My Source and target is having around 1 million rows.
I want to perform a date range lookup Something like
Code: Select all
Source.key1 = lkp.key1 and
source.key2 = lkp.key2 and
lkp.EFF_START_DT <= Source.EFF_DT and
lkp.EFF_END_DT >= Source.EFF_DT
And based upon the suggestions from this forum, i tried using Universe Stage also, but it doesnt improve the performance much.
And in some other posts viewtopic.php?t=102858, DSguru2B has mentioned that we can do this with the Hashed File by intorducing a dummy Key Filed and match it with @INROWNUM
Code: Select all
if NOT(RefLink.NOTFOUND) AND main_id = lookup_id AND ICONV(dt_record,"D/MDY[2,2,4]") >= ICONV(dt_start,"D/MDY[2,2,4]") AND ICONV(dt_record,"D/MDY[2,2,4]") <= ICONV(dt_end,"D/MDY[2,2,4]") then 'P' else 'N'
1) If we mention KEY1 and KEY2 alone as key columns in the Hashed file , then the values can be overwritten.
Code: Select all
For Ex.
Key1$Key2$Desc$Eff_Start_Dt$Eff_End_Dt
A$B$TestDesc$01/01/2005$12/31/2005
A$B$TestDesc1$01/01/2006$12/31/2006
2) If we make the Date fields also as KeyColumns and if we pass EFF_DT form the source, the lookup wont match because it will try to do a equivalent match.
3) This will be same case, if we introduce a dummy sequential key field in Hashed file and match it with the @INROWNUM.
Please Suggest me how to proceed with this.
Note :
My Source and target is having around 1 million rows.