开发者

MySql: Will using Prepared statements to call a stored procedure be any faster with .NET/Connector?

I've been reading a bit about Prepared statements with MySql, and the .NET/Connector does support them.

What I'm wondering, is if I use a prepared statement to call the same stored procedure thousands of times, is that any faster or better performance than not using prepared statements to do so (since the stored procedure should really be compiled already)?

Eg:

var mySqlCmd = new MySqlCommand(con, "call sp_someProcedure(@param1, @param2开发者_开发问答);");
mySqlCmd.Prepare();
mySqlCmd.Parameters.AddWithValue("@param1", "");
mySqlCmd.Parameters.AddWithValue("@param2", "");

for (int i = 0; i < 1000; i++)
{
    mySqlCmd.Parameters["@param1"].Value = i.ToString();
    mySqlCmd.Parameters["@param2"].Value = i.ToString();
    mySqlCmd.ExecuteNonQuery();
}


A prepared statement requires a minimum of 2 db calls. The first call (prepare) takes your application level sql statement e.g. select * from users where user_id = ? and creates a query template in the database which is then parsed and validated. Subsequent calls simply involve passing values from your application layer to the db which are then inserted into the template and executed.

A stored procedure already exists in the database. It has been parsed and validated during creation. A stored procedure is a bit like the template mentioned above but it's a permanent feature of the database not a temporary one.

Therefore, to execute a stored procedure you only need to pass it params - you dont need to prepare it.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