JWColby
jwcolby at colbyconsulting.com
Wed May 9 12:52:33 CDT 2007
Gustav, >It stresses that BULK INSERT is mandatory for this level of data sizes. Oh yea!!! I haven't found any other way that makes this size import doable. >One thing that strikes me, however, is the slow performance of your space stripping; 1000 lines/s is not very much. But I guess you do more than just removing spaces. Nope, just remove spaces and write back out to a pipe delimited file. I have a pair of classes that do this. One loads clsFile - the file spec info (file name stuff, from / to dirs etc) and the other uses one instance per field, and loads one field of the field spec table - clsField. The basic operation is load the filespec class, then a collection of field spec classes. Each field spec class knows what it's field name is, where in the string it's field starts, and how wide it's field is. The file spec then opens a stream object and does a readline into a strLineIn variable. The clsFileSpec iterates its collection of clsField instances, and this strLineIn variable is passed in turn to each field class instance. The field class does a midstr() to pull out precisely the data section that it has to process and stores it in a strData variable / property (pData). The field class then strips off the leading and trailing spaces. Once clsFileSpec has read the strLineIn and passed that in turn to each clsField, it has a collection of clsField instances each holding a stripped section of the original strLine. clsFile then iterates that clsField collection appending each clsField.pData plus a "|" to strLineOut. When it has processed each clsField instance it is done assembling the strLineOut, which it then writes to an output stream. Line in, parse / strip, line out, repeat until done. I do a little logging of the file name / time to do the entire operation on the file etc. 99.99% of the time is in the parse / strip operation out in the clsField instances. Remember that the time to do this varies with the data and the data file. The first file I did had well over SEVEN HUNDRED fields / line. This specific file had 149 fields in it. How many lines per second will be most heavily influenced by the number of fields per line. Not all of them have spaces, but how do I tell? This is a generic solution, so that I can use it on the next file, not custom programmed for one specific file. I think this application will port quite easily to VB.Net though I haven't done so yet. When I do I will run the thing again and give comparison numbers. I do hope / expect that VB.Net will be significantly faster in processing the field - parse / strip. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Wednesday, May 09, 2007 1:18 PM To: accessd at databaseadvisors.com Subject: [AccessD] Infutor Statistics - was RE: [dba-SQLServer] Bulk insert Hi John Thanks for sharing. Quite a story. It stresses that BULK INSERT is mandatory for this level of data sizes. One thing that strikes me, however, is the slow performance of your space stripping; 1000 lines/s is not very much. But I guess you do more than just removing spaces. /gustav >>> jwcolby at colbyconsulting.com 09-05-2007 19:01 >>> Just an FYI. The table that I have been building this whole time contains 97.5 million records, exactly 149 (imported) fields and requires 62.6 Gigabytes of data space inside of SQL Server. It took 2 hours and 28 minutes just to build the auto increment PK field after the table was finished importing records. The index space for the table (with just this single index) is 101 Megabytes. There were 56 raw data files which required 75 gigabytes of disk space to hold. There were 56 CSV files created after stripping out the spaces, which required 40.8 Gigabytes of disk space to hold. Thus by my calculations, 35 gigs of disk space was required to hold JUST THE SPACES in the original fixed width file, with the real data occupying 40.8 GB. It is interesting to note that the raw data in the CSV file was 41gb while the data space required in SQL Server is 62 gb. As the process was built over time, I do not have accurate specs for each and every file, but the process of stripping the spaces off of the fields happened at about 1K records / second. Given 97.5 million records, this equates to 97.5 thousand seconds to do the space stripping, which is 27.77 hours. That of course is done in a VBA application. Again I don't have accurate specs for all of the bulk inserts, however those that I recorded the times for summed to 71.2 million records, which took 4674 seconds (1.3 hours) to import using a BULK INSERT statement, which equates to approximately 15K records / second. Remember that this BULK INSERT is importing precleaned data with pipe delimiters. Also remember that the BULK INSERT itself took 1.3 hours but due to the lack of automation in feeding the Sproc file names, I had to manually edit the SPROC each time I wanted to import a new file so the actual import took much longer, since I wasn't necessarily watching the computer as the last SPROC run finished. So there you go, that is what I have been trying to accomplish this last few weeks. John W. Colby Colby Consulting www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com