John W. Colby
jwcolby at colbyconsulting.com
Tue Sep 7 23:50:59 CDT 2004
In fact I gave up on bcp because the data is surrounded by quotes. DTS strips them off but I can't for the life of me figure out how to have BCP do that. I'm almost done importing at this point. The imports are (crossing my fingers) proceeding smoothly, I am currently processing file 18 of 22, thus by tomorrow morning I should have them all in. Then I have to figure out backup. IMMEDIATELY! I do NOT want to lose this stuff and have to do it again. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence (AccessD) Sent: Wednesday, September 08, 2004 12:08 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] Gigabit switch Hi John: Just another thought about processing/importing your records You can ask BCP to commit every n records. This way, you can just keep truncating the log every few minutes, while BCP is still running. This allows one to load a humungous database using a relatively small log file. You may want to commit every 100,000 records or so. (A friend does this all the time when importing large amounts of data with little hard drive space.) The gigabit switch seems like the ticket...there is no substitute for horse-power. HTH Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of John W. Colby Sent: Tuesday, September 07, 2004 7:25 PM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Gigabit switch I got my 8 port gigabit switch today. With that switch the entire dynamic of my SQL Server experience changes. Prior to this the fastest method of importing using DTS was to have the raw data files on a disk on the SQL Server and have the server machine itself load the data into the database. I would average about 40k records per minute in that configuration. My other new machine running DTS inserting records into the server didn't work much if any slower, but it also didn't work any faster. Now, with the gigabit switch, having the second new machine run DTS on files local to that machine dumping the data into the server is close to twice as fast as having the server doing all of the work. Furthermore, and I don't understand this, having the second new machine running DTS and simultaneously performing another task such as unzipping one of the raw text files used to slow the DTS to a crawl. With the gigabit switch the DTS slows down slightly, just barely noticeable) but the other task proceeds at full speed as well. Not to mention that I am also uploading all the data from my wife's laptop to a usb disk on the server in preparation to format / reinstall that machine. It sure sounds like I had a LAN bottleneck which I have opened way up. 8-) John W. Colby www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com