December 23, 2014 at 3:31 am
I'm trying to make use of this new feature in SQL 2012 and struggling.
I have a table with an typed XML column containing XML looking like
<rdf:RDF xmlns:bl="http://www.bl.uk/schemas/digitalobject/entities#" xmlns:rts="http://cosimo.stanford.edu/sdr/metsrights/" xmlns:xsd="http://www.w3.org/2001/XMLSchema#" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:mods="http://www.loc.gov/mods/v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<rdf:Description rdf:about="ark:/81055/dvdc_100018205527.0x0001ef">
<rdf:type rdf:resource="http://www.bl.uk/schemas/digitalobject/entities#article" />
<bl:hasInformationPackageDescription rdf:resource="" />
<bl:hasRightsMD rdf:resource="ark:/81055/dvdc_100018234679.0x0000e9" />
<bl:isDescribedBy rdf:resource="ark:/81055/dvdc_100018234679.0x0000ea" />
</rdf:Description>
</rdf:RDF>
I want to query the value of one of the attributes (which should be unique), so I create a index
CREATE SELECTIVE XML INDEX test_mdark ON dbo.Metadata_Structural(MetadataValue)
WITH XMLNAMESPACES ('http://www.w3.org/1999/02/22-rdf-syntax-ns#' AS rdf, 'http://www.bl.uk/schemas/digitalobject/entities#' AS bl)
FOR ( MDARKpath = '(/rdf:RDF/rdf:Description/bl:isDescribedBy/@rdf:resource)')
WITH (SORT_IN_TEMPDB = ON)
But I am struggling to make use of the index.
WITH XMLNAMESPACES ('http://www.w3.org/1999/02/22-rdf-syntax-ns#' AS rdf, 'http://www.bl.uk/schemas/digitalobject/entities#' AS bl)
SELECT *
FROM dbo.Metadata_Structural WHERE MetadataValue.exist('(rdf:RDF/rdf:Description/bl:isDescribedBy/@rdf:resource)') = 1;
Uses the selective index as I would expect.
WITH XMLNAMESPACES ('http://www.w3.org/1999/02/22-rdf-syntax-ns#' AS rdf, 'http://www.bl.uk/schemas/digitalobject/entities#' AS bl)
SELECT *
FROM dbo.Metadata_Structural WHERE MetadataValue.value('(rdf:RDF/rdf:Description/bl:isDescribedBy/@rdf:resource)[1]','nvarchar(max)') = @MDARK;
Ignores the selective index completely
WITH XMLNAMESPACES ('http://www.w3.org/1999/02/22-rdf-syntax-ns#' AS rdf, 'http://www.bl.uk/schemas/digitalobject/entities#' AS bl)
SELECT *
FROM dbo.Metadata_Structural WHERE MetadataValue.exist('(rdf:RDF/rdf:Description/bl:isDescribedBy[@rdf:resource eq sql:variable("@MDARK")])') = 1;
Performs a scan of the selective index and then filters on the value of @MDARK, scans the base table and then joins the result set
Can anyone suggest what I'm doing wrong - I imagine it's something fairly basic in my knowledge of XQuery.
December 23, 2014 at 2:51 pm
Quick question, can you script out the table including all indexes and post it, curious on how that looks?
😎
December 29, 2014 at 3:55 am
Sorry for the delay - Christmas got in the way!
One (rather important) error. The XML column is NOT typed (there was some discussion during development and it was turned down eventually).
USE [MER]
GO
/****** Object: Table [dbo].[Metadata_Structural] Script Date: 29/12/2014 10:48:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Metadata_Structural](
[EntityId] [bigint] NOT NULL,
[MetadataValue] [xml] NOT NULL,
[MetadataId] [bigint] IDENTITY(1,1) NOT NULL,
[CreatedDT] [datetime2](2) NOT NULL CONSTRAINT [DF_Metadata_Structural_CreatedDT] DEFAULT (getdate()),
[EditorId] [smallint] NOT NULL,
[IsCurrent] [bit] NOT NULL CONSTRAINT [DF_Metadata_Structural_IsCurrent] DEFAULT ((1)),
CONSTRAINT [PK_Metadata_Structural] PRIMARY KEY CLUSTERED
(
[MetadataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA] TEXTIMAGE_ON [DATA]
GO
ALTER TABLE [dbo].[Metadata_Structural] WITH CHECK ADD CONSTRAINT [FK_Metadata_Structural_Editor] FOREIGN KEY([EditorId])
REFERENCES [dbo].[Editor] ([EditorId])
GO
ALTER TABLE [dbo].[Metadata_Structural] CHECK CONSTRAINT [FK_Metadata_Structural_Editor]
GO
ALTER TABLE [dbo].[Metadata_Structural] WITH CHECK ADD CONSTRAINT [FK_Metadata_Structural_Entity] FOREIGN KEY([EntityId])
REFERENCES [dbo].[Entity] ([EntityID])
GO
ALTER TABLE [dbo].[Metadata_Structural] CHECK CONSTRAINT [FK_Metadata_Structural_Entity]
GO
ALTER TABLE [dbo].[Metadata] CHECK CONSTRAINT [FK_Metadata_MetadataType]
GO
/****** Object: Index [FUQ_Metadata_Structural_Current] Script Date: 29/12/2014 10:50:01 ******/
CREATE UNIQUE NONCLUSTERED INDEX [FUQ_Metadata_Structural_Current] ON [dbo].[Metadata_Structural]
(
[EntityId] ASC
)
WHERE ([IsCurrent]=(1))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
GO
/****** Object: Index [UQ_Metadata_Structural_EntityId] Script Date: 29/12/2014 10:50:01 ******/
CREATE UNIQUE NONCLUSTERED INDEX [UQ_Metadata_Structural_EntityId] ON [dbo].[Metadata_Structural]
(
[EntityId] ASC,
[CreatedDT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
GO
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [test_mdark] Script Date: 29/12/2014 10:50:01 ******/
CREATE SELECTIVE XML INDEX [test_mdark] ON [dbo].[Metadata_Structural]
(
[MetadataValue]
)
WITH XMLNAMESPACES
(
'http://www.bl.uk/schemas/digitalobject/entities#' as bl,
'http://www.w3.org/1999/02/22-rdf-syntax-ns#' as rdf
)
FOR
(
[MDARKpath] = '(/rdf:RDF/rdf:Description/bl:isDescribedBy/@rdf:resource)',
[RightsMDpath] = '(/rdf:RDF/rdf:Description/bl:hasRightsMD/@rdf:resource)'
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
June 11, 2015 at 12:18 am
Looks like this answer on StackOverflow is relevant to you.
Sql Server: Selective XML Index not being efficiently used
Have a look at using a secondary selective xml index.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply