Adding multiple rows to the server at a time.

[JarHead]

I'm trying to find the best, fastest way to add a large number of rows to my server at a time. The way I'm doing it now involves a loop that updates each row individually, which is a lengthy procedure. Is there a better way?

[Nerseus]

If this is SQL server, you can't really beat bcp though DTS is nearly as fast and is a lot more robust. For "batch jobs" that extract or import data from other sources (files, excel, another database) DTS is the recommended approach and is worth investigating.

 

-ner

[JarHead]

I'm using Visual Basic .net to interface with a Postgresql server. I'm not sure if those terms apply to anything but MS Sql server. I'm trying to add about 200,000 rows to this Postgresql server with the click of a button, and not have to wait all day for the operation to process. Any ideas?

[Nerseus]

If you must use code - eg, Postgresql doesn't support any kind of bulk copy - then I'd recommend dynamic SQL, batching up mutliple inserts (if that's what you need) into one chunk of SQL. Finding a balance is up to you, but I generally start with 50 records or so. Something like:

 

sql = "INSERT INTO Table1 (col1, col2) VALUES (123, 'Hello')" + Environment.NewLine
sql = sql + "INSERT INTO Table1 (col1, col2) VALUES (456, 'World')"

 

For "batch jobs" where speed is a concern, you should generally try to avoid "good programming" practices of using Command objects and Paramater objects as they're a lot slower than you probably want.

 

For 200,000 rows it's a toss up - that's kinda in-between size so you may not need to sacrifice "good programming" for speed. Maintenance is also important.

 

If you decide to not use dynamic SQL, maybe let us know some more details: * what kind of code you've got so far

* how big each row is (how many columns and relative sizes

* what kind of machine you're running against (the server)

* what kind of data source you have (where do you get your data)

* what you expect in terms of speed (how long to run)

 

-ner