6XJMCbWz

1CREATE TABLE MyTable
2(
3Id        INT IDENTITY PRIMARY KEY,
4RangeFrom INT NOT NULL,
5RangeTo   INT NOT NULL,
6CHECK (RangeTo > RangeFrom),
7INDEX IX1 (RangeFrom,RangeTo),
8INDEX IX2 (RangeTo,RangeFrom)
9);
10
11WITH RandomNumbers
12     AS (SELECT TOP 10000000 ABS(CRYPT_GEN_RANDOM(4)%100000000) AS Num
13         FROM   sys.all_objects o1,
14                sys.all_objects o2,
15                sys.all_objects o3,
16                sys.all_objects o4)
17INSERT INTO MyTable
18            (RangeFrom,
19             RangeTo)
20SELECT Num,
21       Num + 1 + CRYPT_GEN_RANDOM(1)
22FROM   RandomNumbers
23	
24SELECT *
25FROM MyTable
26WHERE 50000000 BETWEEN RangeFrom AND RangeTo
27	
28DROP TABLE IF EXISTS dbo.MyTableCCI;
29
30CREATE TABLE dbo.MyTableCCI
31(
32Id        INT PRIMARY KEY,
33RangeFrom INT NOT NULL,
34RangeTo   INT NOT NULL,
35CHECK (RangeTo > RangeFrom),
36INDEX CCI CLUSTERED COLUMNSTORE
37);
38
39INSERT INTO dbo.MyTableCCI
40SELECT TOP (987654321) *
41FROM dbo.MyTable
42ORDER BY RangeFrom ASC
43OPTION (MAXDOP 1);
44	
45Table 'MyTableCCI'. Segment reads 1, segment skipped 9.
46	
47CREATE TABLE MyTable2
48(
49Id INT IDENTITY PRIMARY KEY,
50Range GEOMETRY NOT NULL,
51RangeFrom AS Range.STPointN(1).STX,
52RangeTo   AS Range.STPointN(2).STX,
53CHECK (Range.STNumPoints() = 2 AND Range.STPointN(1).STY = 0 AND Range.STPointN(2).STY = 0)
54);
55
56SET IDENTITY_INSERT MyTable2 ON
57
58INSERT INTO MyTable2
59            (Id,
60             Range)
61SELECT ID,
62       geometry::STLineFromText(CONCAT('LINESTRING(', RangeFrom, ' 0, ', RangeTo, ' 0)'), 0)
63FROM   MyTable
64
65SET IDENTITY_INSERT MyTable2 OFF 
66
67
68CREATE SPATIAL INDEX index_name   
69ON MyTable2 ( Range )  
70USING GEOMETRY_GRID  
71WITH (  
72BOUNDING_BOX = ( xmin=0, ymin=0, xmax=110000000, ymax=1 ),  
73GRIDS = (HIGH, HIGH, HIGH, HIGH),  
74CELLS_PER_OBJECT = 16);
75	
76SELECT Id,
77       RangeFrom,
78       RangeTo
79FROM   MyTable2
80WHERE  Range.STContains(geometry::STPointFromText ('POINT (50000000 0)', 0)) = 1
81	
82Table 'MyTable2'. Scan count 0, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
83Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
84Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
85Table 'extended_index_1797581442_384000'. Scan count 4, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
86	
87CREATE TABLE dbo.MyTable3
88(
89  Id        INT IDENTITY PRIMARY KEY,
90  RangeFrom INT NOT NULL,
91  RangeTo   INT NOT NULL,   
92  node  AS RangeTo - RangeTo % POWER(2, FLOOR(LOG((RangeFrom - 1) ^ RangeTo, 2))) PERSISTED NOT NULL,
93  CHECK (RangeTo > RangeFrom)
94);
95
96CREATE INDEX ix1 ON dbo.MyTable3 (node, RangeFrom) INCLUDE (RangeTo);
97CREATE INDEX ix2 ON dbo.MyTable3 (node, RangeTo) INCLUDE (RangeFrom);
98
99SET IDENTITY_INSERT MyTable3 ON
100
101INSERT INTO MyTable3
102            (Id,
103             RangeFrom,
104             RangeTo)
105SELECT Id,
106       RangeFrom,
107       RangeTo
108FROM   MyTable
109
110SET IDENTITY_INSERT MyTable3 OFF
111	
112DECLARE @value INT = 50000000;
113
114;WITH N AS
115(
116SELECT 30 AS Level, 
117       CASE WHEN @value > POWER(2,30) THEN POWER(2,30) END AS selected_left_node, 
118       CASE WHEN @value < POWER(2,30) THEN POWER(2,30) END AS selected_right_node, 
119       (SIGN(@value - POWER(2,30)) * POWER(2,29)) + POWER(2,30)  AS node
120UNION ALL
121SELECT N.Level-1,   
122       CASE WHEN @value > node THEN node END AS selected_left_node,  
123       CASE WHEN @value < node THEN node END AS selected_right_node,
124       (SIGN(@value - node) * POWER(2,N.Level-2)) + node  AS node
125FROM N 
126WHERE N.Level > 0
127)
128SELECT I.id, I.RangeFrom, I.RangeTo
129FROM dbo.MyTable3 AS I
130  JOIN N AS L
131    ON I.node = L.selected_left_node
132    AND I.RangeTo >= @value
133    AND L.selected_left_node IS NOT NULL
134UNION ALL
135SELECT I.id, I.RangeFrom, I.RangeTo
136FROM dbo.MyTable3 AS I
137  JOIN N AS R
138    ON I.node = R.selected_right_node
139    AND I.RangeFrom <= @value
140    AND R.selected_right_node IS NOT NULL
141UNION ALL
142SELECT I.id, I.RangeFrom, I.RangeTo
143FROM dbo.MyTable3 AS I
144WHERE node = @value;
145	
146Table 'MyTable3'. Scan count 24, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
147Table 'Worktable'. Scan count 4, logical reads 374, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
148	
149ALTER TABLE dbo.MyTableWithDiff ADD DiffOfColumns AS RangeTo-RangeFrom;
150
151CREATE INDEX IXDIFF ON dbo.MyTableWithDiff (DiffOfColumns,RangeFrom) INCLUDE (RangeTo);
152	
153WITH RecursiveCTE
154AS
155(
156    -- Anchor
157    SELECT TOP (1)
158        DiffOfColumns
159    FROM dbo.MyTableWithDiff AS T
160    ORDER BY
161        T.DiffOfColumns
162
163    UNION ALL
164
165    -- Recursive
166    SELECT R.DiffOfColumns
167    FROM
168    (
169        -- Number the rows
170        SELECT 
171            T.DiffOfColumns,
172            rn = ROW_NUMBER() OVER (
173                ORDER BY T.DiffOfColumns)
174        FROM dbo.MyTableWithDiff AS T
175        JOIN RecursiveCTE AS R
176            ON R.DiffOfColumns < T.DiffOfColumns
177    ) AS R
178    WHERE
179        -- Only the row that sorts lowest
180        R.rn = 1
181)
182SELECT ca.*
183FROM RecursiveCTE rcte
184CROSS APPLY (
185    SELECT mt.Id, mt.RangeFrom, mt.RangeTo
186    FROM dbo.MyTableWithDiff mt
187    WHERE mt.DiffOfColumns = rcte.DiffOfColumns
188    AND mt.RangeFrom >= 50000000 - rcte.DiffOfColumns AND mt.RangeFrom <= 50000000
189) ca
190OPTION (MAXRECURSION 0);
191	
192DROP TABLE IF EXISTS dbo.MyTableBigDiff;
193
194CREATE TABLE dbo.MyTableBigDiff
195(
196Id        INT IDENTITY PRIMARY KEY,
197RangeFrom INT NOT NULL,
198RangeTo   INT NOT NULL,
199CHECK (RangeTo > RangeFrom)
200);
201
202WITH RandomNumbers
203     AS (SELECT TOP 10000000 ABS(CRYPT_GEN_RANDOM(4)%100000000) AS Num
204         FROM   sys.all_objects o1,
205                sys.all_objects o2,
206                sys.all_objects o3,
207                sys.all_objects o4)
208INSERT INTO dbo.MyTableBigDiff
209            (RangeFrom,
210             RangeTo)
211SELECT Num,
212       Num + 1 + CRYPT_GEN_RANDOM(2) -- note the 2
213FROM   RandomNumbers;
214
215
216ALTER TABLE dbo.MyTableBigDiff ADD DiffOfColumns AS RangeTo-RangeFrom;
217
218CREATE INDEX IXDIFF ON dbo.MyTableBigDiff (DiffOfColumns,RangeFrom) INCLUDE (RangeTo);
219	
220ALTER TABLE dbo.MyTableBigDiff ADD DiffOfColumns_bucket256 AS CAST(CEILING((RangeTo-RangeFrom) / 256.) AS INT);
221
222CREATE INDEX [IXDIFF😎] ON dbo.MyTableBigDiff (DiffOfColumns_bucket256, RangeFrom) INCLUDE (RangeTo);
223	
224CROSS APPLY (
225    SELECT mt.Id, mt.RangeFrom, mt.RangeTo
226    FROM dbo.MyTableBigDiff mt
227    WHERE mt.DiffOfColumns_bucket256 = rcte.DiffOfColumns_bucket256
228    AND mt.RangeFrom >= 50000000 - (256 * rcte.DiffOfColumns_bucket256)
229    AND mt.RangeFrom <= 50000000
230    AND mt.RangeTo >= 50000000
231) ca
232	
233ALTER TABLE dbo.MyTable
234    ADD curtis_jackson 
235        AS CONVERT(BIT, CASE 
236                            WHEN RangeTo >= 50000000
237                            AND RangeFrom < 50000000
238                            THEN 1 
239                            ELSE 0 
240                        END);
241
242CREATE INDEX IX1_redo 
243    ON dbo.MyTable (curtis_jackson) 
244        INCLUDE (RangeFrom, RangeTo);
245	
246SELECT *
247FROM MyTable
248WHERE curtis_jackson = 1;
249	
250Table 'MyTable'. Scan count 1, logical reads 3...
251
252SQL Server Execution Times:
253  CPU time = 0 ms,  elapsed time = 0 ms.
254	
255EXEC sp_execute_external_script 
256@language = N'R', 
257@script = N'
258tweener = 50000000
259MO = data.frame(MartinIn)
260MartinOut <- subset(MO, RangeFrom <= tweener & RangeTo >= tweener, select = c("Id","RangeFrom","RangeTo"))
261', 
262@input_data_1_name = N'MartinIn',
263@input_data_1 = N'SELECT Id, RangeFrom, RangeTo FROM dbo.MyTable',
264@output_data_1_name = N'MartinOut',
265@parallel = 1
266WITH RESULT SETS ((ID INT, RangeFrom INT, RangeTo INT));
267	
268Table 'MyTable'. Scan count 1, logical reads 22400
269
270 SQL Server Execution Times:
271   CPU time = 3219 ms,  elapsed time = 5349 ms.
272	
273EXEC sp_execute_external_script 
274@language = N'Python', 
275@script = N'
276import pandas as pd
277MO = pd.DataFrame(MartinIn)
278tweener = 50000000
279MartinOut = MO[(MO.RangeFrom <= tweener) & (MO.RangeTo >= tweener)]
280', 
281@input_data_1_name = N'MartinIn',
282@input_data_1 = N'SELECT Id, RangeFrom, RangeTo FROM dbo.MyTable',
283@output_data_1_name = N'MartinOut',
284@parallel = 1
285WITH RESULT SETS ((ID INT, RangeFrom INT, RangeTo INT));
286	
287Table 'MyTable'. Scan count 1, logical reads 22400
288
289 SQL Server Execution Times:
290   CPU time = 3797 ms,  elapsed time = 10146 ms.