快捷搜索:  as  2018  FtCWSyGV  С˵  test  xxx  Ψһ  w3viyKQx

澳门新葡亰集团:对比SQL Server中XML AUTO和TSQL命令(1)SQL Server



XML AUTO功能与T-SQL敕令

作为一个DBA,我倾向于关注机能方面的问题,是以我要确定应用XML(扩展标记说话)并不会对机能孕育发生影响。在本文里,我会经由过程对照澳门新葡亰集团XML AUTO功能和标准的T-SQL敕令来显示机能上的差异。在我的测试历程中,我会仅仅涉及到XML AUTO功能的一个对照小的根基的部分,同时我也建议大年夜家在你自己的情况傍边来测试你将要应用的XML功能。

测试情况描述

在我的测试傍边,我会建一个包孕了一万行记录的表格,如下所述:

表格包孕的记录如下:

select * from Employes

XML Auto处置惩罚大年夜量的数据

在我的表格中我应用了如下的查询语句,以一个标准的T-SQL查询开始,并且接着每次都向敕令添加了更多的XML布局:

SELE澳门新葡亰集团CT*FROMEmployes

Go

SELECT*FROMEmployesFORXMLAUTO

go

SELECT*FROMEmployesFORXMLAUTO,TYPE

go

SELECT*FROMEmployesFORXMLAUTO,TYPE,ELEMENTS

go

SELECT*FROMEmployesFORXMLAUTO,TYPE,ELEMENTS,

ROOT

go

我在SQL Profiler中监控了这些敕令。我发明Duration列在SQL Profiler中是不准确的。当Query Analyzer仍旧在处置惩罚结果的时刻已经履行的XML敕令已经在Profiler中显示了。是以我会轻忽掉落这列,并且察看其他的资本:CPU和I/O。下面是对五个敕令履行三次的监控结果:

这看起来显示了T-SQL查询比其他的要领体现的要好。XML AUTO的XML查询应用了跨越八倍的CPU资本但却孕育发生了一样的I/O。繁杂的XML敕令比T-SQL敕令耗损了跨越80倍的读取操作,同时还有许多写操作和上面的六倍的CPU。

什么时刻阐发敕令孕育发生的I/O统计数据:

Table'Employes'.Scancount1,logicalreads8247,physical

reads0,read-aheadreads7520,loblogicalreads0,lob

physicalreads0,lobread-aheadreads0.

Table'Employes'.Scancount1,logicalreads8247,physical

reads0,read-aheadreads4221,loblogicalreads0,lob

physicalreads0,lobread-aheadreads0.

Table'Employes'.Scancount1,logicalreads82澳门新葡亰集团47,physical

reads500,read-aheadreads2586,loblogicalreads0,lob

physicalreads0,lobread-aheadreads0.

Table'Worktable'.Scancount0,logicalreads7,physical

reads1,read-aheadreads0,loblogicalreads229128,lob

physicalreads285,lobread-aheadreads43082.

Table'Employes'.Scancount1,logicalreads8247,physical

reads0,read-aheadreads1394,loblogicalreads0,lob

physicalreads0,lobread-aheadreads0.

Table'Worktable'.Scancount0,logicalreads7,physical

reads0,read-aheadreads0,loblogicalreads230170,lob

physicalreads0,lobread-aheadreads43115.

Table'Employes'.Scancount1,logicalreads8247,physical

reads125,read-aheadreads3835,loblogicalreads0,lob

physicalreads0,lobread-aheadreads0.

Table'Worktable'.Scancount0,logicalreads7,physical

reads0,read-aheadreads0,loblogicalreads230170,lob

physicalreads140,lobread-aheadreads43115.

履行计划同样也显示了相同的事情量:

1000rows:

SELECT*FROMEmployeswhereidbetween5000and

6000

go

SELECT*FROMEmployeswhereidbetween5000and

6000

FORXMLAUTO

go

SELECT*FROMEmployeswhereidbetween5000and

6000

FORXMLAUTO,TYPE

go

SELECT*FROMEmployeswhereidbetween5000and

6000

FORXMLAUTO,TYPE,ELEMENTS

go

SELECT*FROMEmployeswhereidbetween5000and

6000

FORXMLAUTO,TYPE,ELEMENTS,ROOT

go

SQLProfiler:

StatisticsI/O:

Table'Employes'.Scancount1,logicalreads87,physical

reads0,read-aheadreads0,loblogicalreads0,lobphysical

reads0,lobread-aheadreads0.

Table'Employes'.Scancount1,logicalreads87,physical

reads0,read-ahead澳门新葡亰集团reads0,loblogicalreads0,lobphysical

reads0,lobread-aheadreads0.

Table'Employes'.Scancount1,logicalreads87,physical

reads0,read-aheadreads0,loblogicalreads0,lobphysical

reads0,lobread-aheadreads0.

Table'Worktable'.Scancount0,logicalreads7,physical

reads0,read-aheadreads0,loblogicalreads5222,lob

physicalreads0,lobread-aheadreads242.

Table'Employes'.Scancount1,logicalreads87,physical

reads0,read-aheadreads0,loblogicalreads0,lobphysical

reads0,lobread-aheadreads0.

Table'Worktable'.Scancount0,logicalreads7,physical

reads0,read-aheadreads0,loblogicalreads5229,lob

physicalreads0,lobread-aheadreads245.

Table'Employes'.Scancount1,logicalreads87,physical

reads0,read-aheadreads0,loblogicalreads0,lobphysical

reads0,lobread-aheadreads0.

Table'Worktable'.Scancount0,logicalreads7,physical

reads0,read-aheadreads0,loblogicalreads5229,lob

physicalreads0,lobread-aheadreads245.

Execution plan:

什么时刻应用一个Worktable?

优化器应用一个Worktable的限定究竟在哪呢?这取决于优化器和用来做XML解析的内存和数据大年夜小相关的事情量的大年夜小。在我的查询傍边返回的XML被存储在一个大年夜的内存中的XML变量中。上面的限定并不是一个确定的数字。在SQL Server Developer Center 哪里有关职员说:“光是XML的变量和参数相关的内容就可以达到2GB。当值很小的时刻他们应用主存作为存储的载体。然则,大年夜的值就要存储在tem澳门新葡亰集团pdb中。”

结论

XML的数据和功能可能比标准的T-SQL耗损更多的资本。是以,假如当查询涉及到大年夜量的数据或者XML功能加倍繁杂的时刻,最好在数据库级别应用标准的T-SQL。同时也建议大年夜家来测试自己的XML机能,从而确保在数据库中应用XML不会低落系统的机能。

您可能还会对下面的文章感兴趣: