- 浏览: 2163130 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (1878)
- [网站分类]ASP.NET (141)
- [网站分类]C# (80)
- [随笔分类]NET知识库 (80)
- [随笔分类]摘抄文字[非技术] (3)
- [随笔分类]养生保健 (4)
- [网站分类]读书区 (16)
- [随笔分类]赚钱 (7)
- [网站分类].NET新手区 (233)
- [随笔分类]网站 (75)
- [网站分类]企业信息化其他 (4)
- [网站分类]首页候选区 (34)
- [网站分类]转载区 (12)
- [网站分类]SQL Server (16)
- [网站分类]程序人生 (7)
- [网站分类]WinForm (2)
- [随笔分类]错误集 (12)
- [网站分类]JavaScript (3)
- [随笔分类]小说九鼎记 (69)
- [随笔分类]技术文章 (15)
- [网站分类]求职面试 (3)
- [网站分类]其他技术区 (6)
- [网站分类]非技术区 (10)
- [发布至博客园首页] (5)
- [网站分类]jQuery (6)
- [网站分类].NET精华区 (6)
- [网站分类]Html/Css (10)
- [随笔分类]加速及SEO (10)
- [网站分类]Google开发 (4)
- [随笔分类]旅游备注 (2)
- [网站分类]架构设计 (3)
- [网站分类]Linux (23)
- [随笔分类]重要注册 (3)
- [随笔分类]Linux+PHP (10)
- [网站分类]PHP (11)
- [网站分类]VS2010 (2)
- [网站分类]CLR (1)
- [网站分类]C++ (1)
- [网站分类]ASP.NET MVC (2)
- [网站分类]项目与团队管理 (1)
- [随笔分类]个人总结 (1)
- [随笔分类]问题集 (3)
- [网站分类]代码与软件发布 (1)
- [网站分类]Android开发 (1)
- [网站分类]MySQL (1)
- [网站分类]开源研究 (6)
- ddd (0)
- 好久没写blog了 (0)
- sqlserver (2)
最新评论
-
JamesLiuX:
博主,能组个队么,我是Freelancer新手。
Freelancer.com(原GAF – GetAFreelancer)帐户里的钱如何取出? -
yw10260609:
我认为在混淆前,最好把相关代码备份一下比较好,不然项目完成后, ...
DotFuscator 小记 -
日月葬花魂:
大哥 能 加我个QQ 交流一下嘛 ?51264722 我Q ...
web应用程序和Web网站区别 -
iaimg:
我想问下嵌入delphi写的程序总是出现窗体后面感觉有个主窗体 ...
C#自定义控件:WinForm将其它应用程序窗体嵌入自己内部 -
iaimg:
代码地址下不了啊!
C#自定义控件:WinForm将其它应用程序窗体嵌入自己内部
前一段时间,给一位朋友公司做咨询,看到他们的很多的存储过程都存在动态sql语句执行,sp_executesql,即使在没有动态表名,动态字段名的情况下仍然使用sp_executesql,这个做法是不太明智的,会存在一些性能方面的问题。
先说说什么场景使用这个系统存储过程吧,sp_executesql,是sql server动态执行一段可以带有参数(内参,外参)的语句文本的系统存储过程,传入sp_executesql 的参数会以参数的形式传递,不会是以拼凑sql的形式传递,所以能够在不得不拼接sql语句的情景下使用以防止sql注入。不得不拼接sql的情景包括 传递in内参数,动态决定表列,列名,还有就是like,为防止sql注入,也不得不拼接sql。按理来说这是一个非常好的存储过程,但是,由于他本身的限制,会对查询性能有很大的影响,下面我举个例子。
使用northwind数据库,
执行:
select * from orders where customerid = 'SAVEA';
执行:
select * from orders where customerid = 'CENTC';
这两个语句的唯一不同就是客户号不一样,一个在订单表内有31个重复值,一个没有重复值。
然后咱们再来对比当这个语句放在了一个动态执行的sql语句内部的情况如何。
创建如下存储过程:
Code
USE [Northwind]
GO
/****** 对象: StoredProcedure [dbo].[testexecutesql] 脚本日期: 10/15/2009 20:09:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[testexecutesql](@customerid nchar(5))
as
begin
exec sp_executesql N'select * from orders where customerid = @cid ',
N'@cid as nchar(5)',@cid = @customerid ;
end
然后执行这个存储过程:
exec testexecutesql 'SAVEA';
其执行计划如下图,是个聚集索引扫描:
(31 行受影响)
表 'Orders'。扫描计数 1,逻辑读取 22 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
使用聚集索引扫描是个很明智的选择,咱们可以来看看customerid上的非聚集索引的统计信息,orders表共830行,其中客户'SAVEA'就有31个订单,所以优化器选择使用聚集索引扫描而不是嵌套循环的book mark look up。
然后咱们再来执行一个:
exec testexecutesql 'CENTC';
区别仅仅是传入的customerid参数不一样,再看看执行计划,仍然是一样,io也是一样,就是返回的行数只有一行,按理来说,只返回一行,优化器应该会选择使用非聚集索引,嵌套查找数据,但是优化器却没有好好利用customerid上的统计信息,仍然使用了聚集索引扫描,为什们?难道是索引上的统计信息不及时吗?不,在手动使用fullscan后的统计信息仍然是一样的查询计划,为什么呢?
因为sp_executesql本身就是一个存储过程,他执行动态语句的参数是不会被利用上的,所以当第一次编译的时候产生的计划,存储过程testexecutesql 是无法嗅探到的,即无法去引用customerid上的统计信息来做查询计划参考的,所以第一次编译的查询计划是聚集索引扫描就是扫描,即使第二次执行的时候应该是查找。
如何才能改变这一现状呢?
可以使用提示符,recompile强制让存储过程在执行的时候重新编译,来获得最好的执行计划,不过这也是有代价的,就是每次都需要编译,不过相比那些被浪费掉的IO,对一些大表的性能低下的查询计划还是很值得的。于是,我们把存储过程改写如下:
USE [Northwind]
GO
/****** 对象: StoredProcedure [dbo].[testexecutesql] 脚本日期: 10/15/2009 20:09:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[testexecutesql](@customerid nchar(5))
as
begin
exec sp_executesql N'select * from orders where customerid = @cid option(recompile) ',
N'@cid as nchar(5)',@cid = @customerid ;
end
这样再次执行exec testexecutesql 'CENTC'; exec testexecutesql 'SAVEA';
都能获得一个最优的查询计划。
sql server能够支持语句级的重编译,自动嗅探重编译环境,阀值,使得绝大部分情况下能够很好的利用编译后的查询计划,提高数据库整体性能。我在08年初写过的一个ppt,是关数据库于重编译的,大家可以下载看看,http://img.cyzone.cn/temp/SQL SERVER 高级技巧系列之二:重编译详解.ppt
如果有朋友关注数据库性能方面的东西,可以加入我创建的一个小组,http://home.cnblogs.com/group/sql/ 欢迎提出自己遇到的性能问题。
发表评论
-
where T:new() 是什么意思
2014-04-18 09:26 1450where T:new() 是什么意思 经常看到方法后面 ... -
为何在wpf中textbox的值有时不会实时的变化
2013-11-03 19:31 1397TextBox绑定view model 中的一个属性,如果T ... -
好久没写blog了
2012-05-21 18:43 2好久没写blog了 -
使用MySQL命令行修改密码
2011-07-27 20:37 1050使用MySQL命令行修改密码 下面为您额极少的My ... -
更改lnmp安装后的MySQL密码方法
2011-07-27 20:36 1421更改lnmp安装后的MySQL密码方法 2011-07- ... -
mysql-bin.000001文件的来源及处理方法[转]
2011-07-27 20:31 1313mysql-bin.000001文件的来 ... -
lnmp vps下mysql备份
2011-07-27 20:31 1033mysql-bin.000001文件的来 ... -
N点虚拟主机管理系统安装图解
2011-07-25 00:13 1831<script type="text/j ... -
CentOS 6.0正式版终于发布
2011-07-24 10:29 1595CentOS 6.0新在什么地方 ... -
关系数据库还是NoSQL数据库
2011-07-22 16:35 940关系数据库还是NoSQL数据库 作者 孙立 发布于 2 ... -
关系数据库还是NoSQL数据库
2011-07-22 16:31 1074关系数据库还是NoSQL数据库 作者 孙立 发布于 2 ... -
NoSQL开篇——为什么要使用NoSQL
2011-07-22 16:26 1039NoSQL开篇——为什么要使用NoSQL 作者 孙立 发布 ... -
HubbleDotNet开源全文搜索数据库项目--技术详解
2011-07-21 17:49 1168HubbleDotNet开源全文搜索数据库项目--技术 ... -
细说 Form (表单)
2011-07-21 12:27 1246细说 Form (表单) Form(表单)对于每 ... -
ASP.NET(C#)常用数据加密和解密方法汇总
2011-07-21 12:26 1357应同事所邀把ASP.NET(C#)常用数据加密和解密方法 ... -
ds
2011-07-21 09:44 1002blog%5Borigin%5D=0&blog%5Bc ... -
ds
2011-07-21 09:42 961请输入文章内容dsafas -
test
2011-07-21 09:31 901testestestestestestestestestest ... -
判断两个数组中是否存在相同的数字
2010-06-17 09:03 2162判断两个数组中是否存在相同的数字 给定两个排好序的数 ... -
用C#读取XML文档
2010-04-06 09:15 1562本文将以一个非常简单 ...
相关推荐
以下是一个简单的使用`sp_executesql`的例子,假设我们有一个名为`Employees`的表,我们想要根据用户输入的`EmployeeID`查询员工信息: ```sql DECLARE @IDParameter INT = 1; DECLARE @SQL NVARCHAR(MAX) = N'...
这通常涉及将字符串拼接成完整的SQL命令,然后使用`EXEC`或`sp_executesql`存储过程来执行这个命令。下面是一个简单的例子: ```sql DECLARE @tableName NVARCHAR(100) = 'MyTable'; DECLARE @sqlCommand NVARCHAR...
1. **Unicode字符串**:使用`sp_executesql`时,所有的字符串参数必须是Unicode类型(`NCHAR`, `NVARCHAR`, `NTEXT`)。 2. **参数绑定**:当SQL语句包含参数时,需要在`@params`中明确指定这些参数的数据类型和名称。...
1. **使用Sp_Executesql系统存储过程**:这是一种推荐的方法,因为它提供了参数化查询的能力,有助于防止SQL注入攻击,并且在性能上优于EXECUTE()。 ```sql DECLARE @sql NVARCHAR(MAX); SET @sql = N'SELECT *...
EXEC sp_executesql @Sql_Sql, N'@tssj INT OUTPUT', @tssj OUTPUT SELECT @tssj ``` 1. **声明变量**:`DECLARE`关键字用于声明变量,这里声明了两个变量,`@tssj`用于存储结果,`@Sql_Sql`用于存储动态生成的SQL...
3. **EXEC 或 sp_executesql**:执行动态SQL需要使用`EXEC`或特定数据库提供的函数,如SQL Server中的`sp_executesql`。例如: ```sql DECLARE @SQLCommand NVARCHAR(MAX) = 'SELECT * FROM TableName WHERE ...
如果必须使用动态SQL,可以考虑使用`sp_executesql`的参数化功能,以利用查询优化器生成的执行计划。 总之,SQL Server的动态SQL提供了灵活性,但也需要谨慎处理以确保安全性和性能。在构建动态SQL时,应遵循最佳...
例如,使用sp_executesql存储过程可以防止SQL注入。 5. **性能影响**: 动态SQL的执行效率通常低于静态SQL,因为它们需要在运行时解析和编译。频繁使用可能导致大量无用的计划缓存,影响性能。因此,尽量避免不必...
CREATE PROCEDURE用于创建存储过程,EXECUTE或sp_executesql调用执行。 10. 触发器:触发器是数据库响应特定事件时自动执行的SQL语句集合,常用于实现业务规则或数据完整性。 这些基础语法构成了SQL Server 2000中...
15. 如果在查询中遇到动态SQL和复杂的问题,那么可能需要考虑使用sp_executesql存储过程,并且要谨慎传递大型数据类型,如TEXT、XML或IMAGE。总之,要避免使用SELECT COUNT(*)来验证记录的存在,而应该使用IF EXISTS...
EXEC sp_executesql @sql; END; ``` 这个存储过程会查询所有用户数据库中的存储过程,返回每个存储过程的完整路径和类型描述。 "全文检索"和"全库查找"标签提示我们,除了查找存储过程,可能还需要实现对存储过程...
例如,当字段名或表名作为变量时,不能直接在静态SQL中使用,需要通过`EXEC`或`sp_executesql`存储过程执行动态构建的SQL字符串。需要注意的是,字符串前加上`N`表示使用Unicode格式,如`N'select * from tableName'...
3. **使用触发器需谨慎**:虽然触发器能实现复杂的业务规则,但过度使用可能引入难以调试的问题,应谨慎设计和使用。 在SQL Server 2000中,还有其他关键概念,如: - **索引(Indexes)**:用于加速数据检索,包括...
通过构建字符串并使用`sp_executesql`执行,我们可以灵活地处理不确定的表和字段。然而,使用动态SQL时应谨慎,因为它可能导致性能下降并且可能引入安全风险。始终确保遵循最佳实践,对动态SQL进行适当的安全审查和...
3. **执行SQL**:执行动态SQL语句通常通过`sp_executesql`系统存储过程完成。在这个过程中,你需要提供SQL语句和参数。例如: ```sql DECLARE @InputId INT; SET @InputId=1; EXEC sp_executesql @SQL, @...
但是需要注意的是,动态SQL可能引入SQL注入的风险,因此在实际应用中应当谨慎使用。 #### 实现步骤 1. **定义旧所有者和新所有者**: ```sql DECLARE @oldOwner sysname, @newOwner sysname; SELECT @oldOwner ...
public static void ExecuteSql(string SQL_COMMAND, string constring) { using (SqlConnection cn = new SqlConnection(constring)) { cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; ...
- `WHILE`循环和`Sp_ExecuteSql`存储过程结合使用,展示了如何动态生成和执行SQL语句来处理临时表数据。 5. **注意事项**: - 临时表的使用可以提高性能,特别是在需要反复处理大量数据时,因为它们在内存中存储...
EXEC sp_executesql N'ALTER TABLE your_database.dbo.your_table ALTER COLUMN your_column datatype WITH COMMENT = '''; ``` 请注意,上述示例SQL语句需根据实际数据库环境进行调整。在运行任何清除注释的语句...