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?

  • 1
    Since text processing seems an important part here I would look into something like Elasticsearch. Not only is it better optimized fir full text search it could also help finding words with spelling errors (maybe intentional if somebody wants to sneak in some specific text). Otherwise it's really difficult to tell, it's hard to believe that processing 30000 rows should take ten minutes for MySql under what conditions ever (well, unless you do really complicated joins without proper indexing) – thorsten müller Apr 19 '16 at 10:09
  • @thorstenmüller Thanks. I had been pondering whether something like ES or a NoSql variant might be a better option. The 10 minutes wasn't to process the file - just to write it to disc via the import wizard. The actual query was ~1 second. – Matt Thrower Apr 19 '16 at 10:15
  • 1
    Importer speed may depend on a lot of factors like table type and types of indexes on the table. Why would you need 30000 lines/rows? Is this one line per line in a text file? You could maybe concatenate the text into a single text field and insert a single row. But I think ES would be really the better option for the task (still would use a single row for the file if possible). Also would depend if you need the database content later too or if it's only for this temporary step. – thorsten müller Apr 19 '16 at 10:24
  • @MattThrower: from your question, it is unclear if your "one query" does the same as your comparison against that "list of ~700 possible obscenities", or where the equivalent of the latter happens in your MySql solution. Please clarify. – Doc Brown Apr 19 '16 at 10:42
  • 1
    We still do not have enough information to answer your question - why don't you edit your question and give a short outline about the details, an example etc? We cannot tell you what went wrong in your C# code when we do not know what it does. Your DB is probably indexed; for getting similar or better performance in C#, you will typically have to utilize a Hashset or Dictionary. But without knowing more of your requirements or your code, this is just wild guessing. – Doc Brown Apr 19 '16 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.