Monday, January 03, 2005

OpenXML and index scan issue

Faced a peculiar issue with OpenXML, when joining with a table with an OpenXML data, it seems the index being hit is erratic.

CREATE TABLE OpenXMLTest3
(
Col1 INT NOT NULL primary key,
Col2 CHAR(1) NOT NULL,
Col3 CHAR(10)
)
GO

CREATE INDEX IDX_OpenXMLTest3 ON OpenXMLTest3
( Col2, Col3 ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

Execution plan for the following code gives a index scan on IDX_OpenXMLTest3, but if we load a lot of data into this table, and get the execution plan, it shows a clustered index scan on primary key index. ???? Yes on the "primary key index" ????

DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDocOUTPUT, '?'

SELECT * FROM OpenXMLTest3 A,
OPENXML (@hDoc, 'ROOT/OpenXMLTest3',1)
WITH (COL2 CHAR(1), COL3 CHAR(10)) B
WHERE A.COL2 = B.COL2 AND
A.COL3 = B.COL3

When we dump the xml data into a table variable and join with that like one below, it was found to give an index seek on IDX_OpenXMLTest3. That is what we used to fix this isse as the table we are joining is a very big invoice table and OpenXML failed hands down.

DECLARE @Temp table (
Col2 CHAR(1) NOT NULL,
Col3 CHAR(10)
)

SELECT * FROM OpenXMLTest3 A, @Temp B
WHERE A.COL2 = B.COL2 AND
A.COL3 = B.COL3

No comments: