table Valued Function Query Optimize

Question

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 :

#XMLTAB is Temp table.
Tab is table name in database.
tabxml Column holds xml file
tabscore is another column.

Thanks In Advance.

Can you post query execution plan ?

Ravi Singh
– Ravi Singh

2013-07-24 08:20:42 +00:00
Commented Jul 24, 2013 at 8:20 — Ravi Singh
– Ravi Singh, Commented Jul 24, 2013 at 8:20

OzrenTkalcecKrznaric · Accepted Answer · 2013-07-24 08:36:44Z

1

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.

answered Jul 24, 2013 at 8:36

OzrenTkalcecKrznaric

5,6544 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sridhar Over a year ago

tabxml is ntext, which holds xml data

OzrenTkalcecKrznaric Over a year ago

Well, in that case you can't do much. First solution is fixing your schema and altering ntext to xml, and you SHOULD do it if you can. The other possibility is adding calculated persisted field to that table, something like tabxmlxml = CAST(tab.tabxml as xml) PERSISTED, indexing that, and querying that instead of ntext. There are really no other options to speed this up.

Collectives™ on Stack Overflow

table Valued Function Query Optimize

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related