Friday, June 18, 2010

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”.

No comments:

Post a Comment