Usage of COPY FROM STDIN and pqputCopyData in C# .Net

[Srivathsal V]

Hello,

I am new to Npgsql. I am trying to make use of this for a windows app, and our requirement is to reduce the time taken to insert 2 to 3 million rows into a local/embedded DB file on the client, currently it takes 3 minutes to insert 2 million rows in SQLite if we do it in batches of 60000 per transaction. I have tried this using NpgsqlDataAdapter (code snippet pasted below), but it takes around 20 seconds to insert a batch of 60000 rows, where as SQLite takes 2 to 3 seconds for the same. Can anyone help me to use COPY FROM STDIN to insert data from an in memory data structure (not from a text/csv file).

                var connection = new NpgsqlConnection(connStr);

                connection.Open();

                NpgsqlCommand cmd = new NpgsqlCommand() { CommandType = "INSERT INTO Logs (c1,c2,c3,c4) VALUES (@c1,@c2,@c3,@c4)" };

                    

                NpgsqlDataAdapter da = new NpgsqlDataAdapter { InsertCommand = cmd };

                if (da.InsertCommand != null)

                    da.InsertCommand.Connection = connection;

                

            NpgsqlParameter c1= da.InsertCommand.Parameters.Add("@c1", NpgsqlTypes.NpgsqlDbType.Integer);

            c1.SourceColumn = "c1";

            c1.SourceVersion = DataRowVersion.Current;

            NpgsqlParameter c2= da.InsertCommand.Parameters.Add("@c2", NpgsqlTypes.NpgsqlDbType.Integer);

            c2.SourceColumn = "c2";

            c2.SourceVersion = DataRowVersion.Current;

            NpgsqlParameter c3= da.InsertCommand.Parameters.Add("@c3", NpgsqlTypes.NpgsqlDbType.Integer);

            c3.SourceColumn = "c3";

            c3.SourceVersion = DataRowVersion.Current;

            NpgsqlParameter c4= da.InsertCommand.Parameters.Add("@c4", NpgsqlTypes.NpgsqlDbType.Date);

            c4.SourceColumn = "c3";

            c4.SourceVersion = DataRowVersion.Current;

                da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

                da.InsertCommand.Prepare();

                NpgsqlTransaction trans = connection.BeginTransaction();

                DataTable logEventsTable =

                    this.ConvertToLogEventsDataTable(logEvents, areNewLogs);

                logEventsTable.Locale = CultureInfo.CurrentCulture;

                insertCount = da.Update(logEventsTable);

                trans.Commit();

                trans.Dispose();

                da.Dispose();

                logEventsTable.Clear();

                connection.Close();

[Gauthier Segay]

Srivathsal, please look at those:

http://devcrackers.blogspot.com/2013/06/npgsqlcopyin-and-npgsqlcopyserializer.html

https://gist.github.com/smoothdeveloper/0c5c0f6bbdad8b3b18af

I haven't optimized the gist, it might be better to flush after a
batch of rows and at the end rather than after every row

Please let us know if it helps.

> --
> You received this message because you are subscribed to the Google Groups
> "Npgsql Dev" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to npgsql-dev+...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Srivathsal V]

Hello Gauthier Segay,

Thanks a lot. I was able to implement like it was explained in the links you posted. And I now it is slightly better than SQLite's performance. Postgres is advantageous than SQLite in a lot of other areas, the only area where it wasn't good was bulk upload. 

Thanks again for your timely response.