Click here to Skip to main content
15,892,161 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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>




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.
Posted
Updated 20-Apr-11 2:52am
v2
Comments
Kim Togo 20-Apr-11 9:16am    
Can you post the SELECT statement to retrieve the 200 rows?

1 solution

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?
 
Share this answer
 
Comments
nit_singh 20-Apr-11 9:28am    
So you want to say that this is due to the length of the empDesc. I read somewhere that due to the XMLParser it takes some time to return because eevrytime it parsed the data. But I am not sure
Kim Togo 20-Apr-11 9:31am    
You could try change the column type from xml to text type and see if its a XMLParser ?
nit_singh 20-Apr-11 10:41am    
Kim-Even I tried after converting the column type into varchar(max) and Text, but still it is taking same time, So I think the problem may be due to size.
Kim Togo 20-Apr-11 11:15am    
Okay. Maybe you have to rethink you database design.

Do you need all the XML data to display the top 200 rows?
Perhaps you can save some of the display data in other columns ?
SELECT empid,empName,empDate FROM tbl1 ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900