2

I'm trying to query an xml column in sql server. I've created a primary index on the column and query it using:

SELECT *
FROM MyTable
where  Doc.exist('/xml/root/propertyx/text()[. = "something"]') = 1

In a table with 60 000 entries , this query takes some 100 ms on my local dev machine. Is it possible to optimize this somehow to increase performance of the query?

Roger Johansson
  • 22,764
  • 18
  • 97
  • 193

2 Answers2

1

You can optimize for fast query times with a calculated column. A calculated column can't use the XML functions directly, so you have to wrap them in a function:

go
create function dbo.GetSomethingExists(
    @Doc xml)
returns bit
with schemabinding
as begin return (
     select  @Doc.exist('/xml/root/property/text()[. = "something"]')
) end
go
create table TestTable (
    Doc xml,
    SomethingExists as dbo.GetSomethingExists(Doc) persisted
)
go

If you declare the function with schemabinding, you can create an index on SomethingExists:

create index IX_TestTable_SomethingExists on TestTable(SomethingExists)

This should make the query much faster.

Andomar
  • 232,371
  • 49
  • 380
  • 404
0

Creating a Secondary XML Index of Path type might speed things up for you.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281