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.

7
  • 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? Commented May 14, 2015 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
    Commented May 14, 2015 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. Commented May 14, 2015 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. Commented May 14, 2015 at 1:16
  • 2
    Yes, it's a trade-off. Commented May 14, 2015 at 1:28

1 Answer 1

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.

6
  • Thanks Mister, Appreciate your input. I am researching about xml indexes.
    – ess
    Commented May 15, 2015 at 18:01
  • I would spend more time investigating materialising the values, xml indexes are not that great actually. Commented May 15, 2015 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
    Commented May 15, 2015 at 18:18
  • Create Views over XML Columns, do you think I can try this? Please advice
    – ess
    Commented May 15, 2015 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. Commented May 15, 2015 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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