Interval Queries in SQL Server Part 3

[box type=”info”] Note that in the composite index on the view, I put the number n in the first place. This is what I am going to seek for in my queries. Let’s insert the data in the new table, and SQL Server will insert also the data in the view.[/box]

— SLOW! (17+ min!)
INSERT
INTO
dbo.IntervalsU
WITH(TABLOCK)
(id,
lower, upper)

SELECT id,
lower, upper

FROM
dbo.Stage;
GO

You can see from the comment that the indexed view slowed down the insert substantially. Still, the performance is acceptable. However, when I tried to insert the data in the table first, and then create the index on the view, the performance was not really acceptable. At least not for me – after two hours I lost my patience and cancelled the index creation. This is not a negligible issue; imagine what would happen if you would need to re-index the view in production.

Time for querying the table. I am showing here the query that selects the same rows from the middle of the data as the query in my previous articles and as the queries in Itzik’s article.

— query
SET
STATISTICS
IO
ON;
SET
STATISTICS
TIME
ON;
GO

— middle of data
DECLARE
@l
AS
INT
= 5000000,
@u
AS
INT
= 5000020;
DECLARE
@t
AS
TABLE(id
INT);
INSERT
INTO
@t(id)
SELECT id
FROM
dbo.Intervals_Unpacked
WHERE
n BETWEEN @l AND @u;
SELECT
DISTINCT id
FROM
@t;
— logical reads: 6 (5 + 1 from the table variable), CPU time: 0 ms
GO

I use a table variable to temporarily store the id column of the intervals that do include at least one point of the given interval, the interval I am checking for overlapping. The logic is simple – if an interval from the table includes at least one point that the given interval includes, than the two intervals overlap. I decided to store these id values in a table variable and then perform the DISTINCT operator on them in order to prevent SQL Server to expand the view to the base table and make the query less efficient. I could prevent this also with a query hint.

As you can see from the comment, the query is very efficient. You get the same efficiency also if you query the beginning or the end of the data. In addition, the query performance does not depend on the length distribution of the intervals. From the querying point of the view, this is practically an ideal solution. The queries are fast and simple, much simpler than the queries on the RI tree model that Itzik explained in his article.

Of course, you already know there is no free lunch. The drawback of this solution is the same as its advantage – the indexed view. Maintenance of an indexed view is a nightmare. In addition, there are many, many more rows in the unpacked form than in the original form. With this data, the original form has 10,000,000 rows, with maximal length of an interval 20, and average length around 10. I got more than 100,000,000 rows in the unpacked form. With longer intervals, there could potentially be tens of times more rows in the unpacked form.

Many of the problems could be mitigated if Microsoft would spend some time and improve the indexed views. However, like many times with Microsoft, it seems that they are simply forgetting on some features, and leave them half-baked. Then they are surprised when we don’t use these features in production. And instead of finalizing these features, they are simply adding new features in the next version, again half-baked. Like children that get bored with a toy very quickly and want a new toy much too soon.

Since SQL Server can’t help me here, I decided to create a table in the unpacked form. Re-indexing a table is not a problem, and therefore the maintenance is not such a nightmare. In addition, I can use data compression to save the space. However, SQL Server does not automatically update my table. In order to get it updated automatically, you need to create DML triggers on the original table. Below is the code that created and populates a table in the unpacked form.

CREATE
TABLE
dbo.Intervals_Unpacked_Table
(

id
INT
NOT
NULL,
n
INT
NOT
NULL
)
WITH (DATA_COMPRESSION = ROW);
GO

ALTER
TABLE
dbo.Intervals_Unpacked_Table

ADD
CONSTRAINT PK_Intervals_Unpacked_Table
PRIMARY
KEY(n, id);
GO

— 4 min with row compression
INSERT
INTO
dbo.Intervals_Unpacked_Table
WITH(TABLOCK)
(id, n)
SELECT id, n
FROM
dbo.Intervals

CROSS
APPLY dbo.GetNums(lower, upper);
GO

The population of this table was reasonably fast. Of course, there are still more than 100,000,000 rows in this table. What is the space consumption?

EXEC
sys.sp_spaceused
N’dbo.Intervals’,
TRUE;
EXEC
sys.sp_spaceused
N’dbo.Intervals_Unpacked’,
TRUE;
EXEC
sys.sp_spaceused
N’dbo.Intervals_Unpacked_Table’,
TRUE;
GO
— Original table reserverd:                 566,424 KB
— Indexed view reserved:                1,857,808 KB
— Table with row compressions reserved:        1,234,000 KB

From the space consumption point of view, this is not problematic. The unpacked table occupies a bit more than twice as much space as the original table. However, remember that the original table does not have any additional attribute besides the interval boundaries and the key. In production, a real table would have many more columns, and thus much longer rows, and the space consumption would be probably even bigger that the space consumption of the unpacked table. I also tried to use the page compression; however, I did not get much better compression rate with this data set and decided to stick with the row compression. So let’s query the unpacked table!

— query
SET
STATISTICS
IO
ON;
SET
STATISTICS
TIME
ON;
GO

— middle of data
DECLARE
@l
AS
INT
= 5000000,
@u
AS
INT
= 5000020;
SELECT
DISTINCT
id
FROM
dbo.Intervals_Unpacked_Table
WHERE
n BETWEEN @l
AND
@u;
GO
— logical reads: 7, CPU time: 0 ms

The query is very efficient again, and the performance also does not depend on the interval length distribution. However, with longer intervals, you could get many more rows, and still have issues with the maintenance. Still, I like this solution quite a lot, because the queries are simple, efficient, and have stable performance regarding the data distribution.