当前位置：首页 > 资讯 > 技术文档

分析SQL语句性能3种方法分享

时间：2022-08-24 21:54 编辑：来源：阅读：
扫一扫，手机访问

摘要：分析SQL语句性能3种方法分享

[b]第一种方法：[/b]

Minimsdn.com为您提供的代码:
-- Turn ON [Display IO Info when execute SQL]
SET STATISTICS IO ON
-- Turn OFF [Display IO Info when execute SQL]
SET STATISTICS IO OFF

Link: http://msdn.microsoft.com/zh-cn/library/ms184361.aspx [b]第二种方法[/b]:

[u]复制代码[/u] 代码如下:

MINIMSDN.com为您提供的代码:
--Turn ON [Display detail info and the request for resources]
SET SHOWPLAN_ALL ON
-- Turn OFF [Display detail info and the request for resources]
SET SHOWPLAN_ALL OFF

Link: http://msdn.microsoft.com/zh-cn/library/ms187735 [b]第三种方法: [/b] [img]http://files.jb51.net/file_images/article/201205/201205272316447.png[/img] Links: http://msdn.microsoft.com/zh-cn/library/ff650689.aspx ; http://msdn.microsoft.com/zh-cn/library/aa175244(v=SQL.80).aspx Demo For three kinds of Method: For SQL Script:

[u]复制代码[/u] 代码如下:

select * from dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC

v [b]Its Execution plan: ([/b][img]http://files.jb51.net/file_images/article/201205/201205272316448.png[/img] [b])[/b] [img]http://files.jb51.net/file_images/article/201205/201205272316449.png[/img] v [b]Its IO info: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164410.png[/img] [b]) [/b] [b][/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164411.png[/img] [b]- - You can try one table with 100/10000/1000000 rows but create/don't create Clustered/NONCLUSTERED Index. [/b] v [b]Its Detail info Etc.: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164412.png[/img] [b])[/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164413.png[/img] [b] [/b][b]For SQL Script:[/b]

[u]复制代码[/u] 代码如下:

select top 100 * from dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC

v [b]Its Execution plan: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164414.png[/img] [b])[/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164415.png[/img] v [b]Its IO info: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164416.png[/img] [b]) [/b] [b][/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164417.png[/img] v [b]Its Detail info Etc.: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164418.jpg[/img] [b])[/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164419.png[/img] [b]For SQL Script:[/b]

[u]复制代码[/u] 代码如下:

select top 100 * from dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC
order by StagingOMC.COrgTPName

v [b]Its Execution plan: ( [/b] [b] [/b][img]http://files.jb51.net/file_images/article/201205/2012052723164420.jpg[/img] [b])[/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164421.png[/img] v [b]Its IO info: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164422.png[/img] [b]) [/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164423.png[/img] v [b]Its Detail info Etc.: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164424.png[/img] [b])[/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164425.png[/img] [b]For SQL Script:[/b]

[u]复制代码[/u] 代码如下:

select top 100 StagingOMC.COrgTPName,COUNT(CorgID) from dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC
group by StagingOMC.COrgTPName
order by StagingOMC.COrgTPName

v [b]Its Execution plan: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164426.jpg[/img] [b])[/b] [img]http://files.jb51.net/file_images/article/201205/2012052723164427.jpg[/img] v [b]Its IO info: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164428.png[/img] [b]) [/b] [b] [img]http://files.jb51.net/file_images/article/201205/2012052723164429.jpg[/img] [/b] v [b]Its Detail info Etc.: ([/b][img]http://files.jb51.net/file_images/article/201205/2012052723164430.png[/img] [b])[/b] [b] [img]http://files.jb51.net/file_images/article/201205/2012052723164431.png[/img] [/b] [b]- - By these three kinds of methods, you can try to check those words in the internet web are right or wrong about how to improve SQL Script performance. [/b]

全部评论(0)

上一篇：android使用handlerthread创建线程示例
下一篇：Android实现上拉加载更多ListView(PulmListView)

资讯排行榜
更多>>