| Author |
Topic |
|
sorrentmutie
Starting Member
12 Posts |
 Posted - 2004-12-02 : 09:25:53
|
| Dear friends,I would like to listen some advices about the following problem.I wrote a vb.net application that stores in a sql server 2000 database a lot of quantum-mechanics coefficients.Let us suppose I'm dealing just with one table. This table will contain a key, 4 integer columns and one real column. On average this table will contain order of 10^7-10^8 rows.I'm testing different strategies to fill this table.I decided to use visual basic.net. I don't know if this is the best solution (however, much better than vb 6.0 and ado).I can fill about 1500 rows/sec on a Pentium 4 2.8 Ghz using a data adapter, a dataset, filling the local dataset and then updating the database with a sqlcommand. I tested the following situations:1) Just 1 update for job at the end of the local row's generation2) Two Updates for job: at the half and at the end of the job3) Four Updates for job.Usually a job fills 10^6 rows.I get the best results with the second solution while the first and third case give the same results.What is your experience with these large updates?Thanks for your kind attention!Salvatore |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-02 : 09:29:16
|
| Fastest method of getting data into a table is bulk insert from a text file. Would expect it to be lots faster than you are seeing there but you will need to create the text file first.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sorrentmutie
Starting Member
12 Posts |
Posted - 2004-12-02 : 10:29:16
|
quote:
Originally posted by nr
Fastest method of getting data into a table is bulk insert from a text file. Would expect it to be lots faster than you are seeing there but you will need to create the text file first.
So, you are suggesting me to write all the data to a text file and then run a script to load the database?Salvatore |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-02 : 10:35:42
|
| Yep - that's probably the fastest method.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2004-12-02 : 10:37:02
|
| That could well be the fastest solution. If you look at what's happening when you use your dataset, you will see a shitload of inserts happening (lots of roundtrips). Caching it as a text file and running BCP might be faster, since you insert 10^6 rows at a time. |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-12-02 : 10:37:16
|
quote:
Originally posted by sorrentmutie
quote:
Originally posted by nr
Fastest method of getting data into a table is bulk insert from a text file. Would expect it to be lots faster than you are seeing there but you will need to create the text file first.
So, you are suggesting me to write all the data to a text file and then run a script to load the database?Salvatore
What's the original format of the data they you receive?!------------->>> BREAKING NEWS!!! <<<------------- Saddam Hussien has weapons of mass destrcution |
|
|
|
sorrentmutie
Starting Member
12 Posts |
Posted - 2004-12-02 : 10:42:55
|
| My program simply calculates these coefficients that I then write into the database.Salvatore |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-12-02 : 10:46:40
|
quote:
Originally posted by sorrentmutie
My program simply calculates these coefficients that I then write into the database.Salvatore
Then try as suggested by the guys and output the calculation to a text file then use the shell() function with in your VB code to run the BCP command to load the data in to your table. Should be straightforward enough.  ------------->>> BREAKING NEWS!!! <<<------------- Saddam Hussien has weapons of mass destrcution |
|
|
|
sorrentmutie
Starting Member
12 Posts |
Posted - 2004-12-02 : 10:50:24
|
quote:
Originally posted by AmethystiumThen try as suggested by the guys and output the calculation to a text file then use the shell() function with in your VB code to run the BCP command to load the data in to your table. Should be straightforward enough.
Ok, I'll try and let you know. I learned a lot of things today. Thanks!Salvatore |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-02 : 10:52:14
|
| Just bulk inserted a million rows (4 ints and a real) into a table on my desktop - took 27 secs.Would expect it to be faster on a server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-12-02 : 10:53:10
|
quote:
Originally posted by sorrentmutie
quote:
Originally posted by AmethystiumThen try as suggested by the guys and output the calculation to a text file then use the shell() function with in your VB code to run the BCP command to load the data in to your table. Should be straightforward enough.
Ok, I'll try and let you know. I learned a lot of things today. Thanks!Salvatore
Cool!  you can try something like this :Shell("bcp yourDatabase.dbo.yourTable in c:\CalculationFile.TXT -c -T")Might be a start ------------->>> BREAKING NEWS!!! <<<------------- Saddam Hussien has weapons of mass destrcution |
|
|
|
sorrentmutie
Starting Member
12 Posts |
Posted - 2004-12-03 : 06:40:52
|
quote:
Originally posted by AmethystiumShell("bcp yourDatabase.dbo.yourTable in c:\CalculationFile.TXT -c -T")
Hello to everyone,I'm sorry to bother you with a rather "trivial" problem.I generated a txt file in the following format:1,1,1,1,0.7055475containing 10000 rowsThis file does not specify the primary key and probably this produces the following error when I run the command you suggested me:Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP fileI read the documentation about the format file option. So I created the following file:8.051 SQLINT 0 4 "," 2 int12 SQLINT 0 4 "," 3 int23 SQLINT 0 4 "," 4 int34 SQLINT 0 4 "," 5 int45 SQLFLT8 0 8, "\r\n" 6 realebut the format file is not readError = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format fileCan you help me again?Thank you very much in advanceSalvatore |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-03 : 06:52:32
|
| Might be that you are missing the final CR in the file, or that the final CR is causing a "blank row" to be [attempted to be] processed-c defaults to TAB between fields and "\n" between rows - I don;t know wheter that means "newline" ONLY or any sort of "line break"You might want to trybcp yourDatabase.dbo.yourTable in c:\CalculationFile.TXT -T -t, -r\r\n(not sure if I've got the "\r" & "\n" the right way round!)Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-03 : 07:00:33
|
| That file doesn't need a format file.Needs -c -t, switches.-c will default to \n (crlf) for row terminator so only needs the -t,.seehttp://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sorrentmutie
Starting Member
12 Posts |
Posted - 2004-12-03 : 07:03:35
|
quote:
Originally posted by Kristen
Might be that you are missing the final CR in the file, or that the final CR is causing a "blank row" to be [attempted to be] processed-c defaults to TAB between fields and "\n" between rows - I don;t know wheter that means "newline" ONLY or any sort of "line break"You might want to trybcp yourDatabase.dbo.yourTable in c:\CalculationFile.TXT -T -t, -r\r\n(not sure if I've got the "\r" & "\n" the right way round!)Kristen
Hi Kristen,I tried to use your sintax. The shell then asks me for the formatof each column, including the primarykey that is not include in the text file. Then it writes just one row with completely wrong values...Salvatore |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-03 : 08:05:42
|
| Maybe you need -c then to stop it asking for the format fileI would go with what Nigel says - i.e. -c plus a definition for the column separated of -t,Kristen |
|
|
|
sorrentmutie
Starting Member
12 Posts |
Posted - 2004-12-03 : 08:11:47
|
quote:
Originally posted by nr
That file doesn't need a format file.Needs -c -t, switches.-c will default to \n (crlf) for row terminator so only needs the -t,.seehttp://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html
Hi!At the end it worked. The problem was with the missing key inthe text file as I read form the link you suggested me.I simply (Please don't smile about my pratical approach1) added a a first column in the text file with a costant value.The command worked ignoring the costant value in the file, putting the appropriate key and all the others values!Salvatore |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-03 : 08:46:28
|
| I take it you have an identity. I would have done it by creating a view on the table which excludes the identity and bcp into that but whatever works is ok.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sorrentmutie
Starting Member
12 Posts |
Posted - 2004-12-03 : 08:58:24
|
quote:
Originally posted by nr
I take it you have an identity. I would have done it by creating a view on the table which excludes the identity and bcp into that but whatever works is ok.
Yes, you are absolutely correct. As usual I need a first quick answer to gain confidence. Now I can start to think about a real solution. It's not clear to me if I want to use an identity: the real number is related to a quantic state expressed by the four integer numbers.In principle I'd use 4 keys but it seems to me too much expensiveand not so useful.Every table represents a particular molecular system and each table is written by a series of consecutive jobs. I'm "sure" that is not possible to write two equal rows. Thanks againSalvatore |
|
|
|
|
Strategy with large updates