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
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm