深入SQL语句性能调整(三)

日期: 2008-12-03 来源:TechTarget中国 英文

  但这种旧写法,会像先前所提到的,外层 (外圈) 查询的「每一次」查询动作都需要引用内层 (内圈) 查询的数据。以上方示例而言,外层查询的每一笔数据,都要等内层查询「扫描整个数据表」并作比对和计数,因此830笔数据每一笔都要重复扫描整个数据表830次,所耗用的时间也因此爆增至170ms。

  若您用相同的写法,去查询AdventureWorks数据库中,有31,465笔数据的Sales.SalesOrderHeader数据表,用 ROW_NUMBER 函数要677 ms,还不到 1 秒钟;但用子查询的话,居然要高达 233,835 ms,将近快 4 分钟的时间。

  – 用ROW_NUMBER的写法,改查询AdventureWorks数据库(31,465 笔数据,要 677 ms,还不到 1 秒钟)


SELECT SalesOrderID, ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownum
FROM Sales.SalesOrderHeader
 
  – 用「子查询」的写法,改查询AdventureWorks数据库 (31,465 笔数据,要 233,835 ms,将近4分钟)


SELECT SalesOrderID,
(SELECT COUNT(*) FROM Sales.SalesOrderHeader AS 内圈
WHERE 内圈.SalesOrderID <= 外圈.SalesOrderID) AS 编号
FROM Sales.SalesOrderHeader AS 外圈
ORDER BY 编号

 
  虽然这是较极端的范例,但由此可知子查询的撰写,在使用上不可不慎,尤其是「关联子查询」。程序员在系统开发初期、数据量还很少时感受不到此种 SQL 语法的重大陷阱;但等到系统上线几个月或一两年后,就会有反应迟缓的现象, 不可不慎。

  注:AS关键词及「衍生数据表」是SQL Server 2005的新语法,「衍生数据表」只会存在内存中,AS关键词的作用是赋予一个别名。过去许多必须用暂存数据表或 View (视图) 的情况,现在都可以用「衍生数据表」来取代,如此一来不但可以降低数据库管理工作的负担,亦可提升查询性能。

  7、其他查询技巧

  • DISTINCT、ORDER BY语法,会让数据库做额外的计算。此外「联集」的使用,若没有要剔除重复数据的需求,使用 UNION ALL 会比 UNION 更优,因为后者会加入类似 DISTINCT 的算法。

  • 在 SQL Server 2005 中,存取数据库对象时,最好明确指定该对象的「结构描述 (Schema)」,也就是使用两节式的名称,如下方代码所示。否则若呼叫者的预设 Schema 不是 dbo,则 SQL Server 在执行时,会先寻找该使用者预设 Schema 所搭配的对象,找不到的话才会转而使用预设的 dbo,会多耗费寻找的时间。因此若要执行一个叫做 dbo.mySP1 的 Stored Procedure,应使用以下的两节式名称: EXEC dbo.mySP1

  8、尽可能用Stored Procedure取代应用程序直接存取数据表

  Stored Procedure 除了经过事先编译、性能较好以外,亦可节省 SQL 语句传递的网络频宽,也方便商业逻辑的重复使用。再搭配自订函数和 View 的使用,将来若要修改数据表结构、重新切割或「反正规化」时亦较方便。

  9、尽可能在数据来源层,就先过滤数据

  使用 SELECT 语法时,尽量避免传回所有的数据至前端而不设定 WHERE 等过滤条件。虽然 ASP.NET 中 SqlDataSource、ObjectDataSource 控件的 FilterExpression 可再做筛选,GridView 控件的 SortExpression 可再做排序,但会多消耗掉数据库的系统资源、web server 的内存和网络频宽。最好还是在数据库和数据来源层,就先用 SQL 条件式或 Stored Procedure 筛选出所要的资料。

  结论

  本文的观念,不管是写SQL statement、Stored Procedure、自订函数或 View 皆然。本文只是挑出程序员较容易犯的 SQL 语法性能问题,以期能在短时间浏览过本文后,在写ADO.NET程序时能修正以往随兴的 SQL 语句撰写习惯。文中提到的几点,只不过是 SQL 语法性能议题的入门。市面上有很多更进阶的书籍,例如:「The Art of SQL」、「SQL Tuning」,亦有针对 Oracle 或 SQL Server 数据库撰写的 performance tuning 相关书籍,有兴趣可自行翻阅。

我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。

我原创,你原创,我们的内容世界才会更加精彩!

【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

相关推荐