Database Mapping app performance issue. Any suggestion ?

Question

Hi all I'm involve on a project which going to map database record from one DBMS into another DBMS. When the record number is small there no problem but now as the record number grow and grow rapidly there a performance issue. here my situation i'd like to append (and mapping) several record from one DBMS table into another DBMS table. Before append the record user has capability to map a record into an SQL Expression. My problem is before is do the append ( and mapping ) processes i need to validate the expression againts backend. So in this case i need to validate an expression againts all record on single table from DBMS or is there any other way to validate those SQL expression ? Or any suggestion for me ? FYI for appending and mapping 10 record of 18 columns table into 14 columns of table it took about 20 second to complete. prety long time for me. The process need to verify SQL expression from source table this verify process i've described above and my test table contain only 70000 records which i think a small number of record. any suggestion will be appraciated. thx.

Amit Choudhary · Answer

Hi Johan Well you can use Dataset to fetch the records from first DBMS and while if you are in Tier application then you can optimize the Dataset by setting its property RemoteFormating=Binary before sending to the network. The binary formatter compress the data in dataset to a good extent. for Verification of Dataset you can use Schmea's to match from other DBMS. After verification you can just push all the data to another DBMS using DataAdapter. Hope it helps you.