Currently working on a system which allows users to upload data files, which we then "clean" (i.e. process in various ways). Speed of processing and return to the user is an important consideration, so we want to do this as fast as possible.
The original plan was that once the user had uploaded the file, we would read it into memory and perform all the processing there, before writing the data back to a new file for the user to download. We assumed that this would be faster than the disc I/O required to write the data into a data table.
To further complicate issues, we're using MySQL for this, which we've not used before. We also can't be certain of the data format we're getting - it'll be a delimited file, but it could be a variety of formats (i.e. different delimiters, possible encapuslation etc). We've developed a number of tests to work these variables out prior to processing.
Anyway, one of the cleaning steps is to remove obscenities from the data. To do this, we need to compare each data row plus obvious combinations like forename and surname, against a list of ~700 possible obscenities. I wrote some C# card to do this in memory, and to my surprise it took 48 seconds to iterate through a 30,000 record file.
So I imported the data into MySql and ran a query. The query itself took about a second, but loading the data using MySql's Table Data Import Wizard took about ten minutes which seems absurdly long. The same process in SQL server took 30 seconds.
Wanting to run a fairer end to end test, I started investigating how to import the file into MySql via C#. There seem to be a lot of options, from attempting a bulk insert, to iterating and running line by line - too many to realistically test them all.
Is there any other way I can approach this? In terms of sheer speed, is it generally a better idea to stick to memory or take the disc writing hit?