CODEWITHSUNDEEP
sql-serverxml-column
Roger Johansson
Roger Johansson

Reputation: 23214

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?

Upvotes: 2

Views: 1305

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 0

Andomar
Andomar

Reputation: 238068

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.

Upvotes: 1

Related Questions