Moving "HASH" file content to a temporary table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
Moving "HASH" file content to a temporary table
All,
I have created two jobs.
Job:1
It will extract some set of data from a table and store it in a HASH file.
ServerA.(managerid->column name) -->Transformer --> Hashfile ( 1700 rows)
Job:2
It will fetch the fund_id from a table which has 5M records for the given date(say for example 15/jun/2009), then do look up with the hash file and then insert into a table only the matched rows.
Hash file
|
ServerB.(fund_id->column name) --> transformer --> Table
Query used in ServerB.
select fund_id,max(fund_dt)
from nv -- Table has 5M records
where fund_dt < getdate()
group by fund_id
clustered Index: fund_id,fund_dt
Table has 5M records. we need to fetch data only for those 1700 manager id's. so i would like to add one condition after the WHERE clause like
select fund_id,max(fund_dt)
from nv -- Table has 5M records
where fund_dt < getdate()
and fund_id in ( select managerid from #temp) -- Here is my requirement.
group by fund_id
I am using sybase database. version 12.5.3
1) Is it possible to move the hash file to a temporary table.
2) is there any other way to tune the above task? Becuase it is taking more than 15 minutes to complete the execution.
I have created two jobs.
Job:1
It will extract some set of data from a table and store it in a HASH file.
ServerA.(managerid->column name) -->Transformer --> Hashfile ( 1700 rows)
Job:2
It will fetch the fund_id from a table which has 5M records for the given date(say for example 15/jun/2009), then do look up with the hash file and then insert into a table only the matched rows.
Hash file
|
ServerB.(fund_id->column name) --> transformer --> Table
Query used in ServerB.
select fund_id,max(fund_dt)
from nv -- Table has 5M records
where fund_dt < getdate()
group by fund_id
clustered Index: fund_id,fund_dt
Table has 5M records. we need to fetch data only for those 1700 manager id's. so i would like to add one condition after the WHERE clause like
select fund_id,max(fund_dt)
from nv -- Table has 5M records
where fund_dt < getdate()
and fund_id in ( select managerid from #temp) -- Here is my requirement.
group by fund_id
I am using sybase database. version 12.5.3
1) Is it possible to move the hash file to a temporary table.
2) is there any other way to tune the above task? Becuase it is taking more than 15 minutes to complete the execution.
Karthik
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
Re: Moving "HASH" file content to a temporary tabl
I tested this process manually like
where fund_id in (1,4,8,1001,5778,84449)
The query executed within 10 seconds. Thats why i want to implement the logic
where fund_id in (select managerid from #temp)
where fund_id in (1,4,8,1001,5778,84449)
The query executed within 10 seconds. Thats why i want to implement the logic
where fund_id in (select managerid from #temp)
Karthik
Yes, you could load the hashed file into a temporary table. That would entail moving less rows from the database to DataStage. How many rows get returned with just "select count(*) from nv where fund_dt < getdate();"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
That isn't too large a number, why not use that query, then do a hashed file lookup in a transform stage instead of copying the hashed file to a db table?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
Look at my query again, what about the "fund_dt < getdate()" part?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm