XML类型列面临的问题 [英] Facing problem with XML type column

问题描述

我有一个带有XML类型列的表.该表中包含一千多行.
当我尝试选择前200行时，这需要2秒钟以上的时间.

检查下面的示例代码

I have a table with a column of XML type. This table contains more than thousand rows in it.
When I am trying to select top 200 rows , it is taking more than 2 seconds.

check the sample code below

<code>
create table tblEmp
(
	empid int identity(1,1),
	empDesc xml
)
GO
declare @i int
set @i=1
while @i < 200
begin
insert into tblEmp values(
''<employees>
	<employee dept="ITS">
		<id>''+convert(varchar, @i)+''</id>
		<name>NAME''+convert(varchar, @i)+''</name>	
		<address>ADDRESS''+convert(varchar, @i)+''</address>
		<phone>	
			<homeph valid="true">HOMEPHONE''+convert(varchar, @i)+''</homeph>
			<workph>WORKPH''+convert(varchar, @i)+''</workph>
		</phone>
	</employee>
</employees>'')
set @i = @i +1
end
Go</code>

从tblEmp中选择empDesc-花费3秒钟以上的时间
从tblEmp中选择empid-连一秒钟都没有



谁能告诉我为什么要花太多时间才能返回200行.
提前谢谢.

select empDesc from tblEmp -- Taking more than 3 seconds
select empid from tblEmp -- not taking even a second



Can anyone tell me why it is taking too much time only to return 200 rows.
Thanks in advance.

推荐答案

这只是一个猜测.

列"empid"可能是一个自动递增的数字，并在您的sql server上建立了索引.因此，数据受到限制，它必须返回的内容.

列"empDesc"也许很沉重"，无法检索并返回到您的程序?

This is only a guess.

Column "empid" is probable an auto increment number and is indexed on you sql server. Therefore data is limited what it has to return.

Column "empDesc" is perhaps "heavy" to retrieve and return to your program?

这篇关于XML类型列面临的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！