Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-16 : 08:34:18
|
| Rob writes "What is the fastest way to load data into SQL Server?Several times over the past few years I have faced the same challenge. I get a fairly large quantity of data (say 10 - 1000 Mbytes); it may come from SQL Server or from some other source, in which case I usually get it as a text file; I read the data in Visual Basic; I process it; and then I need to load results into SQL Server tables. The volume of data to be loaded is again quite large, say one hundred megabytes. I find that this last step, loading to SQL Server, can easily take longer than the other parts of my program (reading input, number crunching). I am looking for the quickest way of getting data into SQL server. ADO Recordsets with .Addnew or .Update are a non-starter, as far as performance goes. ADO can give acceptable preformance for reading data into the program, either using .Getrows or just looping with .Movenext. But trying to insert or update is just too slow. I have tried using asynchoronous operation, but that doesn't help much.The fastest method I have come with so far is to write to a flat file using VB "Print #File, ..." and then do a T-SQL BULK INSERT. This works quite well and is fast. But it is a bit messy - I have to decide where to put the file, clean up afterwards, have permission to bulk insert - so I wonder if there is a better way?Well is there a better way? A way that is as fast, or faster; and also easier to manage?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-16 : 08:39:07
|
| BULK INSERT or bcp are the fastest ways. All the other methods should be avoided as they use row-by-row processing, which is death. Don't do it. Cleanup isn't really a problem even for large files; making sure you have enough disk space is usually harder. I've had to manage a couple of 1-2 GB text files and the only problem was finding space; this was made easier by putting them on a network server with tons of room. It's a little slower than direct disk access, but since you'd have to copy the file over to the server anyway it'll probably take just as long in the end.Any reason why you can't import the raw file into SQL Server and then manipulate it there? This is known as "staging", if you search the SQL Team forums for that term you'll find a number of articles that describe the process in more detail. Usually it's easier to clean up data inside SQL Server as it can bring the full power of set-based operations on large numbers of rows. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-10-16 : 08:41:06
|
| Bulk Insert is fastest. Followed closely by using BCP. Then comes a DTS datapump task. The slowest (by far) would be row-by-row inserts from some other program (VB via ADO or whatever).Your best bet is to bulk insert the data raw from your text file and use T-SQL DML from there to do any processing and/or modification/manipulation.Jay White{0} |
|
|
|
rvn625
Starting Member
1 Post |
Posted - 2003-10-17 : 01:55:14
|
| Thanks for your answers.Is there any way to hook into the library that bcp uses, so that I can get my data in without having to write it to a file? The overhead is not great, but it would be neater to avoid it. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-10-17 : 06:01:54
|
| Never heard anybody did this trick.Even DMO.BulkCopy object works only with files. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-10-17 : 08:20:14
|
I did once try pointing a BULK INSERT at a named pipe... I think it worked, but it wasn't very convenient to say the least.Hmm... eventually I found the little program that was creating the pipe and stuffing data into it, but it didn't work with BULK INSERT or bcp, so maybe I imagined it after all.  |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-10-17 : 08:46:59
|
| Cool, Arnold!!PS OMG, how lucky I am sticking to MS Access at my work... |
|
|
|
|
|
|
|
|
What is the fastest way to load data into SQL Server?