· 8 years ago · Aug 16, 2018, 01:14 PM
1Efficient way to look up sequential values
2CREATE TABLE Products
3 (
4 ProductId int not null
5 constraint PK_Products
6 primary key
7 ,Name varchar(100) not null
8 )
9
10CREATE TABLE Segments
11 (
12 ProductId int not null
13 constraint FK_Segments__Products
14 foreign key references Products (ProductId)
15 ,OrderBy int not null
16 ,Value float not null
17 ,constraint PK_Segments
18 primary key (ProductId, OrderBy)
19 )
20
21CREATE TABLE #MatchThis
22 (
23 Position int not null
24 ,Value float not null
25 )
26
27First item 0 <value 1>
28Second item 1 <value 2>
29Third item 2 <value 3>
30...
31Nth item N-1 <value N>
32
33DECLARE
34 @ItemCount int
35 ,@FirstValue float
36
37-- How many items to be matched ("N", above)
38SELECT @ItemCount = count(*)
39 from #MatchThis
40
41-- The value of the first item in the search set
42SELECT @FirstValue = Value
43 from #MatchThis
44 where Position = 0
45
46SELECT
47 pr.Name
48 ,fv.OrderBy -- Required by the Group By, but otherwise can be ignored
49 from #MatchThis mt
50 cross join (-- All Segments that match the first value in the set
51 select ProductId, OrderBy
52 from Segment
53 where Value = @FirstValue) fv
54 inner join Product pr -- Just to get the Product name
55 on pr.ProductId = fv.ProductId
56 inner join Segment se
57 on se.ProductId = fv.ProductId
58 and se.OrderBy = fv.OrderBy + mt.Position -- Lines them up based on the first value
59 and se.Value = mt.Value -- No join if the values don't match
60 group by
61 pr.Name
62 ,fv.OrderBy
63 having count(*) = @ItemCount -- Only include if as many segments pulled for this product/segment.OrderBy as are required
64
65ProductId, DelimitedList
661 ,323.113,5423.231,873.42,422.64,763.1,
67
68WHERE DelimitedList LIKE '%,323.113,5423.231,873.42,%'
69
70/*Set up test tables*/
71CREATE TABLE Products(
72ProductId int primary key)
73
74
75CREATE TABLE ProductSegments(
76ProductId int REFERENCES Products,
77Sort int,
78Value decimal(10,3)
79Primary key (ProductId,Sort))
80
81CREATE NONCLUSTERED INDEX ix ON ProductSegments(ProductId,Value)
82
83
84CREATE TABLE ProductSegmentsDenormalized
85(
86ProductId int REFERENCES Products,
87DelimitedList varchar(max)
88)
89
90/*Insert some initial data to Products...*/
91INSERT INTO Products VALUES (1),(2),(3)
92
93/*... and for ProductSegments*/
94;WITH numbers(N)
95 AS (SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
96 FROM master..spt_values v1,
97 master..spt_values v2)
98INSERT INTO ProductSegments
99 (ProductId,
100 Sort,
101 Value)
102SELECT ProductId AS Product,
103 n1.N Sort,
104 ( ABS(CHECKSUM(NEWID()))% 1000000000 ) / 1000.00
105FROM numbers n1,
106 Products
107
108
109
110/*Set up table for search data*/
111
112DECLARE @SearchValues TABLE
113(
114Sequence int primary key,
115Value decimal(10,3)
116)
117INSERT INTO @SearchValues
118VALUES (1,323.113),(2,5423.231),(3,873.420),(4,422.640),(5,763.100)
119
120
121
122/*Fiddle the test data so we have some guaranteed matches*/
123UPDATE ps
124SET ps.Value = sv.Value
125FROM ProductSegments ps
126JOIN @SearchValues sv ON ProductId = 1 AND Sort = 100 + Sequence
127
128UPDATE ps
129SET ps.Value = sv.Value
130FROM ProductSegments ps
131JOIN @SearchValues sv ON ProductId = 3 AND Sort = 987 + Sequence
132
133
134/*Create the denormalised data*/
135INSERT INTO ProductSegmentsDenormalized
136SELECT ProductId, '|' + DelimitedList
137FROM Products p
138CROSS APPLY ( SELECT CAST(Value as varchar) + '|'
139 FROM ProductSegments ps
140 WHERE ps.ProductId = p.ProductId
141 ORDER BY Sort
142 FOR XML PATH('') ) D ( DelimitedList )
143
144
145
146/*Do the search*/
147SELECT ProductId
148FROM ProductSegmentsDenormalized psd
149WHERE psd.ProductId IN (SELECT p.ProductId
150 FROM Products p
151 WHERE NOT EXISTS (SELECT *
152 FROM @SearchValues sv
153 WHERE NOT EXISTS
154 (SELECT *
155 FROM ProductSegments ps
156 WHERE ps.ProductId = p.ProductId
157 AND sv.Value = ps.Value)))
158AND DelimitedList LIKE '%|' + (SELECT CAST(Value AS VARCHAR) + '|'
159 FROM @SearchValues sv
160 ORDER BY Sequence
161 FOR XML PATH('')) + '%'
162
16334 57 67 34
164
16534 57 67 34
16657 67 34
16767 34
16834