Using Visual Basic to process a large text file vs using SQL Server

Earlier this year I rose to the challenge on the Microsoft “Transact-SQL” forum where someone was trying to “parse 1 million records in one minute” using SQL server.

The problem was basically to take a large text file with “60 million rows resulting in 200-300 million rows”. Their best attempts using SQL server were taking 9 hours. The data appeared to be generated from a television viewer tracking system and these records needed to be processed every night before being analysed in SQL server.

Every solution that was being offered using SQL server had apparently been tried.

My alternate demonstration using Visual basic (within MS Excel) took 4 minutes per 1 million records and this was using a basic laptop. This was a time saving from 9 hours down to 1 hour and even better if a server was used.

It shows that it is best to use the right tool for the job - even in the world of I.T.

Google ‘social.msdn "parsing data" "transact-sql" ssdl gerry phillips ‘ to find the thread with the “answer”.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d868cb65-90bd-4857-9fc5-521e35e73ef9

Update on the Book

Despite Gerry being on a world trip for work we have been making good progress on the data warehousing book. Here are a few summary points about the book

The book will cover our Data warehousing strategy & philosophy and the Holistic Data warehousing method and will include a free download of the working template.
It will have full documentation of how to use the Holistic data warehouse template
The book is currently just over 300 pages with over 225 colour screenshots and diagrams.
It will be printed in black and white for hard copies and in colour for ebooks
We are optimising the graphics for presentation in the ebook format for display on the Apple iPad - using the (free) Kindle app.

We are expecting the book to be published within 3 months and available on Amazon and Kindle only.

Stay tuned