Sql-server – search xml column inside large table (7 million rows)

sql serverxml

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.

Best Answer

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.

Related Solutions

SQL Server – Are Multicolumn Histograms Possible?

Are multicolumn histograms possible?

Not true multi-dimensional histograms, no.

Is this artificial and inelegant solution the only available option to achieve accurate estimations when dealing with filtering by two or more not independent columns?

SQL Server does support "multi-column" statistics, but they only capture average density (correlation) information in addition to a histogram on the first named column. They are only useful for equality comparisons.

The average density information does not capture any detail, so you will get the same selectivity for any pair of values on a two-column statistics object. In some cases, multi-column statistics can be good enough, and better than nothing. Multi-column statistics are automatically built on multi-column indexes.

Depending on SQL Server version, you may also be able to make use of filtered indexes and filtered statistics:

-- Filtered statistics example
CREATE STATISTICS stats_StatsTest_col2_col1_eq_1
ON dbo.StatsTest (col2)
WHERE col1 = 1;

CREATE STATISTICS stats_StatsTest_col2_col1_eq_2
ON dbo.StatsTest (col2)
WHERE col1 = 2;

Or you could build an indexed view (which can support indexes and statistics of its own). Indexed views are the mechanism behind the DATE_CORRELATION_OPTIMIZATION database setting, a little-used feature for inter-table correlations, but which applies to the spirit of the question.

Is the computed column and the filter by the computed column strictly necessary for obtaining actual precision?

It is not the only method. In addition to the things already mentioned, you could also specify the exact textual definition of the computed column and the optimizer will generally match it to statistics on the computed column.

There are also trace flags that change the assumptions made about multi-column correlations. Also, the default correlation assumption in SQL Server 2014 (with the new cardinality estimator enabled) is changed from Independence to Exponential Backoff (more details here and here). Ultimately, this is just a different assumption though. It will be better in many cases, and worse in others.

Exact precision in cardinality estimation is not always needed to obtain a good execution plan. There is always a trade-off between generating a plan that can be reused for different parameter values, and a plan that is optimal for a specific execution, but not reused.

Sql-server – Perform regex search queries of a text column in table of 100+ million using regex

I recently did an SSIS package that went though a table with over 4 billion records, the first step in the package was a query that extracted about 50 million records. The query just took a few seconds. Granted, my query was able to use an index, but it did have multiple conditions, and had to skip through the index to get at a subset of all the records and then test them with another couple conditions. And the return set was in the same order of magnitude as one of your entire tables. So I don't think you will have to do much to make this happen.

Sounds like you may not know how to use the LIKE operator. You can find details for that here. That will let you use some regular expressions to match against your text.

I'd recommend that you load the 2-grams into a table and give it a try. My guess is that a simple query with a LIKE condition will do the job for you.

Best Answer

Related Solutions

SQL Server – Are Multicolumn Histograms Possible?

Sql-server – Perform regex search queries of a text column in table of 100+ million using regex

Related Question