I am currently in the process of writing a .NET program with C# to transfer a database on a Sybase/Advantage 9.1 platform to a PostgreSQL 9.3.4 platform. I am doing this transfer in two stages - transfer all the tables/schema without the data and then transferring the data. I was able to transfer the schema without much of a problem; however, I am having issues with transferring the data (mainly performance issues as there are multiple tables with over 4 million entries).
My current approach for the data transfer is as follows...
- Establish an ODBC connection to the Advantage database
- Create an ODBC Data Adapter
- Fill a Data Table with the data entries using the method DataAdapter.Fill()
- Write the contents of the Data Table to a CSV file
- Establish an ODBC connection to the PostgreSQL database
- Use the COPY query to upload the CSV file into the database
This current method works and correctly transfers the data. However, I calculated that it would take 45 minutes to transfer a table with 5 million entries. I need this to work much faster. The longest portion of my current method is the DataAdapter.Fill().
Does anyone have any suggestions on how to do this transfer more efficiently and in less time? Either a suggestion to improve my current method or a different approach entirely. I would like to stick to using C# and the .NET platform to write a program for this transfer.
Just to add more details. Below is the portion of code that is currently taking the most time and seems to be the trouble area.
string query = "SELECT * FROM \"[tableNames]\""; OdbcDataAdapter adapter = new OdbcDataAdapter(query, myConnection); tableEntries.BeginLoadData(); adapter.ResetFillLoadOption(); adapter.Fill(tableEntries); tableEntries.EndLoadData();