Hello World
Spiga

是否会成为问题——Linq to Sql的执行可能无法复用查询计划

2007-11-21 08:43 by 老赵, 5350 visits

查询计划

Sql Server在执行一条查询语句之前都对对它进行“编译”并生成“查询计划”,查询计划告诉Sql Server的查询引擎应该用什么方式进行工作。Sql Server会根据当前它可以收集到的各种信息(例如内存大小,索引的统计等等)把一条查询语句编译成它认为“最优”的查询计划。很显然,得到这样一个查询计划需要消耗CPU资源,而大部分的查询语句每次经过编译所得到的查询计划往往是相同的,因此除非指定了RECOMPILE选项,Sql Server在执行查询语句时,会对查询计划进行缓存——也就是说,如果是相同的查询语句,Sql Server只会对它进行一次编译操作,然后在每次执行时对查询计划进行复用。查询计划如果无法复用,则会在相当程度上降低数据库性能——因为过多的CPU被消耗在查询语句的编译上。各种提及数据库查询优化的资料上大都会提到这一点,我们往往通过查看性能计数器的某些统计,或者Sql Server系统表中的一些记录,就可以判定您的数据库应用是否出现了这个问题。

对于存储过程来说,复用查询计划是轻而易举的。不过对于那些喜欢在程序代码中拼接Sql字符串的朋友来说,日子就有些不好过了。Sql Server是根据您传入的Sql语句来缓存查询计划的,如果您“强行”拼接了Sql字符串并交给Sql Server执行,那么查询计划被复用的可能性微乎其微。因此,我们绝对应该杜绝拼接字符串的行为,因为这不仅仅造成了传统的Sql注入!而那些习惯相对较好的朋友,则会使用带参数的Sql语句,在交给Sql Server执行时就可能复用查询计划。因为和调用存储过程相比,发送带参数的Sql语句只是将使用了sp_executesql命令而已,每次执行的查询语句还是相同的。

问题何在?

对于复用查询计划的问题,在上文中我说了这么一句话:“……使用带参数的Sql语句,在交给Sql Server执行时就可能复用查询计划……”。我为什么要说“可能”?因为即时使用带参数的Sql语句,在某些情况下我们还是无法对查询计划进行复用。这是怎么一回事儿呢?我们还是直接从Linq to Sql来产生Sql语句,然后观察Sql Server的行为吧。

请看以下的代码(示例所操作的数据表与《在Linq to Sql中管理并发更新时的冲突(2):引发更新冲突》一文相同):

LinqToSqlDemoDataContext dataContext = new LinqToSqlDemoDataContext();
dataContext.Log = Console.Out;

Video video1 = dataContext.Videos.SingleOrDefault(
v => v.Introduction == "Hello");
Video video2 = dataContext.Videos.SingleOrDefault(
v => v.Introduction == "Hello World");

Console.ReadLine();

还是查看输出:

SELECT [t0].[VideoID], [t0].[Introduction], [t0].[SiteID]
FROM [dbo].[Video] AS [t0]
WHERE [t0].[Introduction] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Hello]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21004.1

SELECT [t0].[VideoID], [t0].[Introduction], [t0].[SiteID]
FROM [dbo].[Video] AS [t0]
WHERE [t0].[Introduction] = @p0
-- @p0: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [Hello World]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21004.1

两局Sql语句完全相同,按我们刚才的说法,Sql Server应该缓存了查询计划。但是我们通过查看sys.syscacheobjects的相关数据可以看出,事情并非如同我们想象的那样:

SELECT cacheobjtype, sql FROM sys.syscacheobjects;
DBCC freeproccache;

请注意上图中被选中的两条记录,它表明了Sql Server并没有缓存执行计划。

为什么?这两次执行究竟有什么区别?通过Linq to Sql很容易看出,两次执行所用到的参数不同。更进一步,如果对比Linq to Sql输出的缓存以及sys.syscacheobjects视图中的记录,就会发现:其实仅仅是参数的尺寸不同。

