hi all !!!

this is my scenario (simplified for this post):

Source: Informix database with single table with more than 100 hundred
MILLIONS of records
Destination: SQL2000 with two tables (A and B)
Requirement: write a task that reads from the huge Informix table and,
depending on conditions on the source row,:
* do nothing
* transform and write to table A
* transform and write to table B

The question is: whats the more efficient way to do this ?

thanks in advance,
ant.

Are you always moving 100 Million rows?
This is gonna take some time regardless of method.

Options

1.

Straight pump into a working table on SQL Server then use TSQL to do the
INSERTS. Set batch sizes so your log doesn't blow up.

Adv: Set based
Dis: Two movements

2. Use Lookups and as the rows come across use these to do the deciding.

Adv: One movement
Dis: Slow as hell. You have to read each row individually and process it.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Antonio Lopez Arredondo" <adog (AT) someplace (DOT) com> wrote

personally I would go for this option.

transfer all the records to a flat table in sql using bulk insert.
create indexes on the flat table
start your transformations by using select into statements to the table A
and table B.

This is how we performed data transfer of approx the same amount records.

Going for bulk inserts will perform the best performance

Another option is to create views in the informix database and transfer the
view resultsets to sq.

--
Regards,

Dandy Weyn
MCSE, MCSA, MCDBA, MCT

www.dandyman.net
"Antonio Lopez Arredondo" <adog (AT) someplace (DOT) com> wrote

On Sun, 18 Jan 2004 11:42:25 -0600, "Antonio Lopez Arredondo" wrote:

G'day Antonio,

Is it not possible to use a view or an SQL statement to select only
table A's records or table B's records?

If it is, then this might be much easier. You give the partitioning work
to INFORMIX, only drag the required data across the network (i.e. you
never pull the "do nothing" records across the network), and can use a
copy transform in DTS to put the data into SQL Server.

If you are not allowed to create views in INFORMIX to give you this
data, then specifying the SQL statement (instead of table name) for the
copy transform will get you there.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello