· 7 years ago · Sep 15, 2018, 08:32 PM
1Inefficient SQL query with DATETIME calculations. How to optimize?
2CREATE TABLE production_plan (
3 order_id nvarchar(50) NOT NULL,
4 production_line uniqueidentifier NULL,
5 prod_start DATETIME NULL,
6 prod_end DATETIME NULL
7);
8
9-- About 31 000 rows inserted, ordered by order_id.
10...
11
12-- Clusteded index on ind_order_id.
13CREATE CLUSTERED INDEX ind_order_id
14ON production_plan (order_id ASC);
15
16-- Non-clustered indices on the other columns.
17CREATE INDEX ind_times
18ON production_plan (production_line ASC, prod_start ASC, prod_end ASC);
19
20------------------------------------------------------
21
22-- There is actually more temperatures for one time (i.e. more
23-- sensors). The UTC is the real time of the row insertion, hence
24-- the primary key.
25CREATE TABLE temperatures (
26 UTC datetime PRIMARY KEY NOT NULL,
27 production_line uniqueidentifier NULL,
28 temperature_1 float NULL
29);
30
31-- About 91 000 rows inserted ordered by UTC.
32...
33
34-- Clusteded index on UTC is created automatically
35-- because of the PRIMARY KEY. Indices on temperature(s)
36-- do not make sense.
37
38-- Non-clustered index for production_line
39CREATE INDEX ind_pl
40ON temperatures (production_line ASC);
41
42-- The tables were created, records inserted, and the indices
43-- created for less than 1 second (for the sample on my computer).
44
45-- About 45 000 rows in about 24 seconds when no indices were used.
46-- The same took less than one second with the indices (for my data
47-- and my computer).
48SELECT pp.order_id, -- not related to the problem
49 pp.prod_start, -- UTC of the start of production
50 pp.prod_end, -- UTC of the end of production
51 t.UTC, -- UTC of the temperature measurement
52 t.temperature_1 -- the measured temperature
53 INTO result_table02
54 FROM production_plan AS pp
55 JOIN temperatures AS t
56 ON pp.production_line = t.production_line
57 AND t.UTC BETWEEN pp.prod_start
58 AND pp.prod_end
59 ORDER BY t.UTC;
60
61-- About 46 000 rows in about 9 minutes without indices.
62-- It took about the same also with indices
63-- (8:50 instead of 9:00 or so).
64DECLARE @offset_start INT;
65SET @offset_start = -60 -- one minute = one sample before
66
67DECLARE @offset_end INT;
68SET @offset_end = +60 -- one minute = one sample after
69
70SELECT pp.order_id, -- not related to the problem
71 pp.prod_start, -- UTC of the start of production
72 pp.prod_end, -- UTC of the end of production
73 t.UTC, -- UTC of the temperature measurement
74 t.temperature_1 -- the measured temperature
75 INTO result_table03
76 FROM production_plan AS pp
77 JOIN temperatures AS t
78 ON pp.production_line = t.production_line
79 AND t.UTC BETWEEN DATEADD(second, @offset_start, pp.prod_start)
80 AND DATEADD(second, @offset_end, pp.prod_end)
81 ORDER BY t.UTC;
82
83CREATE INDEX ind_pl
84 ON temperatures (production_line ASC, UTC);
85
86SELECT pp.order_id, -- not related to the problem
87 pp.prod_start, -- UTC of the start of production
88 pp.prod_end, -- UTC of the end of production
89 t.UTC, -- UTC of the temperature measurement
90 t.temperature_1 -- the measured temperature
91 INTO result_table02
92 FROM production_plan AS pp
93 CROSS APPLY
94 (
95 SELECT t1.utc, t1.temperature_1
96 FROM temperatures AS t1
97 WHERE t1.production_line = pp.production_line
98 AND t1.UTC BETWEEN DATEADD(second, @offset_start, pp.prod_start)
99 AND DATEADD(second, @offset_end, pp.prod_end)
100 ) t
101 ORDER BY t.UTC;
102
103-- UTC range expanded by the offsets -- temporary table used.
104-- (Much better -- less than one second.)
105
106DECLARE @offset_start INT;
107SET @offset_start = -60 -- one minute = one sample before
108
109DECLARE @offset_end INT;
110SET @offset_end = +60 -- one minute = one sample after
111
112-- Temporary table with the production_plan UTC range expanded.
113SELECT production_line,
114 order_id,
115 prod_start,
116 prod_end,
117 DATEADD(second, @offset_start, prod_start) AS start,
118 DATEADD(second, @offset_end, prod_end) AS bend
119 INTO #pp
120 FROM production_plan;
121
122CREATE INDEX ind_UTC
123 ON #pp (production_line ASC, start ASC, bend ASC);
124
125SELECT order_id,
126 prod_start,
127 prod_end,
128 UTC,
129 temperature_1
130 INTO result_table06
131 FROM #pp JOIN temperatures AS t
132 ON #pp.production_line = t.production_line
133 AND UTC BETWEEN #pp.start AND #pp.bend
134 ORDER BY UTC;
135
136DROP TABLE #pp;
137
138CREATE CLUSTERED INDEX ind_UTC
139 ON result_table06 (UTC ASC);
140
141ALTER TABLE production_plan ADD
142 offset_start int NOT NULL CONSTRAINT DF__production_plan__offset_start DEFAULT 0,
143 offset_end int NOT NULL CONSTRAINT DF__production_plan__offset_end DEFAULT 0,
144 prod_start_UTC as CAST(DATEADD(second,offset_start,prod_start) as DATETIME) PERSISTED NOT NULL ,
145 prod_end_UTC as CAST(DATEADD(second,offset_end,prod_end) as DATETIME) PERSISTED NOT NULL
146
147-- or just
148--ALTER TABLE production_plan ADD
149-- prod_start_UTC as CAST(DATEADD(second,-60,prod_start) as DATETIME) PERSISTED NOT NULL ,
150-- prod_end_UTC as CAST(DATEADD(second,60,prod_end) as DATETIME) PERSISTED NOT NULL
151
152IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[temperatures]') AND name = N'ind_pl')
153 DROP INDEX [ind_pl] ON [dbo].[temperatures] WITH ( ONLINE = OFF )
154
155CREATE INDEX ind_times_UTC
156ON production_plan (production_line ASC, prod_start_UTC ASC, prod_end_UTC ASC);
157
158SELECT pp.order_id, -- not related to the problem
159 pp.prod_start, -- UTC of the start of production
160 pp.prod_end, -- UTC of the end of production
161 t.UTC, -- UTC of the temperature measurement
162 t.temperature_1 -- the measured temperature
163 INTO result_table05
164 FROM production_plan AS pp
165 JOIN temperatures AS t
166 ON pp.production_line = t.production_line
167 AND t.UTC BETWEEN pp.prod_start_UTC
168 AND pp.prod_end_UTC
169ORDER BY t.UTC;
170
171select getdate()+1.000/(24.00*60.00)
172
173select getdate()+0.000694444