Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am using this example to export data from SQL Server to PostgreSQL, when I start the export as 300,000 rows takes 12 minutes, what I can do to speed up this process or you know another way to do it?

string SourceDriver = "Driver={SQL Server Native Client 10.0}";
OdbcConnection SourceConnection = new OdbcConnection(SourceDriver+ ";Server=10.10.10.10;Database=sourceMSSQL;Uid=sa;Pwd=12345;");

string DestDriver = "Driver={PostgreSQL}";
OdbcConnection DestConnection = new OdbcConnection(DestDriver+ ";Server=10.10.10.11;Port=5432;Database=destPostgreSQL;Uid=postgres;Pwd=12345;");

string SourceSql = "SELECT Code, Label, Model, List, Size, Quantity, City, Family,  ExportDate FROM MovPedidosP0";
string DestSql = "INSERT INTO tmp_MovPedidosP0_t (Code, Label, Model, List, Size, Quantity, City, Family,  ExportDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";

using(OdbcCommand SourceCommand = new OdbcCommand(SourceSql, SourceConnection))
{
    SourceConnection.Open();
    using(OdbcDataReader SourceReader = SourceCommand.ExecuteReader())
    {
        Console.WriteLine("Exporting...");

        DestConnection.Open();

        while(SourceReader.Read())
        {
            using(OdbcCommand DestCommand = new OdbcCommand(DestSql, DestConnection))
            {
                DestCommand.Prepare();
                DestCommand.Parameters.Clear();

                for(int i=0; i<SourceReader.FieldCount; i++)
                {
                    DestCommand.Parameters.AddWithValue("?ID" + (i+1).ToString(), SourceReader[i]);
                }

                DestCommand.ExecuteNonQuery();
                TotalRows++;
            }
        }

        DestConnection.Close();
    }
}

SourceConnection.Close();
share|improve this question
You might look into PostgreSql's batch processing: stackoverflow.com/questions/758945/… – Chris Lively Mar 21 at 23:07

2 Answers

Much simpler and probably faster if you export to a text file using SSIS and import with the COPY command.

share|improve this answer
Or even use SSIS to copy directly to the PostgresSQL database. – John Saunders Mar 21 at 23:27
I would use SSIS and COPY but I need to export the information through an application and is an end user who will use it. – byoigres Mar 21 at 23:53

Try using the native NpgsqlConnection and SqlConnection instead of Odbc connections.

http://npgsql.projects.pgfoundry.org/

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.