Building database from file

Question

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] + "' )," +
    "CURRENT_TIMESTAMP)"+
    "ON DUPLICATE KEY UPDATE LastUpdated = CURRENT_TIMESTAMP;"+
    "COMMIT ;";

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

Answer 1

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.

Answer 2

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.

Answer 3

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.