WayneS (1/12/2011)

---

Mark-101232 (1/12/2011)

---

Changing

value('.','varchar(max)'),1,1,'')

to

value('(./text())[1]','varchar(max)'),1,1,'')

appears to give a better query plan

Thanks for the information... I'll have to test this out.

Edit: WOW! What a difference! Identical queries, with just that one change. The way in the article gets:

(1000 row(s) affected)

Table '#TestData'. Scan count 1001, logical reads 2411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 234 ms, elapsed time = 3175 ms.

Your suggested change gets:

(1000 row(s) affected)

Table '#TestData'. Scan count 1001, logical reads 2411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 172 ms, elapsed time = 561 ms.

Looking at the execution plans (both queries run together), the method in the article uses 99% and your solution 1%.

Thanks for the tip!