群里的问题.
关于执行计划重用,俺有个问题,比如我从sys.syscacheobjects中查到了大量的
update xx set id = yy where sid = xx 语句,在不将拼写SQL改成存储过程的前提下,c#代码中该么样给参数传值?
思路sql语句通常是不能重用执行计划,所以一直都推荐使用存储过程来执行常用语句,而且更加安全。
测试:
首先C#
for (int i = 0; i < 1000; i++) { string j = "changeC#execPlan "; ExcuteSQLNoReturn("update Demo_Top set PGuid = '" + j + "' where PID ="+i); //执行sql语句 }
上面没有参数化,每条都会产生一个执行计划
select * from sys.syscacheobjects
如果开发人员不能在sql server中建立存储过程,那么尝试执行sp_executesql的方式来复用执行计划如下
for (int i = 0; i < 1000; i++) { string j = "N'changeC#execPlan' "; // ExcuteSQLNoReturn("update Demo_Top set PGuid = '" + j + "' where PID ="+i); string sqltext = "N'update [Demo_Top] set [PGuid] = @j where [PID] = @i '"; string sqlParm = "N'@i int, @j nvarchar(30)'"; ExcuteSQLNoReturn(" execute sp_executesql " + sqltext + ", " + sqlParm + ", " + " @i = " + i.ToString() + "," + " @j =" + j); }
结果: