3

Database design:

Table Name : Plays ( 6 million records)

Columns: Play id, col1, col2......col7, PlayFieldValues

PlayFieldValues xml column. sample xml stored in this column

<PlayAttributes>
  <PlayFields>
    <PlayField ID="Play.GenericInt1" >25</PlayField>
    <PlayField ID="X" TS="">0</PlayField>
    <PlayField ID="Y" >OFF</PlayField>
  </PlayFields>
</PlayAttributes>

Each xml has more than 25 elements

Using following query I am returning all Playid's from plays table having "Play.GenericInt1" attribute value 25 (​we can add more conditions in where clause. This is just an example.)

Select 

playId
FROM
(
SELECT playId, x.v.value('@ID[1]', 'VARCHAR(100)') AS playfield ,
x.v.value('.', 'VARCHAR(100)') AS playfieldvalue
from plays
cross apply PlayFieldValues.nodes('/PlayAttributes/PlayFields/PlayField') x(v)
)T

where
T.playfield in('Play.GenericInt1') and T.playfieldvalue=25

Problem: This query is working great on plays table with 1k or 2k rows, but it is not scaling when running on 5 million rows table. execution time is crossing 20 min. Please advice what types of methods and procedures in sql I can use to search the xml column. I tried XML indexes but it costing us lot of space and maintenance problems.

  • 5
    What version of SQL Server are you using? Have you tried selective XML indexes? Have you considered materializing the pieces you're after at insert/update time, with a trigger, or with a background job? – Aaron Bertrand May 14 '15 at 0:18
  • sql server 2008 and sql server 2012. What is materializing the pieces means? please explain. no I havent tried selective indexes. I am researching now. Thanks. – ess May 14 '15 at 0:58
  • Materializing means storing a copy somewhere. So instead of extracting playfield from the XML at query time, you do that at insert/update time, and store the result somewhere. – Aaron Bertrand May 14 '15 at 1:05
  • 5
    You can do 6 million rows once each, and perform that as a background task and in chunks, or you can do the above for all 6 million rows every time you run a query. – Aaron Bertrand May 14 '15 at 1:16
  • 2
    Yes, it's a trade-off. – Aaron Bertrand May 14 '15 at 1:28
1

You may improve your performance by using xml exist, instead of extracting the values.

SELECT playId
from plays
where PlayFieldValues.exist('/PlayAttributes/PlayFields/PlayField[@ID = "Play.GenericInt1" and text()="25"]')=1

...but without any xml indexes this is still going to involve a table scan.

  • Thanks Mister, Appreciate your input. I am researching about xml indexes. – ess May 15 '15 at 18:01
  • I would spend more time investigating materialising the values, xml indexes are not that great actually. – Mister Magoo May 15 '15 at 18:05
  • materialising the values is good idea for new rows, but we are researching what to do with existing 6 million rows. any ideas? – ess May 15 '15 at 18:18
  • Create Views over XML Columns, do you think I can try this? Please advice – ess May 15 '15 at 18:58
  • I don't believe you can create an indexed view on an XML column, and just having a view defined would not impact performance for this use case in any way. – Mister Magoo May 15 '15 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.