WEB开发网
开发学院数据库MSSQL Server 对比SQL Server中XML AUTO和T-SQL命令 阅读

对比SQL Server中XML AUTO和T-SQL命令

 2007-08-02 09:47:12 来源:WEB开发网   
核心提示: 执行计划同样也显示了相同的工作量:1000rows:SELECT*FROMEmployeswhereidbetween5000and6000goSELECT*FROMEmployeswhereidbetween5000and6000FORXMLAUTOgoSELECT*FROMEmploy

执行计划同样也显示了相同的工作量:

  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-aheadreads0,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。当值很小的时候他们使用主存作为存储的载体。但是,大的值就要存储在tempdb中。”

结论

XML的数据和功能可能比标准的T-SQL消耗更多的资源。因此,如果当查询涉及到大量的数据或者XML功能更加复杂的时候,最好在数据库级别使用标准的T-SQL。同时也建议大家来测试自己的XML性能,从而确保在数据库中使用XML不会降低系统的性能。

上一页  1 2 3 4 

Tags:对比 SQL Server

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接