Wednesday, March 21, 2012

Indexing XML

I have an table-valuated function with XML as parameter. I generate table from that XML. Here is an example of parameter:

<root>
<an>
<anid>5433</anid>
<tix>64</tix>
<dataid>43</dataid>
<datavalue>bc84</datavalue>
</an>
...
</root>

function body begins with:
WITH X(anid, tix, dataid, datavalue, cnt) AS
(
SELECT
new.col.query('./anid').value('.', 'nvarchar(100)') as anid,
new.col.query('./tix').value('.', 'nvarchar(100)') as tix,
new.col.query('./dataid').value('.', 'nvarchar(100)') as dataid,
new.col.query('./datavalue').value('.', 'nvarchar(100)') as datavalue,
cnt = (
SELECT count(*) as cnt
FROM @.xmldata.nodes('/root/an') new(col)
)
FROM
@.xmldata.nodes('/root/an') new(col)
)
....

after what I use X table in joins. The problem is that the X table has no indexes and joins are very slow :[. Is there any possibility to improve performance? That is function and I cannot create index.

thanks a lot

if ur using sqlserver2005 , there is an option for xml index there, though u need to have a primary index defined before u can do it...try it out...

reply if it works out ..

No comments:

Post a Comment