I'm programming a task in c# where lots of records are produced and have to be saved right into a database. Right now things i do (that is VERY slow) is store many of these results as a listing of structs. Then in the finish iterate through this struct and add all the records for an sql query string. The problem with here it is takes age range to iterate through a listing if this consists of 100000s of products. An identical size card inserts must be carried out several occasions within the simulation. I have considered just storing the string in the off and instead of storing the records in a listing place it in to the string directly. Also possibly storing these questions temporary file and taking advantage of sql copy. I do not genuinely have much knowledge about coping with this quantity of data so that your feedback is going to be appreciated.

Thanks ahead of time

What you need to try is inhabiting personal files together with your data then while using built-in COPY command. This is actually the suggested approach to inhabiting a database. http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

When building the CSV temp file be certain to stick to the CSV spec. In case your column data consists of new lines (n r), commas (,) or quotes (") then escape the quotes (") with quotes

data=data.Replace("\"", "\"\"");

and surround the information with quotes


Something similar to

public String CSVEscape(String columnData)
    if(columnData.Contains("\n") || columnData.Contains("\r") || columnData.Contains("\"") || columnData.Contains(","))
        return "\"" + columnData.Replace("\"", "\"\"") + "\"";
    return columnData;

If I am reading through your question properly, you are delivering the PostgreSQL server a string that appears something similar to this:

INSERT INTO mytable (x, y, z) VALUES (1,2,3), (4,5,6), ...

List of positive actions rather is

  1. begin a transaction
  2. prepare the statement INSERT INTO mytable (x, y, z) VALUES ($1, $2, $3)
  3. for every struct inside your list, execute the prepared statement using the appropriate fields
  4. commit the transaction

(Sorry, no code, because I'm not sure C#'s DB APIs.)

I would not bother determining COPY IN unless of course the approach I referred to above continues to be far too slow. I recieve nervous when placing data right into a database requires any kind of text munging on my small part.

For those who have a minimal performance by utilizing OOP approach, so using structs/ classes, first factor to complete, would be to measure and optimize code whenever possible.

When the performance despite optimisation bad inside your specific context, I'd leave OOP approach and pass to raw SQL.

Among solution could be, as if you stated in publish, throughout generation of string for every entity, immediately combine it with large file, where in the finish of generation you'll find complete huge SQL string. The issue here's testability of solution.

But, you realize, somewhere you have to "pay". You can't possess a comfott along with a performance contemporary on such scale.