Данных мало. всего 2700 строк в результирующей таблице.
Первый запрос - чисто посмотреть на время выполнения селекта из таблицы с одним столбцом, там в 4 раза больше строк.
Последний - селект из таблицы с четырьмя столбцами.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
---------------------------------------------------------------------------------------------------------------------------------------
print 'simple in -----------------------------------------------------'
set statistics time on
select adt, s_id, tem from tlog
where s_id in (5, 6, 7, 8) and adt > '2022-07-14 10:55:23.400'
order by 1 asc
set statistics time off
---------------------------------------------------------------------------------------------------------------------------------------
print 'joins -----------------------------------------------------'
set statistics time on
select distinct t.adt, t1.tem v1, t2.tem v2, t3.tem v3, t4.tem v4
from tlog t
left join tlog t1 on t.adt = t1.adt and t1.s_id = 5
left join tlog t2 on t.adt = t2.adt and t2.s_id = 6
left join tlog t3 on t.adt = t3.adt and t3.s_id = 7
left join tlog t4 on t.adt = t4.adt and t4.s_id = 8
where t.adt > '2022-07-14 10:55:23.400' and t.s_id in (5, 6, 7, 8)
order by 1
set statistics time off
---------------------------------------------------------------------------------------------------------------------------------------
print 'pivot with fake aggr -----------------------------------------------------'
set statistics time on
;with t as (
select adt, s_id, tem from tlog where s_id in (5, 6, 7, 8) and adt > '2022-07-14 10:55:23.400'
)
select adt adt, [5] v1, [6] v2, [7] v3, [8] v4 from t pivot (max(tem) for s_id in ([5], [6], [7], [8])) P
set statistics time off
---------------------------------------------------------------------------------------------------------------------------------------
print 'case with fake aggr -----------------------------------------------------'
set statistics time on
;with t as (
select adt
,case when s_id = 5 then tem end v1
,case when s_id = 6 then tem end v2
,case when s_id = 7 then tem end v3
,case when s_id = 8 then tem end v4
from tlog where adt > '2022-07-14 10:55:23.400' and s_id in (5, 6, 7, 8))
select adt, max(v1), max(v2), max(v3), max(v4) from t
group by adt
order by 1
set statistics time off
---------------------------------------------------------------------------------------------------------------------------------------
print 'optimal -----------------------------------------------------'
set statistics time on
select adt, v1, v2, v3, v4 from l_val4 where adt > '2022-07-14 10:55:23.400' and g_id = 1
set statistics time off
---------------------------------------------------------------------------------------------------------------------------------------
simple in -----------------------------------------------------
(11088 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 12 ms.
joins -----------------------------------------------------
(2772 row(s) affected)
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 33 ms.
pivot with fake aggr -----------------------------------------------------
(2772 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 11 ms.
case with fake aggr -----------------------------------------------------
(2772 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 12 ms.
optimal -----------------------------------------------------
(2773 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
Получается, нет ничего быстрее таблицы с количеством столбцов, соответствующим количеству замеров в группе. Но case с фиктивными аггрегациями тоже неплох. А pivot тоже подгружает ЦП, но не так сильно, как join.
