Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

As the question reads, I am building a database with 3 tables. Now these tables are going to be used to store names.

  • Table 1 will store First Names
  • Table 2 will store Last Names
  • Table 3 will be a one to one table linking the First names to the Last names

Now all this data will be coming from a text file in the format of:

Firstname MI Lastname

I estimate that their will be over 100 million records. I also don't want to do a insert for every name it will be an update record on duplicate Key. So what is the most optimal way to enter this into my database. By the way it is a Innodb so the whole table gets locked so I can't do multiple insert updates at a time

Now this whole process will be done via C# using mysql connection and I have:

sqlRequest += "START TRANSACTION ;" +
    "UPDATE firstName SET LastUpdated = CURRENT_TIMESTAMP WHERE first= '" + countSplit[0] +"' ;" +
    "INSERT INTO firstName(first, LastUpdated) SELECT '" + countSplit[0] + "' AS first, CURRENT_TIMESTAMP AS LastUpdated FROM dual WHERE NOT EXISTS ( SELECT * FROM firstName d WHERE d.first= '" + countSplit[0] + "') ;" +
    "COMMIT ;";
sqlRequest += "START TRANSACTION ;" +
    "UPDATE lastName SET LastUpdated = CURRENT_TIMESTAMP WHERE last = '" + countSplit[2] + "' ;" +
    "INSERT INTO lastName (last, LastUpdated) SELECT '" + countSplit[2] + "' AS last, CURRENT_TIMESTAMP AS LastUpdated FROM dual WHERE NOT EXISTS ( SELECT * FROM lastName d WHERE d.last = '" + countSplit[2] + "') ;" +
    "COMMIT ;";
sqlRequest += "START TRANSACTION ;" + 
    "INSERT INTO first_to_last " +
    "(firstid,lastid,LastUpdated) VALUES "+
    "((SELECT firstid FROM firstName WHERE first='" + countSplit[0] + "')," +
    "(SELECT lastid FROM lastName WHERE last='" + countSplit[2] + "' )," +
    "COMMIT ;";

So do you think that this is the best way? Or do you think there is something better?

share|improve this question

migrated from Dec 3 '12 at 13:44

This question came from our site for professional and enthusiast programmers.

Any reason why you're implementing the database like this where all you need is one table with a Primary Key, First Name, Last Name columns? – Mataniko Dec 3 '12 at 4:24
Building a application that requires this layout – Dustin Taggart Dec 3 '12 at 5:21
Can you be more specific on your application? The database design shouldn't be affected by the application design as you can display the data in any way you like. This sounds like a relational database homework assignment to show the use of foreign keys. – Mataniko Dec 3 '12 at 5:29
Well to be honest I am working on this to be plugged into a already existing system. This is only a small part, my employer suggested I ask here but not to reveal anything relating to the project. I have it working but it takes a long time to build. I am looking for suggestions on speed performance. – Dustin Taggart Dec 3 '12 at 6:11
Performance could be related to how the tables are defined, and you can expect a hit on every query since the system will require to select on three tables, possibly use Joins for sane results, etc. With this implementation. As for the queries themselves, you are basically scanning the entire table with every insert with your nested select. It's also an expensive select because you're not using an index. (At least that's what I figure from the partial information. – Mataniko Dec 3 '12 at 6:35

From the looks of this, you should be using a Stored Procedure, not entirely familiar with how that works in MySQL but I imagine it is similar to SQL Server Stored Procedures.

Using a Stored Procedure would be the easiest way to do this.

You create the stored procedure with the parameters that you need, then in the C# you call the stored procedure giving it the parameters in the specified manner. I haven't tried to do with with MySQL but I imagine it is much the same as with SQL Server ( I know I said that once already )

You are trying to do the Database's work with C#, keep them separate.

Cleanse the input, create a command for the Stored Procedure, give it the parameters, execute the command.

This way you can loop through a list of names in C# sending the names to the stored procedure, or create the stored procedure to accept a list of names.

Creating the Stored Procedure will also allow you to use it other places in the C# application, and if you need to change the Query, it is easily maintainable, you change the query and don't have to recompile the application as long as the input stays the same.

share|improve this answer

You don't show it here, but I guarantee you're splitting on whitespace. Which is wrong. Condsider the name Jane MaryAnne Vander Werf. You're going to cut off the second half of this person's last name. This is notoriously hard to do in reality. Names don't just fall into our sense of what "good data" looks like. Please see Falsehoods Programmers Believe About Names.

What you really need to do is make sure your input makes sense. If it doesn't, you're eventually going to have problems. Your input needs to be in the form of fixed width columns so that you can do this manipulation based on the position of the character.

share|improve this answer
Relevant: "Falsehoods Programmers Believe About Names". – BCdotWEB Jun 4 '15 at 14:04
Extremely relevant @BCdotWEB! Thanks! – RubberDuck Jun 4 '15 at 14:06

A few thoughts:

  1. Pre-process the file to remove duplicates. Assuming the data is formatted appropriately you could sort the file and then walk through it to pull out the first first/last pairing as it appears the middle initial is not considered.

  2. During pre-processing regularize the output into a file that can be loaded into a working table using LOAD DATA INFILE. Let the database get the data inside as fast as it can.

  3. With the data loaded into a working table create a procedure to split the data into the horrible design apparently necessary without the need to send records in using your programming language of choice.

  4. Truncate or drop the working table when you don't need it anymore.

And, a short anecdote on finding good solutions. Assume someone else has demonstrated a system that is ten times faster than yours. Then, start asking yourself how in the world they were able to do so. This can often push you past your initial impressions -- especially if you have a competitive nature.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.