Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2014
    Posts
    3

    Question Unanswered: Transfer an Advantage Database's Schema/Data to a PostgreSQL Platform Using C# Progra

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest reading the PostgreSQL documentation 14.4. Populating a Database section.

    In general:
    1. Use the PostgreSQL COPY command
    2. If possible, defer the creation of constraints and indexes until after the data is loaded
    3. Use ANALYZE after the data is loaded and constraints and indexes are built
    There may be other settings or tips depending on your specific circumstances.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2014
    Posts
    3
    Quote Originally Posted by Pat Phelan View Post
    I'd suggest reading the PostgreSQL documentation 14.4. Populating a Database section.

    In general:
    1. Use the PostgreSQL COPY command
    2. If possible, defer the creation of constraints and indexes until after the data is loaded
    3. Use ANALYZE after the data is loaded and constraints and indexes are built
    There may be other settings or tips depending on your specific circumstances.

    -PatP
    Thanks for the response. I was actually aware that COPY was the most effective way to load the data into the database; however, this is not the area of the program that is taking the longest time.

    The current problem area in my code is using the ODBC DataAdapter to fill a datatable in C#. This is where the data is pulled from the Advantage Database.

    The specific code for the area that takes the longest time is...

    string query = "SELECT * FROM \"[tableNames]\"";
    OdbcDataAdapter adapter = new OdbcDataAdapter(query, myConnection);


    tableEntries.BeginLoadData();
    adapter.ResetFillLoadOption();
    adapter.Fill(tableEntries);
    tableEntries.EndLoadData();

  4. #4
    Join Date
    Jun 2014
    Posts
    3
    Quote Originally Posted by Pat Phelan View Post
    I'd suggest reading the PostgreSQL documentation 14.4. Populating a Database section.

    In general:
    1. Use the PostgreSQL COPY command
    2. If possible, defer the creation of constraints and indexes until after the data is loaded
    3. Use ANALYZE after the data is loaded and constraints and indexes are built
    There may be other settings or tips depending on your specific circumstances.

    -PatP
    Thanks for the reply; however, I was actually aware of this COPY command being very effective. This is not the area in my code taking the most time. The most ineffective area actually occurs when taking data from the Advantage database and putting it into a datatable using the ODBC DataAdapter. The code is below.

    string query = "SELECT * FROM \"[tableNames]\"";
    OdbcDataAdapter adapter = new OdbcDataAdapter(query, myConnection);
    tableEntries.BeginLoadData();
    adapter.ResetFillLoadOption();
    adapter.Fill(tableEntries);
    tableEntries.EndLoadData();

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I understand your preference for using C# code, but ADO is built for small data sets and you appear to be working with a rather large data set.

    I would use BCP at the Windows command level to create a CSV from your table or if the table requires formatting I'd create a query or view to do that formatting. BCP will create the file far faster than C# can read the file using ADO.

    Once the file exists, use the PostgreSQL COPY command as you've already noted.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •