MySql statement prepare "not sticking"

Question

I'm attempting to use a prepared statement, and while the MySqlCommand executes just fine, the execution time is abysmal. I had it write the result of cmd.IsPrepared to the console, and sure enough, it is false. Here is where I setup the MySqlCommand:

MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = @"INSERT INTO dict (pre, dist, dict.char, score) VALUES(@pre, @dist, @char, @score) ON DUPLICATE KEY UPDATE score = score + @score";
cmd.Parameters.Add("@pre", MySqlDbType.VarChar, 32);
cmd.Parameters.Add("@dist", MySqlDbType.Int32);
cmd.Parameters.Add("@char", MySqlDbType.VarChar, 1);
cmd.Parameters.Add("@score", MySqlDbType.Double);
cmd.Prepare();

I've also tried executing the Prepare() before adding parameters with the same result.

I then have a loop of code that does some computation and sets variables like so:

cmd.Parameters[3].Value = score;

...and does nothing else to the command until it comes time to run:

Console.WriteLine(cmd.IsPrepared);
cmd.ExecuteNonQuery();

The result to the console is always false. This is all done within a basic transaction, but that doesn't seem like it should mess things up. I do open the transaction before I setup the MySqlCommand, though.

Any ideas as to where this is going wrong?

edit: I replicated the code in java, and the prepared statements work fine in it. So it's not a problem with my database server itself, it is specifically a problem in .net. Surely the .net/connector isn't broken for everyone, so what could possibly be the deal here?

And it definitely isn't prepared and simply not setting that bool value, the running time in .net for some test input is so long I don't have the patience to wait it out, but in java the same input runs in ~3 minutes. Both use basically the same code.

Here's a simple test I did in .net, so you can see the full code of what I'm trying (I removed the UID and password from the connection string, but in the normal code they are there, a connection is established, and the statement enters data into the database):

        using (MySqlConnection con = new MySqlConnection(@"SERVER=localhost;DATABASE=rb;UID=;PASSWORD=;"))
        {
            con.Open();

            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = @"INSERT INTO test (test.test) VALUES(?asdf)";
                cmd.Prepare(); //doesn't work

                cmd.Parameters.AddWithValue("?asdf", 1);

                cmd.ExecuteNonQuery();
            }
        }

I'm using MySql.Data.dll version 6.4.4.0 with a runtime version of v4.0.30319 in c# 2010. I'm also including MySql.Data.MySqlClient for the above example code.

Answer 1

It looks like you're doing a using on the MySqlCommand object, at least in your second example. To get any benefit of prepared statements, you would need to not dispose of the connection and command objects. You also will want to call prepare after setting CommandText and before setting any parameters.

https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html

Also prepared statement parameters in MySQL aren't named, they're specified based on order. The CommandText should just contain question marks where the parameters are, and the parameter objects need to be added in exactly that order.

Answer 2

Preparing an SQL statement that is going to be executed only once would not bring any performance benefit, so I'll assume you are executing it multiple times:

• Ensure you are reusing the same MySqlCommand object while repeatedly calling ExecuteNonQuery. Be careful how you use using - you don't want to dispose the MySqlCommand object too early.
• Only assign new parameter values before each new execution - don't change the statement text or add/remove parameters.
• You'll also possibly need to keep the MySqlConnection alive during all that time. Be careful about using here as well.

BTW, some ADO.NET providers ignore Prepare method altogether and "prepare" the statement only on the first execution (ODP.NET does that, not sure about MySQL). If you did everything correctly, this should have no impact on performance whatsoever...