没错,就是这个原因。在使用ADO.NET时,如果SqlParameter的Type是nvarchar,并且没有指定Size属性,则可能就会因为具体参数的尺寸不同而造成查询计划无法复用的结果。这一点,很多人都忽视了。

优化方案

在使用ADO.NET进行开发时,该问题其实很容易解决。我们只要指定SqlParameter的Size属性即可。由于每次指定了一个固定的参数尺寸,Sql Server就能够复用查询计划了。

不过我们现在在使用Linq to Sql,又该怎么做呢?嗯,我们可以为XXXXDataContext重写(override)SubmitChanges方法,在其中获得需要执行的SqlCommand对象(具体方法请参考《在Linq to Sql中管理并发更新时的冲突(1):预备知识》一文),获得其中的SqlParameter参数,并设定它们的Size属性。我们可以使用Custom Attribute来标注应该为哪个属性设置什么样的Size,如果再结合AOP,哈哈……

等等,先别想那么远。即使得到了SqlCommand对象,它所生成的Sql语句是以@p0、@p1作为参数名,您知道该修改哪个SqlParameter对象吗?再者,SubmitChanges方法只是提交我们做出的修改,但是在一般的系统中,查询操作的次数和性能消耗大大超过修改操作,而重写了SubmitChangeds方法又不能影响我们的优化操作……

因此,我想在这里说的是:这个问题我们没法进行优化。

不过我们还是幸运的,因为我根据我的经验,似乎在查询条件中使用长度不等的字符串作为参数的情况并不多见。不是么?

Creative Commons License

本文基于署名 2.5 中国大陆许可协议发布,欢迎转载,演绎或用于商业目的,但是必须保留本文的署名赵劼(包含链接),具体操作方式可参考此处。如您有任何疑问或者授权方面的协商,请给我留言

Add your comment

