title: Xml query with cross join on large dataset. how to increase performance
body: <p>I have the following query which takes about 50 seconds for 100 records, however using a count i have determined that the results set will be 100 000 items 
  (this is for a report) so i am looking for ways to increase performance.</p>
  
  <pre><code>SELECT TOP 200
  OL.*,
  LI.LineItem.value('Field[@name="storename"][1]/@value', 'varchar(MAX)') AS storename,
  LI.LineItem.value('Field[@name="pwnumber"][1]/@value', 'varchar(MAX)') AS pwnumber,
  LI.LineItem.value('Field[@name="department"][1]/@value', 'varchar(MAX)') AS department,
  LI.LineItem.value('Field[@name="item"][1]/@value', 'varchar(MAX)') AS item,
  LI.LineItem.value('Field[@name="barcode"][1]/@value', 'varchar(MAX)')AS barcode,
  LI.LineItem.value('Field[@name="oldprice"][1]/@value', 'varchar(MAX)')AS oldprice,
  LI.LineItem.value('Field[@name="newprice"][1]/@value', 'varchar(MAX)')AS newprice,
  LI.LineItem.value('Field[@name="goodsgroup"][1]/@value', 'varchar(MAX)')AS goodsgroup,
  LI.LineItem.value('Field[@name="startdate"][1]/@value', 'varchar(MAX)')AS startdate,
  LI.LineItem.value('Field[@name="prodgroup"][1]/@value', 'varchar(MAX)')AS prodgroup,
  LI.LineItem.value('Field[@name="season"][1]/@value', 'varchar(MAX)')AS season,
  LI.LineItem.value('Field[@name="pkquantity"][1]/@value', 'varchar(MAX)')AS pkquantity,
  LI.LineItem.value('Field[@name="recordtype"][1]/@value', 'varchar(MAX)')AS recordtype
  FROM [lines] OL
  LEFT JOIN [Clineextension] OLE ON OL.OrderLineId = OLE.OrderLineId
  CROSS APPLY OLE.LineItems.nodes('/LineItems/LineItem') as LI(LineItem)
  WHERE Customer = 1302
  AND StatusId &gt; 1
  </code></pre>
  
  <p>i think that especially the select statements can be improved but i am not sure how.</p>
  
  <p>any ideas?
  any help is greatly appreciated.</p>
  
  <p>other info
  sql server 2005
  all rights
  xml field vary per customer</p>