Any advice about large size database transfer by c#?

[Dancefire]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

What i am tring to do is to transfer a database tabe SRC to another
database DEST. Actually it's not only simple transfer, but the row in
DEST is calculated by row in SRC.
For example:

We have a database table SRC, there are columns A, B, C. There are 5
million records in SRC, and totaly is about 500MB.
We have a database table - DEST, which has column AA, BB, CC, DD and
EE. AA, BB and CC is calculate by SRC.A and SRC.B by one go. DD and EE
is calculate by SRC.B and C by one go. you can run algorithm 3 time to
get AA, BB and CC seperatedly, but it will be 3 times slow, and should
not be happen. And the algorithm is complicated which means, should
not be implemented in store procedure.

We want to migrate the data from SRC to DEST. what is the best
solution to get smallest memory and disk cost?

I have tried simple way dataadapterSrc.Fill(datatableSrc) and then
foreach row in datatableSrc to calculate the result and add to
datatableDest table. But after fill, it almost cost my about 800MB
memory, and during my datatableDest.Rows.Add(row) operation. it run
out of memory. and stopped.

and I tried to use DataReader, it's better, but the situation of
datatableDest is same.

I tried to use pure SqlCommand to do the "Inser into " option. But
when I use Access database, the database size increasing dramatically.
and soon reach the 2GB size limits. But after my shrink the database,
it's actually only about 100MB. Maybe too many log in mdb, and too
many garbage space caused by single insert command, but I don't how to
prevent this happen.

I think the perfect solution is just like a pipeline, without keeping
any old records in memory and will not increase the database size
unreasonable, read 1 or 100 and save them, release the resouce and
read more.

Is there any better solution to solve the problem?

Thanks.

Dancefire
???
CCNA
http://www.dancefires.com/
http://blog.csdn.net/dancefire/
MSN: (e-mail address removed)
I am interested in Operating System, Embedded System and Network Security.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFDbpF6RS5AkKgtcCcRAgacAKCNBKSsWwcSdjwDIkITnLBRrjSIlACeJhcr
ofrFVQtgtieYyx8GQ7NWlzQ=
=FEJh
-----END PGP SIGNATURE-----

 

[Guest]

it might be better 4 ya to consider transferring data in a way that specific
to the database implementation instead of resorting to c# (IMO)

1. replicate the data (simply clone) across to the target implementation

2. write a stored procedure in the target to preform the data calculation
alone with the transferring

hope this helps

 

[Dancefire]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

it might be better 4 ya to consider transferring data in a way that
specific to the database implementation instead of resorting to c#
(IMO)

1. replicate the data (simply clone) across to the target
implementation

2. write a stored procedure in the target to preform the data
calculation alone with the transferring

hope this helps

Thanks, the two reason I don't want to use store procedure. first, the
algorithm of data processing is complicated, it's not suitable to use
store procedure to implemented; second, I can't fix the target
database platform, and store procedure is not portable, so I'd better
don't use store procedure.

Dancefire
- --
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFDbuHyRS5AkKgtcCcRAofUAKCVa3K7CzPjq/fcGS9e0akwPb8WbgCePOSR
f1vjM/cUx1/gmLyC3GJefh8=
=Bi0i
-----END PGP SIGNATURE-----

 

[Guest]

Well, as i reckon its pretty much like a database problem anyway.
Wud ya plz specify the database 4 the job to be done. prolly, we cud sort
this out technically.

cheers