Building database from file C# Mysql

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

• 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?

-

migrated from stackoverflow.comDec 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.

this 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

-