0

I'm not sure if this can be done at all but if it can it would be a huge help.

I have a system where I am not allowed to make changes to the database structures. I have yet to have code changes put in place either for that matter but at least those they will consider (bairly).

What I am dealing with is a table that contains XML data stored in a varchar(max) column. Then a view that not only references the XML using xQuery but actually has xQuery in the WHERE clause.

Here is the original query.

CREATE VIEW [dbo].[My_View]
AS
   WITH xoutput AS (   
          SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
          FROM [dbo].ci
          INNER JOIN [dbo].ct 
            ON ci.[ContentType_id] = ct.[Id]
          INNER JOIN [dbo]. cv 
            ON ci.ID = cv.[ContentItemRecord_id]
          WHERE ct.[Name] = 'MyImage' and Latest = 1) 
  SELECT h.ep.value('(MyID)[1]','varchar(10)') as MyID,        
      h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,       
      h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,       
      h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput         
  CROSS APPLY x.nodes('//Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked)[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished)[1]', 'bit') = 1 

What I thought of trying was creating an indexed view out of at least part of it.

The first thing of course was to create a SCHEMABOUND view with COUNT_BIG. Where I'm running into a problem is the grouping.

When I try it like this:

ALTER VIEW My_XML_Kenneth
WITH SCHEMABINDING
AS
    SELECT ci.[ContentType_id], ci.ID,
        h.ep.value('(IsBlocked)[1]', 'bit') AS IsBlocked,
        h.ep.value('(IsPublished)[1]', 'bit') AS IsPublished,
        h.ep.value('(MyID)[1]','varchar(10)') as MyID,
        h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,       
        h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,       
        h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
        COUNT_BIG(*) ct_bg
    FROM [dbo].ci
    INNER JOIN [dbo].ct 
        ON ci.[ContentType_id] = ct.[Id]
    INNER JOIN [dbo].cv 
        ON ci.ID = cv.[ContentItemRecord_id]
    CROSS APPLY (VALUES (CONVERT(xml, ci.[Data]))) x (x)
    CROSS APPLY x.x.nodes('//Data/MyImage') as h(ep)
    WHERE ct.[Name] = 'MyImage' 
      AND Latest = 1
    GROUP BY ci.ContentType_id, ci.ID, 
        h.ep.value('(IsBlocked)[1]', 'bit'),
        h.ep.value('(IsPublished)[1]', 'bit'),
        h.ep.value('(MyID)[1]','varchar(10)'),
        h.ep.value('(Image)[1]', 'varchar(max)'),       
        h.ep.value('(IsBlocked)[1]', 'bit'),       
        h.ep.value('(IsPublished)[1]', 'bit')
GO

I get an error

XML methods are not allowed in a GROUP BY clause.

When I try this as the GROUP BY

GROUP BY ci.ContentType_id, ci.ID, h.ep.nodes('//')

I get this error

"nodes" is not a valid function, property, or field.

Is what I'm trying even possible?

Note: last time I tried to come up with a fix to this view I got HEAVY push back that they couldn't possibly make a change. So if it isn't possible it's not like I'm out much.

  • I don't think indexing this view is going to give you magic performance benefits even if it were possible. And while I realize you can't make changes to the existing structure, you might have better luck making indexed computed columns (or populating an additional table with triggers) if you're trying to reduce the cost of calculating the XML pieces at query time. – Aaron Bertrand Oct 8 '14 at 0:21
  • My hope is that it would materialize the values from the xml. The biggest performance hit I'm getting right now is the WHERE clause uses data from one of the xquery columns. I did actually suggest computed columns the first time I looked at this and was told "It would take way to much time and effort to do regression testing." "We can't make any structure changes". I even suggested a separate table with a load job. Couldn't get them to go with that either. – Kenneth Fisher Oct 8 '14 at 1:58
  • Well you can't use XML methods in GROUP BY. You can bury that away in a CTE or derived table, but you can't outsmart SQL Server here - you can't index a view that uses a CTE or derived table. I say create a separate table and materialize these results at insert/update time either by changing the code that performs the DML or adding a trigger. Build a proof of concept to show them it can be done easily and also to show them the performance difference when querying the results that are already materialized. They may actually change their ideas of the value of regression testing. – Aaron Bertrand Oct 8 '14 at 2:06
  • (Also, if you run your queries against the materialized copies, and something doesn't pan out correctly in testing, it's pretty easy to switch the queries back to the old, slow versions. In fact if you access them via views etc. you can have wrappers or synonyms that dictate where the end user gets the data (totally transparent to them). – Aaron Bertrand Oct 8 '14 at 2:08
2

You can index a view that contains an XML column. You cannot index a view that contains any of the XML datatype methods ( eg .nodes, .query, .value, .exist ).

I would say your greatest issue is always casting the VARCHAR column to XML. Is there are a particular reason you are not using the XML datatype? Although it can alter XML slightly ( eg whitespace ), storing it as VARCHAR means you can't take advantage of the usual performance tuning methods with the XML datatype such as typing the XML ( XML SCHEMA COLLECTION ) and XML Indexes.

In the meantime, add the text() accessor and avoid repeating XML calls ( ie no need to .value IsBlocked as you have it as WHERE clause criteria ) for a performance boost:

CREATE VIEW [dbo].[My_View2_4]
AS
WITH xoutput AS (   
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,        
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,       
      0 as IsBlocked,       
      1 as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1 
GO

These were my results from a simple 100k row test rig I created: enter image description here

Simple test rig:

-- Simple test rig
USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.ci') IS NOT NULL DROP TABLE dbo.ci 
IF OBJECT_ID('dbo.ct') IS NOT NULL DROP TABLE dbo.ct 
IF OBJECT_ID('dbo.cv') IS NOT NULL DROP TABLE dbo.cv
IF OBJECT_ID('dbo.My_View') IS NOT NULL DROP VIEW dbo.My_View
IF OBJECT_ID('dbo.My_View2') IS NOT NULL DROP VIEW dbo.My_View2
IF OBJECT_ID('dbo.My_View2_4') IS NOT NULL DROP VIEW dbo.My_View2_4
GO

CREATE TABLE dbo.ci ( Id INT IDENTITY PRIMARY KEY, ContentType_id INT, data VARCHAR(MAX) )
CREATE TABLE dbo.ct ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR(20), Latest BIT )
CREATE TABLE dbo.cv ( ContentItemRecord_id INT PRIMARY KEY )
GO


;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.ci ( ContentType_id, data )
SELECT rn, '<Data>
    <MyImage>
        <MyID>' + CAST( rn AS VARCHAR(MAX) ) + '</MyID> 
        <Image>' + CAST( NEWID() AS VARCHAR(MAX) ) + '</Image>
        <IsBlocked>0</IsBlocked>
        <IsPublished>1</IsPublished>
    </MyImage>
</Data>'
FROM cte
GO

CHECKPOINT

INSERT INTO dbo.ct ( Name, Latest )
SELECT 'MyImage', ContentType_id
FROM dbo.ci

CHECKPOINT

INSERT INTO dbo.cv ( ContentItemRecord_id )
SELECT Id
FROM dbo.ci
GO

-- Original view
CREATE VIEW [dbo].[My_View]
AS
WITH xoutput AS (   
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID)[1]','varchar(10)') as MyID,        
      h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,       
      h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,       
      h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('//Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked)[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished)[1]', 'bit') = 1 
GO


CREATE VIEW [dbo].[My_View2]
AS
WITH xoutput AS (   
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,        
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,       
      h.ep.value('(IsBlocked/text())[1]', 'bit') as IsBlocked,       
      h.ep.value('(IsPublished/text())[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1 
GO

--... a few other aborted attempts. 2.1 to 2.3

CREATE VIEW [dbo].[My_View2_4]
AS
WITH xoutput AS (   
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,        
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,       
      0 as IsBlocked,       
      1 as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1 
GO


CREATE VIEW [dbo].[My_View2_5]
AS
WITH xoutput AS (   
    SELECT ci.[Data],  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,        
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,       
      0 as IsBlocked,       
      1 as IsPublished,
      [ContentType_id], ID
FROM xoutput
    CROSS APPLY (SELECT CONVERT(xml, [Data]).query('.')) AS T(x)
        CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1 
GO

/*
SELECT *
FROM [dbo].[My_View]
GO
SELECT *
FROM [dbo].[My_View2_4]
GO
SELECT *
FROM [dbo].[My_View2_5]
GO
*/
  • Very nice. I'll give the text() accessor a shot. Unfortunately when I suggested changing to an XML data type I was told "NO" very firmly. It's a combination of vendor code and very touchy developers with a lot of pull when it comes to this system. I might be able to get a minor change like that put in place though. Again thanks! – Kenneth Fisher Oct 8 '14 at 13:50
  • You could also try to push the convert to a cross apply and adding a .query('.'). CROSS APPLY (SELECT CONVERT(xml, ci.[Data]).query('.')) AS T(x) and then query T.x as the first column in the CTE. Would be interesting to see what it does to your test rig. – Mikael Eriksson Oct 8 '14 at 14:07
  • Hi @MikaelEriksson, I've added my (very) simple test rig and your suggestion (as view dbo.myView_2_5). The extra CROSS APPLY didn't seem to help but see what you make of it. I did try a few different arrangements (hence my missing numbers, 2_1, 2_2 etc ) including pushing the WHERE clause up into the .nodes method but that didn't seem to help. I also tried converting the column to XML and adding a primary xml idex, which brought the best results (<1 second). Unfortunately this option is not available to the OP. – wBob Oct 8 '14 at 14:29
  • I was getting at that the convert could be treated as a deferred compute scalar, described by Paul White in Compute Scalars, Expressions and Execution Plan Performance. If it were, the convert would happen everywhere where the expression is referenced in the query plan. Putting the convert in a cross apply and using query(.) does prevent the deferred compute scalar in other plans. Here it looks like it only adds some overhead. – Mikael Eriksson Oct 8 '14 at 15:26
  • Perhaps it is not deferred when using the test data you have here and perhaps it is with other data. @KennethFisher should test on his actual data to see if it has any positive effect. It is not possible to see in the execution plan if the compute scalar is deferred or not. – Mikael Eriksson Oct 8 '14 at 15:28

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.