DSXchange

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 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)

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();"

ArndW wrote: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_ ...

It is around 50000 records.

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?

ArndW wrote: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? ...

select count(*) from nv = 53287608
select count(*) from nv where fund_id in (select managerid from #temp) = 50000

Look at my query again, what about the "fund_dt < getdate()" part?

ArndW wrote:Look at my query again, what about the "fund_dt < getdate()" part? ...

Nothing that much diffference. 53247608

How to move hash file content to a temporary table?

Pretty much just like you'd think - source from the hashed file, write to your database. Nothing tricky.