SQL Server 2000 Bulk Import Options (HA! What a Joke!)
Well, it sad to say that I am so disappointed in the SQL Server 2000 import options! I've spent the last couple of days on two different projects trying to improve the performance when importing data. The one that should be the easiest to achieve is taking data straight from the National Do Not Call registry at
http://www.donotcall.gov , whose site was obviously done in .Net as well. You have your choice of XML or CSV. CSV is still the fastest, but in trying to import this data, I am still having to loop through every row. The size of data is tiny! Nothing but a phone number! Granted about 750,000 of them! The thing that complicates everything is the fact that I can't write the data to a file (I'm on a webfarm), so all processing is done in memory. So, even the command line tool BCP isn't an option! XML should be easy to import with the various options OPENXML, SQLXMLBulkLoad, etc, but I had problems with every single BULK method I tried! So, either way, I'm looping through all these rows!
My first version of the XML method was doing a ReadXML and it worked fine for 250,000-300,000 rows, (be sure you do a ReadXMLSchema first! Learned that the hard way! ) But I had to redo this method as well, when trying to process 750,000 rows the ReadXML method would immediately max my CPU to 100% and even after an hour or so, showed no signs of completing and this is before it even starts inserting any data! This was completely rewritten using the .Net XMLTextReader class, but yet again, loops through every node of the XML document, but at least it would process the 750,000 rows in avg of 7-10 minutes.
And as a side not I also tried to use
ICSharpCode.SharpZipLib to save the user the extra step of unzipping the file first. But again, every same, was based on the Uploaded zip file being written to the FileSystem first, which doesn't work in a webfarm environment, so I'm still researching that one!
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
Comments
Leave a Comment
About dwalker
David Walker has over 15 years experience in application development with over 50% of that employed as a consultant with companies such as: Texaco, Bank of Oklahoma, Winner Communications (ESPN.com) and IBM Global Services. At the age of 14, he began his application development ambitions with a Commodore 64, BASIC, and a 300 baud modem. Even at that early age, he primarily focused on two specific application types: multi-user communities and database applications.
His hunger to learn as much as possible about development lead him through courses such as DBase III, DBase IV, Pascal, C, C++, Java, and several in UNIX. He started his development career first doing heavy processing with Access and VBA, then moved on to VB 3, Oracle, and Delphi. Visual Basic was one environment that remained constant for many years, including his very first .NET projects performed in Visual Basic.NET.
After working several years on very high end internal Corporate applications, the consultant company he was working for, sought out his ideas for actual software products that could be packaged and sold. He had already developed several prototypes of a dynamic portal application, before portals even became popular, so this became the logic decision and he became the Director of Product Development. Under his direction, a team of developers and graphic artists, took a skinning approach before that become popular, and completed the core portal application, and continued on to developer 15+ add-on modules, including things such as: Help Desk Ticket Systems, Change Control, Records Management, Human Resources, and many more applications. Eventually, it spun off into it's own separate company as KnowledgeGEAR, a complete intranet in the box solution.
Having worked as a consultant, he has had a experience with a very wide range of applications and architectures, at one time, even converting several Fox Pro and GW-Basic applications to VB 6 and ASP. His early training of Unix and the C language and years of experience with JavaScript, lead him very quickly to C#, where he has remained focused ever since.
He is the current President of the
Tulsa Developers .NET user group.. He has been an MCP since 2003 and MCAD and MCSD since 2005. He is currently pursuing his MCDBA and then on to MCSE.