| Author |
Topic |
|
RoyalSher
Yak Posting Veteran
95 Posts |
 Posted - 2003-05-11 : 08:16:26
|
| Hi,Can ne body tell me the difference between Fast / Slow Bcp ? Thanks in Advance.RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-11 : 09:35:56
|
| Well, fast bcp is faster than slow bcp, that's about it I guess. |
 |
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-05-12 : 00:13:37
|
| Rob,Thanks for the reply. On further r&d i had done on this question of mine, i happen to find few articles in regard to fast bcp & slow bcp..fast bcp is insert into <tabname> values (col1,...) (select col1,.. from <tabname>) and slow bcp is bulk copying of data from a table to a text file.is there ne thing related to what i have found and the question i have asked ? Any advices plz lemme noe.RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-12 : 06:40:10
|
| Well, for one thing, bcp is a Bulk Copy Program, so it ALWAYS does bulk copies and ALWAYS does them from text files of some kind. INSERT INTO...SELECT is NOT a bulk copy operation, nor is it faster than bcp, because it is a fully logged operation. bcp is either unlogged or minimally logged. There are many circumstances where using bcp to export data to a text file, and then to import it into another table, is much faster than using INSERT INTO....SELECT.The only feature I've found that might suggest a "fast" bcp mode is to write a custom bulk loader to do parallel data loading. This is not a feature of the bcp utility though. |
|
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-05-13 : 10:27:37
|
Hi Rob,Thanks for replying again.After lot of excavations, i did find out fast / slow bcp. bcp ing a table without clustered / non clustered indexes is called as fast bcp and with these indexes its a slow bcp. These were inferences from one of the article excerpts.I might be wrong in understanding the concept, can there be any ellaboration abt the topic.. probably any suggestion for refering topics.Thanks once again.  RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-13 : 21:18:37
|
| Well, I've never heard of it being referred to as "fast" bcp vs. "slow" bcp, but yes, dropping indexes before a bcp operation can speed it up significantly. However, unless you are routinely importing hundreds of thousands or millions of rows with bcp, it's probably not worth the administrative hassle of dropping and recreating indexes. |
|
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-05-14 : 02:29:26
|
Rob,Well slow bcp is not defined and it shd be definitely opp. of fast bcp as per the article.Let me put a link for you.. i believe that would help in understanding my point of emphasising..abt fast and slow bcp.quote:
(fast bcp requires that you drop all indexes).
. Any remarks plz let me know!Link: http://www.winnetmag.com/Articles/Index.cfm?ArticleID=494HTH.RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
|
|
|
pmr
Starting Member
37 Posts |
Posted - 2003-05-14 : 03:25:38
|
I read the page you referenced.What they say is:"Because you want to use fast bcp, you drop all five indexes (fast bcp requires that you drop all indexes)."Their sentence is ambiguous. What they meant to say was:"Because you want bcp to run fast, you drop all five indexes."Rob is right. If you are returning only a few records, on the other hand, bcp will end up being slower because Microsoft SQL has to initialize and run the stand-alone bcp utility first before bcp itself can begin processing data. Since bcp is fully stand-alone, it can be run by itself from the command line.The following is a portion of my code where I used bcp. You can see how it sets up the command line syntax and then uses "xp_CmdShell" to initialize and run the command:DECLARE @cmd varChar(255), @dir varChar(100), @file varChar(8), @fileNum intSELECT @dir = 'c:\Program Files\CardRead\Log\', @fileNum = 0SET @fileNum = @fileNum + 1SET @file = 'Chapel0' + Cast(@fileNum AS Char(1))SET @cmd = 'bcp ##file_exist IN "' + @dir + @file + '.txt" -F 1 -L 1 -c'EXECUTE master.dbo.xp_CmdShell @cmd Peter |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-14 : 18:52:51
|
Peter is right, their wording isn't very clear and his interpretation is more accurate than theirs. Also, this article was written in 1997, and I'm pretty sure that the difference would be far less noticeable with SQL 7.0 and 2000, which were not released at that time (6.5 was the latest version) 6.5 and earlier versions had huge differences in structure and performance, and couldn't rely on more advanced techniques that are available under the covers in the later versions.They also didn't mention anything about primary keys, which are constraints, and are also usually clustered, so if you don't drop the primary key as well then you don't save much. Of course, you can't drop a primary key if it's referenced as a foreign key by another table...unless you drop the foreign key constraint...you see where I'm going with this I'm sure.  If you want to know for certain, try both methods (dropping indexes first then creating them after, or just leaving them in place) and measure the performance. Unless you see better than 15% improvement in speed, I'd suggest leaving the indexes and constraints in place. It's no fun to drop them, have something fail, and then they don't get recreated afterwards. The simpler procedures are usually the better ones as well. |
|
|
|
tiggyboo
Starting Member
5 Posts |
Posted - 2011-03-18 : 11:10:01
|
| Well, if it's anything like sybase, there is a gigantic difference. Fast BCP is not logged, and slow BCP is. Therefore, we slow BCP, your options are potentially drastically limited with "in" operations as the possibility of filling the log file is constantly looming. So, depending on your configuration, dropping indexes first to achieve fast bcp (assuming also that you don't care about logging) might actually be less administrative overhead than the alternative. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-18 : 11:13:32
|
| Absolutely correct. I think you've set a record for longest time between replies on a SQLTeam thread! |
|
|
|
|
Fast / Slow Bcp