| microsoft.public.sqlserver.server 2005-12-22, 3:24 am |
| the test code£º
--Step1:Create the xml Schema
CREATE XML SCHEMA COLLECTION PermissionSchema
AS
'<?xml version="1.0" standalone="yes"?>
<xs:schema id="Permission" xmlns="http://www.gocean.com.cn"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Permission" msdata:IsDataSet="true"
msdata:Locale="zh-CN">
<xs:complexType>
<xs:choice minOccurs="1" maxOccurs="unbounded">
<xs:sequence>
<xs:element name="Sec1" type="xs:int" minOccurs="1" />
<xs:element name="Sec2" type="xs:int" minOccurs="1" />
<xs:element name="Sec3" type="xs:int" minOccurs="1" />
<xs:element name="Sec4" type="xs:int" minOccurs="1" />
<xs:element name="Sec5" type="xs:int" minOccurs="1" />
<xs:element name="Sec6" type="xs:int" minOccurs="1" />
<xs:element name="Sec7" type="xs:int" minOccurs="1" />
<xs:element name="Sec8" type="xs:int" minOccurs="1" />
<xs:element name="Sec9" type="xs:int" minOccurs="1" />
<xs:element name="Sec10" type="xs:int" minOccurs="1" />
</xs:sequence>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>'
select * from sys. xml_schema_collectio
ns
--Step 2:Create a table
CREATE TABLE Permission
(ID int IDENTITY(1,1),
PXml xml(PermissionSchema
),
PInt int)
ALTER TABLE [Permission] ADD
CONSTRAINT [PK_Permission] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
--Create a SP
CREATE PROCEDURE SaveXML(@XML xml,@Int int) AS
insert Permission(PXml,PInt
) values(@XML,@Int)
--Create XML index £¨Note£ºthe query will be faster without the xml
index.£©
create primary xml index xidx_Permission on Permission(PXml)
create xml index xidx_Permission_path
on Permission(PXml) using xml index
xidx_Permission for path
create xml index xidx_Permission_prop
erty on Permission(PXml) using xml
index xidx_Permission for property
create xml index xidx_Permission_valu
e on Permission(PXml) using xml index
xidx_Permission for value
--Step 3£ºinsert some data
set nocount on
Declare @times int,@i int
set @times=100000
set @i=1
Declare @max int
set @max=1024
declare @pxml varchar(1000),@int int
while @i<@times
Begin
Select @int=convert(int,Ran
d()*@Max)
select @pxml='<Permission><Sec1>'
+convert(varchar(1),
@int & power(2,0))+'</Sec1><Sec2>'
+convert(varchar(1),
@int & power(2,1))+'</Sec2><Sec3>'
+convert(varchar(1),
@int & power(2,2))+'</Sec3><Sec4>'
+convert(varchar(1),
@int & power(2,3))+'</Sec4><Sec5>'
+convert(varchar(2),
@int & power(2,4))+'</Sec5><Sec6>'
+convert(varchar(2),
@int & power(2,5))+'</Sec6><Sec7>'
+convert(varchar(2),
@int & power(2,6))+'</Sec7><Sec8>'
+convert(varchar(3),
@int & power(2,7))+'</Sec8><Sec9>'
+convert(varchar(3),
@int & power(2,8))+'</Sec9><Sec10>'
+convert(varchar(3),
@int & power(2,9))+'</Sec10></Permission>'
--Select @pxml,@int
Exec SaveXML @pxml,@int
Set @i=@i+1
End
set nocount off
--Step 4£ºthe last testing code£¬ "Select XML" vs "Select int"
--Result£ºif without xml schema£¬especially without xml index£¬the sql
sentence will run fastest.But why??
select getdate()
set nocount on
Declare @times int,@i int,@int int
set @times=10000
set @i=1
declare @id int,@rl int
While @i<@times
begin
Select @int=convert(int,Ran
d()*100000)
Select @id=ID
--,PXml.query('/Permission/Sec1[/Permission/Sec1>0]')
,@rl=PXml.value('(/Permission/Sec3)[1]','int') --case1: Comment case2
--,@rl=(pint & power(2,3)) --case2£ºComment case1
From
permission
with(nolock)
Where ID=@int
select @i=@i+1
end
|