Search code examples
sql-serverxml-column

Sql Server Xml Column query performance?

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?

Solution

  • 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.