2

Suppose I have the following XML data, where

  • <root> can have an arbitrary number of <child> children
  • <child> can have an arbitrary number of <grandchild> children
  • <grandchild> can have an arbitrary number of <greatgrandchild> children
  • <child> and <grandchild> can have an arbitrary number of <parameter> children
  • each <parameter> has one <name> and one <value> child
  • the depth stops at greatgrandchild (there is no need for arbitrary depths)

For example,

<root name="root">
  <child name="a">
    <grandchild name="a1">
      <parameter>
        <name>param1</name>
        <value>ABC123</value>
      </parameter>
      <parameter>
        <name>param2</name>
        <value>CBC</value>
      </parameter>
      <greatgrandchild name="a1a">
        <parameter>
          <name>paramA</name>
          <value>ABC</value>
        </parameter>
        <parameter>
          <name>paramB</name>
          <value>DBC</value>
        </parameter>
      </greatgrandchild>
    </grandchild>
  </child>
</root>

What I need to do is select the parameter with a value containing "_BC", where _ is a character that is not A.

I have the following query

SELECT
    TestId,
    [root],
    child,
    grandchild,
    NULL AS greatgrandchild,
    parameter
FROM (
        SELECT
            TestId,
            [root].a.value('@name', 'varchar(max)') AS [root],
            child.b.value('@name', 'varchar(max)') AS child,
            grandchild.c.value('@name', 'varchar(max)') AS grandchild,
            parameter.d.value('(name)[1]', 'varchar(max)') AS parameter,
            parameter.d.value('(value)[1]', 'varchar(max)') AS parameter_value
        FROM
            dbo.TestTable
            CROSS APPLY TestData.nodes('/root') AS [root](a)
            CROSS APPLY [root].a.nodes('child') AS child(b)
            CROSS APPLY child.b.nodes('grandchild') AS grandchild(c)
            CROSS APPLY grandchild.c.nodes('parameter') AS parameter(d)
    ) t
WHERE
    parameter_value LIKE '%[^Aa]BC%'
UNION ALL
SELECT
    TestId,
    [root],
    child,
    grandchild,
    greatgrandchild,
    parameter
FROM (
        SELECT
            TestId,
            [root].a.value('@name', 'varchar(max)') AS [root],
            child.b.value('@name', 'varchar(max)') AS child,
            grandchild.c.value('@name', 'varchar(max)') AS grandchild,
            greatgrandchild.d.value('@name', 'varchar(max)') AS greatgrandchild,
            parameter.e.value('(name)[1]', 'varchar(max)') AS parameter,
            parameter.e.value('(value)[1]', 'varchar(max)') AS parameter_value
        FROM
            dbo.TestTable
            CROSS APPLY TestData.nodes('/root') AS [root](a)
            CROSS APPLY [root].a.nodes('child') AS child(b)
            CROSS APPLY child.b.nodes('grandchild') AS grandchild(c)
            CROSS APPLY grandchild.c.nodes('greatgrandchild') AS greatgrandchild(d)
            CROSS APPLY greatgrandchild.d.nodes('parameter') AS parameter(e)
    ) t
WHERE
    parameter_value LIKE '%[^Aa]BC%'

For example, for the following test data:

CREATE TABLE TestTable (
    TestId INT PRIMARY KEY,
    TestData XML NOT NULL
);