31 条回复

  1. gakaki[未注册用户]
    *.*.*.*
    链接

    gakaki[未注册用户] 2007-11-21 01:29:00

    so LINQis worse than store procedure TOO? it can not be compile in sqlserver

  2. 老赵
    admin
    链接

    老赵 2007-11-21 01:36:00

    @gakaki
    在这方面的确不如使用sp。

  3. STS[未注册用户]
    *.*.*.*
    链接

    STS[未注册用户] 2007-11-21 03:12:00

    string param="Hello";

    Video video1 = dataContext.Videos.SingleOrDefault(
    v => v.Introduction == param);

    param="HelloWorld";

    Video video2 = dataContext.Videos.SingleOrDefault(
    v => v.Introduction == param);

  4. STS[未注册用户]
    *.*.*.*
    链接

    STS[未注册用户] 2007-11-21 03:14:00

    另外有人提到,参数的长度,不会影响SQL的编译.

  5. 老赵
    admin
    链接

    老赵 2007-11-21 07:48:00

    @STS
    用变量代替参数也没有用的,以传入参数的值为准。
    至于会不会影响编译,总是以Sql Server的行为为准,而我是用Sql Server里的信息来证明的。

  6. 1-2-3
    *.*.*.*
    链接

    1-2-3 2007-11-21 08:14:00

    复用查询计划与不复用查询计划的速度到底差多少呢?很想知道。

  7. ShareDuck[未注册用户]
    *.*.*.*
    链接

    ShareDuck[未注册用户] 2007-11-21 08:19:00

    请问在Linq中如何表达SQL中的in?

  8. 老赵
    admin
    链接

    老赵 2007-11-21 08:40:00

    @1-2-3
    这要视情况而定。在某些时候,复用查询计划反而会降低性能,所以要指定recompile选项来要求Sql Server重新编译。

  9. 老赵
    admin
    链接

    老赵 2007-11-21 08:42:00

    @ShareDuck
    我觉得做不到。因为普通的ado.net无法支持简单地依靠传入参数来支持in操作。

  10. 悟道2007
    *.*.*.*
    链接

    悟道2007 2007-11-21 09:09:00

    呵呵,强文啊,哎!看样子我得再接再历呢!呵呵!

  11. 1-2-3
    *.*.*.*
    链接

    1-2-3 2007-11-21 09:19:00

    觉得造成查询比较费时的情况差不多有3种:
    1. 从100万条数据中查询100条数据,where 子句中含有非索引字段,或在100万条范围内使用了分组操作。
    2. 虽然where子句中不含非索引字段,但查询语句需要得数据量很大,例如返回50万条数据。
    3. 一人连续(或多人同时)执行100次查询,例如
    select * from t1 where t1.id = 1
    select * from t1 where t1.id = 2
    select * from t1 where t1.id = 3
    ...
    select * from t1 where t1.id = 100

    是不是复用查询计划比较适用于第三种情况?
    例如第一次查询耗时300毫秒,第2至第100次查询每次需要耗时100毫秒(因为复用了查询计划),这样总耗时为300+100*99=10.2秒。
    而如果不复用查询计划,假设每次查询耗时200毫秒,总耗时为20秒。

    不知道我理解得对不对?

  12. 老赵
    admin
    链接

    老赵 2007-11-21 09:32:00

    @1-2-3
    打个比方,就拿A JOIN B操作来讲,比如是选择Nested-loop join还是merge join比较好呢?如果用户传入了一个参数1,在表A中对应了只有100条记录,那么Sql Server根据索引上的统计信息,确定了一个查询计划:使用Nested-loop join,这个性能很高。但是第二次如果传入了参数2,在表A中对应了10万条记录,这时候应该是merge join性能高,可是sql server已经缓存的查询计划,所以它还是会用nested-loop join。
    这种因为参数引起震荡比较大的话,可能就需要强制sql server重新生成查询计划。
    查询是个很复杂的东西,不能简单一概而论,一定要在实践中经过测试和统计得出的结果来进行优化。

  13. 1-2-3
    *.*.*.*
    链接

    1-2-3 2007-11-21 09:53:00

    我还是第一次听说Nested-loop join和merge join,刚刚搜了些文章来看,果然很复杂啊,受教了,谢谢。

  14. 老赵
    admin
    链接

    老赵 2007-11-21 10:12:00

    @1-2-3
    数据库很复杂的,我目前也只是略知皮毛。:)

  15. Klesh Wong
    *.*.*.*
    链接

    Klesh Wong 2007-11-21 10:26:00

    有理有据,好

  16. progame
    *.*.*.*
    链接

    progame 2007-11-21 11:16:00

    是否缓存执行计划 对执行时间的影响很多时候问题不是太大 但对内存占用的影响就很大了

  17. 金色海洋(jyk)
    *.*.*.*
    链接

    金色海洋(jyk) 2007-11-21 12:41:00

    @ 1-2-3
    1. 从100万条数据中查询100条数据,where 子句中含有非索引字段,

    你说的这种情况并不一定会影响。主要看这100万条数据占用了多大的硬盘空间。没有超过一个G的话应该不会太慢。

    其实影响速度的还是你说的第二点。对于这一点最好采用分页的方式。

    如果能够使用 top + 排序字段可以利用索引 的形式的话,那就可以快多了。

  18. 木野狐(Neil Chen)
    *.*.*.*
    链接

    木野狐(Neil Chen) 2007-11-21 13:12:00

    不错,学习了。

  19. Colin Han
    *.*.*.*
    链接

    Colin Han 2007-11-21 13:40:00

    好文,评论更精彩,学习了很多。

  20. 1-2-3
    *.*.*.*
    链接

    1-2-3 2007-11-21 14:12:00

    @金色海洋(jyk)
    没错,一般使用了分页就会解决掉很多的性能问题(而且Linq对分页的支持十分智能),但是从交互性来说有时一页一页的翻很烦的,特别是需要前后来回参照的工作。我突然想到是不是可以使用Ajax和Lazy Load配合使用呢,例如页面装载时先不显示昂贵的数据,而在需要显示昂贵数据的地方显示“正在加载...”,然后由Ajax使用单独的线程向服务器端请求这些昂贵的数据。就像IE:先加载并显示文字,然后再慢慢下载图片,不知道可不可以做得到。

  21. Enzo
    *.*.*.*
    链接

    Enzo 2007-11-21 14:46:00

    @gakaki
    我觉得效率肯定不如存储过程,

  22. 老赵
    admin
    链接

    老赵 2007-11-21 15:20:00

    @Enzo
    从数据传输大小方面来说,其实已经输在起跑线上了,呵呵。

  23. RicCC
    *.*.*.*
    链接

    RicCC 2007-11-21 16:03:00

  24. lodestar
    *.*.*.*
    链接

    lodestar 2007-11-21 16:26:00

    ◎Jeffrey Zhao
    表A中对应了只有100条记录,那么Sql Server根据索引上的统计信息,确定了一个查询计划:使用Nested-loop join.如果表A中对应了10万条记录,这时候应该是merge join性能高,可是sql server已经缓存的查询计划,所以它还是会用nested-loop join...
    这种说法可能是有误的。这种变化应该会有可能导致数据库查询计划的改变,我的理解是这样的:

    sql是4GL(第四代语言),描述的是应该做什么而不是怎么做,处理细节是被系统隐藏起来的,所以可能有多种途径来完成同一个sql。数据库端通常都会使用优化器分析使用那条途径的成本更低。执行成本最低的将被采用。好比是一条条路径,要找的是两点之间最短的那条。这就是查询计划产生的原因。
    数据库中有个区域是做sql缓存的,select * from t1 where t1.id = :1,select * from t1 where t1.id = :2;在sql缓冲中是不同的sql,但是如果绑定后再发送给数据库引擎,那么将只会有select * from t1 where t1.id = :绑定变量 一条。查询时候数据库看是否有可以重复使用的sql,没有,那只有重新生成1条sql并消耗cpu和内存资源制定相应查询计划。

    做个试验,Ling to Sql的查询条件不变,对表A的查询字段制定索引,会发现查询速度监控结果的不同。这说明查询计划并没有重用。至于上面说的现象应该是优化器造成的,有时我会手工强制查询计划提高查询效率。

  25. 老赵
    admin
    链接

    老赵 2007-11-21 16:29:00

    @lodestar
    您说的没错,我说的并不确切。
    其实Linq to Sql的问题可能应该说是,对于自动生成的sql的控制程度不够,所以很多时候没有办法改变成为较优或最优的情况。

  26. lodestar
    *.*.*.*
    链接

    lodestar 2007-11-21 16:45:00

    Linq to Sql确实不能有效复用缓冲区sql

  27. 金色海洋(jyk)
    *.*.*.*
    链接

    金色海洋(jyk) 2007-11-21 19:19:00

    @ 1-2-3
    想法倒是不错,但是在IE里面给<table> 追加行,会占用多少cpu呢?

    我曾经试过,在一个div里面添加<table> ,一个<table> 20行,加了不到20个就慢得不行了,cpu占满了。也许是我复制数据的方式太笨了吧。

  28. 有物先天下 混成不知名
    *.*.*.*
    链接

    有物先天下 混成不知名 2007-11-22 22:02:00

    @1-2-3

    这种方式最大的问题是搜索引擎不支持.

  29. 有物先天下 混成不知名
    *.*.*.*
    链接

    有物先天下 混成不知名 2007-11-22 22:07:00

    @金色海洋(jyk)

    一般不会有效率问题,即使用模版方式(Js解析模版),200行在 Ie 下都很快

  30. zhkn
    *.*.*.*
    链接

    zhkn 2008-01-19 03:24:00

    还是习惯使用SP

  31. 老赵
    admin
    链接

    老赵 2008-01-19 15:13:00

    @zhkn
    不过用一些ORM可以省下不少时间的

发表回复

登录 / 登录并记住我 ,登陆后便可删除或修改已发表的评论 (请注意保留评论内容)

昵称:(必填)

邮箱:(必填,仅用于Gavatar

主页:(可选)

评论内容(大于5个字符):

  1. Your Name yyyy-MM-dd HH:mm:ss

使用Live Messenger联系我