在存储过程里面使用动态语句,其中
@SortID,@ArticleID是变量,
语句A:
SELECT ArticleID,Title,SortID FROM Article WHERE SortID=@SortID AND ArticleID<@ArticleID ORDER BY ArticleID DESC
与语句B:
EXEC "SELECT ArticleID,Title,SortID FROM Article WHERE SortID="+@SortID+" AND ArticleID<"+@ArticleID+" ORDER BY ArticleID DESC"那种执行效率更高?A语句会不会导致索引用不上?
请大虾们指点!!
@SortID,@ArticleID是变量,
语句A:
SELECT ArticleID,Title,SortID FROM Article WHERE SortID=@SortID AND ArticleID<@ArticleID ORDER BY ArticleID DESC
与语句B:
EXEC "SELECT ArticleID,Title,SortID FROM Article WHERE SortID="+@SortID+" AND ArticleID<"+@ArticleID+" ORDER BY ArticleID DESC"那种执行效率更高?A语句会不会导致索引用不上?
请大虾们指点!!
既然存在语句A,那么说明这条语句不存在语法错误,服务器不用进行编译
而语句B,只是字符串,语法是否有错,在服务器执行的时候要进行语法方面的检查,从而降低了效率
select 1
exec ('select 1')
exec sp_executesql N'select 1'1 执行计划完全是一模一样的;
2 跟踪结果
TEXT Event Class
select 1 SQL:StmtCompleted
select 1 SQL:StmtCompleted
exec ('select 1') SQL:StmtCompleted
select 1 SQL:StmtCompleted
exec sp_executesql N'select 1' SQL:StmtCompleted虽然从表面上看,或者从单条语句看,直接执行sql语句比exec或者sp_executesql的效率高,但是我觉得这是片面的;如果只是一条非常简单的sql语句,例如select name from student,效率都差不多的,3这不同之处基本可以忽略不计的;但是如果select name from student where id=?,在系统中的影响就有差别了,应该说sp_executesql的稍微好一些,不需要重新编译的,这个可以在系统运行时,使用时间探查器观察在存储过程中是否有未被重复使用的sql语句个人观点,期待高手精彩回答!
TEXT Event Class
select 1 SQL:StmtCompleted
select 1 SQL:StmtCompleted
exec ('select 1') SQL:StmtCompleted
select 1 SQL:StmtCompleted
exec sp_executesql N'select 1' SQL:StmtCompleted