CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlquery-optimization
Sridhar
Sridhar

Reputation: 503

table Valued Function Query Optimize

Working on table Valued Function Query. Which is eating about 70% of the whole query execution time. Need some help in fine tuning it.

INSERT INTO #XMLTAB
SELECT ID,CAST(tab.tabxml as xml).value('(/Root/Element)[1]', 'varchar(100)')
FROM tab 
WHERE TabScore= 36

Ref :

Thanks In Advance.

Upvotes: 1

Views: 482

Answers (1)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

I see you are using SQL Server XML data type. Do you know you can index it?

Try with this:

CREATE PRIMARY XML INDEX PXML_tab_tabxml
ON tab (tabxml)

CREATE XML INDEX IXML_tab_tabxml
ON tab (tabxml)
USING XML INDEX PXML_tab_tabxml FOR PATH;

First statement will shred your xml to internal structure (index rows) and enable SQL Server more effectively query such disassembled XML. This is known as a PRIMARY XML INDEX.

However, if you are querying your XML from some table using a value() method, consider running the second statement which will further speed up such operations. This is known as a SECONDARY XML INDEX and can be of three different types.

Be aware that creating such XML indexes on large tables where XML already exists can take time.

Upvotes: 1

Related Questions