INSERT INTO dbo.TestTable (TestId, TestData)
VALUES (1, '<root name="root">
  <child name="a">
    <grandchild name="a1">
      <parameter>
        <name>param1</name>
        <value>ABC123</value>
      </parameter>
      <parameter>
        <name>param2</name>
        <value>CBC</value>
      </parameter>
      <greatgrandchild name="a1a">
        <parameter>
          <name>paramA</name>
          <value>ABC</value>
        </parameter>
        <parameter>
          <name>paramB</name>
          <value>DBC</value>
        </parameter>
      </greatgrandchild>
    </grandchild>
  </child>
</root>');

the following resultset should be returned:

TestId root child grandchild greatgrandchild parameter
------------------------------------------------------
1      root a     a1         NULL            param2
1      root a     a1         a1a             paramB

I am wondering whether my SELECT query can be optimized

On a 400-row table with 20KB XML data each row, the SELECT query takes 40 minutes. The XML layout cannot be changed.

2
  • Have you tried adding an XMLschema? Sep 11 '14 at 19:07
  • I suspect you'll need to look at XML indexes to optimize this. These operations are VERY CPU-intensive otherwise.
    – JNK
    Sep 11 '14 at 19:28
5

Here is a rewrite of what you already have.

  1. No shredding necessary on root node.
  2. Specify the text() node for the parameter name and parameter value.

In my limited tests that made it significantly faster. I look forward to see what if any performance gain you see on your side.

select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       null as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'
union all
select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       GGC.X.value('@name', 'varchar(max)') as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('greatgrandchild') as GGC(X)
  cross apply GGC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'

Update:

I took the liberty to execute the test rig provided by wBob on SQL Server 2014 with Compatibility level 110 (SQL Server 2012) and 120 (SQL Server 2014)

Result:

Compatibility level  OP's query  My query  wBob query using XML indexes
-------------------  ----------  --------  ----------------------------
110                  64 sec      37 sec    1 sec
120                  8 sec       4 sec     5 sec

The difference you see in execution time is there because SQL Server 2014 uses a new cardinality estimator. When compatibility level is 110 SQL Server will use the old estimator. Turning the new cardinality estimator on or off using trace flags yields the exact same result.

The difference in times for the queries that does not use the XML indexes is because in Compatibility level 120 the plans are parallel with DOP 16 in my case.

It is also really interesting to notice that the execution time for using XML indexes is five times slower when using the new cardinality estimator. The reason for that is the same as above only the other way around. There is a parallel plan only when using the old cardinality estimator.

1
  • Interesting work Mikael! I have found SQL Server can make bad choices for plans with XML indexes. I have had some limited success getting better plans in these cases by temporarily turning off the costing model (using undocumented DBCC commands). I try this out for this case and report back, if I get time!
    – wBob
    Sep 12 '14 at 19:11
1

I get a good result with an XML Schema Collection and a primary XML index. I don't think the XSC is adding to the performance particularly but it's a good constraint to have.

My complete test rig:

USE tempdb
GO

SET NOCOUNT ON

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.testTable') IS NOT NULL DROP TABLE dbo.testTable
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2
IF OBJECT_ID('tempdb..#tmp3') IS NOT NULL DROP TABLE #tmp3
GO

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = N'xsc_test' )
    DROP XML SCHEMA COLLECTION xsc_test
GO

CREATE XML SCHEMA COLLECTION xsc_test
AS N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="child" maxOccurs="unbounded">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="grandchild" maxOccurs="unbounded">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element maxOccurs="unbounded" name="parameter">
                                            <xs:complexType>
                                                <xs:sequence>
                                                    <xs:element name="name" type="xs:string" />
                                                    <xs:element name="value" type="xs:string" />
                                                </xs:sequence>
                                            </xs:complexType>
                                        </xs:element>
                                        <xs:element name="greatgrandchild" maxOccurs="unbounded">
                                            <xs:complexType>
                                                <xs:sequence>
                                                    <xs:element maxOccurs="unbounded" name="parameter">
                                                        <xs:complexType>
                                                            <xs:sequence>
                                                                <xs:element name="name" type="xs:string" />
                                                                <xs:element name="value" type="xs:string" />
                                                            </xs:sequence>
                                                        </xs:complexType>
                                                    </xs:element>
                                                </xs:sequence>
                                                <xs:attribute name="name" type="xs:string" use="required" />
                                            </xs:complexType>
                                        </xs:element>
                                    </xs:sequence>
                                    <xs:attribute name="name" type="xs:string" use="required" />
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
            <xs:attribute name="name" type="xs:string" use="required" />
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

CREATE TABLE dbo.testTable ( testId INT, testData XML,
    CONSTRAINT PK_testTable  PRIMARY KEY ( testId ) )
GO


INSERT INTO dbo.TestTable (TestId, TestData)
VALUES (1, '<root name="root">
  <child name="a">
    <grandchild name="a1">
      <parameter>
        <name>param1</name>
        <value>ABC123</value>
      </parameter>
      <parameter>
        <name>param2</name>
        <value>CBC</value>
      </parameter>
      <greatgrandchild name="a1a">
        <parameter>
          <name>paramA</name>
          <value>ABC</value>
        </parameter>
        <parameter>
          <name>paramB</name>
          <value>DBC</value>
        </parameter>
      </greatgrandchild>
    </grandchild>
  </child>
</root>');
GO

;WITH cte AS 
(
SELECT 2 x
UNION ALL
SELECT x + 1
FROM cte
WHERE x < 400
)
INSERT INTO dbo.testTable ( testId, testData )
SELECT x, 
    (
    SELECT 
        'root' + CAST( x AS VARCHAR(5) ) AS "@name",
        (
        SELECT x AS "@name",
            (
            SELECT x * 10 AS "@name",
                (
                SELECT
                    'param' + CAST( x AS VARCHAR(5) ) AS "name",
                    CHAR(x + 64) + 'BC' AS value
                FROM ( SELECT TOP 5 * FROM cte ) [parameters] -- grandchild parameters
                FOR XML PATH('parameter'), TYPE
                ),
                (
                SELECT x * 100 AS "@name",
                    (
                    SELECT
                        'param' + CAST( x AS VARCHAR(5) ) AS "name",
                        CHAR(x + 64) + 'BC' AS value
                    FROM ( SELECT TOP 3 * FROM cte ) [parameters]   -- greatgrandchild parameters
                    FOR XML PATH('parameter'), TYPE
                    )
                FROM ( SELECT TOP 2 * FROM cte ) greatgrandchildren -- make 2 greatgrandchildren
                FOR XML PATH('greatgrandchild'), TYPE
                )
                FROM ( SELECT TOP 3 * FROM cte ) grandchildren -- make 3 grandchildren
                FOR XML PATH('grandchild'), TYPE
            )
        FROM ( SELECT TOP 5 * FROM cte ) children -- make 5 children
        FOR XML PATH('child'), TYPE
        )
    FOR XML PATH('root'), TYPE
    ) yourXML
FROM cte
OPTION ( MAXRECURSION 400 )
GO

-- Setup END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Original Query START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
GO

DECLARE @startDate DATETIME = GETDATE()

-- Original query
SELECT
    TestId,
    [root],
    child,
    grandchild,
    NULL AS greatgrandchild,
    parameter
INTO #tmp1
FROM (
        SELECT
            TestId,
            [root].a.value('@name', 'varchar(max)') AS [root],
            child.b.value('@name', 'varchar(max)') AS child,
            grandchild.c.value('@name', 'varchar(max)') AS grandchild,
            parameter.d.value('(name)[1]', 'varchar(max)') AS parameter,
            parameter.d.value('(value)[1]', 'varchar(max)') AS parameter_value
        FROM
            dbo.TestTable
            CROSS APPLY TestData.nodes('/root') AS [root](a)
                CROSS APPLY [root].a.nodes('child') AS child(b)
                    CROSS APPLY child.b.nodes('grandchild') AS grandchild(c)
                        CROSS APPLY grandchild.c.nodes('parameter') AS parameter(d)
    ) t
WHERE
    parameter_value LIKE '%[^Aa]BC%'
UNION ALL
SELECT
    TestId,
    [root],
    child,
    grandchild,
    greatgrandchild,
    parameter
FROM (
        SELECT
            TestId,
            [root].a.value('@name', 'varchar(max)') AS [root],
            child.b.value('@name', 'varchar(max)') AS child,
            grandchild.c.value('@name', 'varchar(max)') AS grandchild,
            greatgrandchild.d.value('@name', 'varchar(max)') AS greatgrandchild,
            parameter.e.value('(name)[1]', 'varchar(max)') AS parameter,
            parameter.e.value('(value)[1]', 'varchar(max)') AS parameter_value
        FROM
            dbo.TestTable
            CROSS APPLY TestData.nodes('/root') AS [root](a)
            CROSS APPLY [root].a.nodes('child') AS child(b)
            CROSS APPLY child.b.nodes('grandchild') AS grandchild(c)
            CROSS APPLY grandchild.c.nodes('greatgrandchild') AS greatgrandchild(d)
            CROSS APPLY greatgrandchild.d.nodes('parameter') AS parameter(e)
    ) t
WHERE
    parameter_value LIKE '%[^Aa]BC%'

SELECT @@rowcount r, DATEDIFF( s, @startDate, GETDATE() ) diff1
GO

-- Original Query END
------------------------------------------------------------------------------------------------




------------------------------------------------------------------------------------------------
-- Rewrite 1 START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2

DECLARE @startDate DATETIME = GETDATE()

select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       null as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
INTO #tmp2
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'
union all
select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       GGC.X.value('@name', 'varchar(max)') as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('greatgrandchild') as GGC(X)
  cross apply GGC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'

SELECT @@rowcount r, DATEDIFF( s, @startDate, GETDATE() ) diff2
GO

-- Rewrite 1 END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- XSC with XML Index START
------------------------------------------------------------------------------------------------

-- Size Before
EXEC sp_spaceused 'testTable', 'true'
GO
ALTER TABLE dbo.testTable ALTER COLUMN testData XML(xsc_test)
CREATE PRIMARY XML INDEX xidx_testTable ON dbo.testTable(testData)
GO

-- Size After
EXEC sp_spaceused 'testTable', 'true'
GO

DECLARE @startDate DATETIME = GETDATE()

SELECT
    t.testId,
    r.c.value('@name', 'VARCHAR(20)') [root],
    c.c.value('@name', 'VARCHAR(20)') child,
    gc.c.value('@name', 'VARCHAR(20)') grandchild,
    NULL AS greatgrandchild,
    p.c.value('(name)[1]', 'VARCHAR(20)') parameter,
    p.c.value('(value)[1]', 'VARCHAR(20)') value

INTO #tmp

FROM dbo.testTable t
    CROSS APPLY t.testData.nodes('root') AS r(c)
        CROSS APPLY r.c.nodes('child') AS c(c)
            CROSS APPLY c.c.nodes('grandchild') AS gc(c)
                CROSS APPLY gc.c.nodes('parameter') AS p(c)

UNION ALL

SELECT
    t.testId,
    r.c.value('@name', 'VARCHAR(20)') [root],
    c.c.value('@name', 'VARCHAR(20)') child,
    gc.c.value('@name', 'VARCHAR(20)') grandchild,
    ggc.c.value('@name', 'VARCHAR(20)') grandchild,
    p.c.value('(name)[1]', 'VARCHAR(20)') parameter,
    p.c.value('(value)[1]', 'VARCHAR(20)') value

FROM dbo.testTable t
    CROSS APPLY t.testData.nodes('root') AS r(c)
        CROSS APPLY r.c.nodes('child') AS c(c)
            CROSS APPLY c.c.nodes('grandchild') AS gc(c)
                CROSS APPLY gc.c.nodes('greatgrandchild') AS ggc(c)
                    CROSS APPLY ggc.c.nodes('parameter') AS p(c)

SELECT *
INTO #tmp3
FROM #tmp
WHERE value Like '[^A]BC%'

SELECT @@rowcount r, DATEDIFF( s, @startDate, GETDATE() ) diff3
GO

-- XSC with XML Index END
------------------------------------------------------------------------------------------------

All queries perform well with the primary xml index. Obviously there is a disk space penalty with the XML index, in my rig the table went from ~3MB to ~24MB!

I can't see secondary xml indexes or selective xml indexes adding a lot to this (as you are basically traversing the whole document) but might give them a try if I get time.

1
  • 1
    Re 2014 work @MikaelEriksson, got best results with the "encourage parallel plan" trace flag although can't really encourage for use production.
    – wBob
    Sep 12 '14 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.