Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 история  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 53624
Нужна идея, как правильно написать

есть линкед сервер
через него читаю
таблица данных и таблица истории (история - что-то вроде EAV)
Надо по каждой строке данных подтянуть историю - значение параметра на начало периода и на конец периода и дату изменения этих параметров на начало и конец периода

Собственно, данные я получил
Но напрягает читать эти данные тучей скалярных подзапросов, да еще по линкедсерверу
при том, что основной запрос будет из 20 тысяч строк - т.е. 20000 * 16 (4 параметра * 4 значения) - и получаем 320 тысяч скалярных подзапросов через линкед сервер

что весьма напряжно

Сама таблица истории пока пустая
но в ней будут десятки миллионов строк уже через полгода
Индексы в ней есть, и пообещали добавить любые, какие будут нужны
+

declare
    @p_date_from datetime = {ts '2018-12-17 11:00:00'},
    @p_date_to   datetime = {ts '2018-12-17 17:00:00'};

with
data as
(
select 'CI1189072' id  union all
select 'CI1190919' id  union all
select 'CI1191312' id  union all
select 'CI1191970' id  union all
select 'CI1192025' id  union all
select 'CI1192060' id  union all
select 'CI1193174' id  union all
select 'CI1197787' id  union all
select 'CI1197822' id  union all
select 'CI1198778' id),
hist as
(
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 Ч/Б:' field_name, '21770' new_simple_val union all
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 Ч/Б:' field_name, '66601' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1189072' id, { ts '2018-12-17 12:30:03'} created_time, N'Счетчик А4 Ч/Б:' field_name, '1268820' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '17375' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 Ч/Б:' field_name, '14303' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '42852' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 Ч/Б:' field_name, '3068' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1198778' id, { ts '2018-12-17 03:41:38'} created_time, N'Счетчик А4 Ч/Б:' field_name, '9254' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 Ч/Б:' field_name, '8870' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1193174' id, { ts '2018-12-17 04:13:26'} created_time, N'Счетчик А4 ЦВ:' field_name, '1' new_simple_val
)
select data.id,
       (
        select top 1
               new_simple_val
          from hist
         where hist.FIELD_NAME = N'Счетчик А4 Ч/Б:'
           and hist.id = data.id
           and hist.created_time <= @p_date_from
          order by hist.created_time desc
       )     [Счетчик А4 Ч/Б: на начало периода отбора],
       (
        select top 1
               new_simple_val
          from hist
         where hist.FIELD_NAME = N'Счетчик А4 Ч/Б:'
           and hist.id = data.id
           and hist.created_time <= @p_date_to
          order by hist.created_time desc
       )     [Счетчик А4 Ч/Б: на конец периода отбора]

  from data
idСчетчик А4 Ч/Б: на начало периода отбораСчетчик А4 Ч/Б: на конец периода отбора
CI1189072NULL1268820
CI1190919NULL17375
CI11913126660166601
CI11919702177021770
CI1192025NULL14303
CI1192060NULL42852
CI1193174NULLNULL
CI119778788708870
CI119782230683068
CI119877892549254
17 дек 18, 19:54    [21766362]     Ответить | Цитировать Сообщить модератору
 Re: история  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 53624
сейчас проверил - на чтение одного скаляра на 11 тысяч строк ушло 43 сек
т.е. 43*16 сек - мне явно не подойдет
17 дек 18, 20:01    [21766365]     Ответить | Цитировать Сообщить модератору
 Re: история  [new]
invm
Member

Откуда: Москва
Сообщений: 9112
andreymx
Но напрягает читать эти данные тучей скалярных подзапросов, да еще по линкедсерверу
при том, что основной запрос будет из 20 тысяч строк - т.е. 20000 * 16 (4 параметра * 4 значения)
Ну так это особенность EAV. Либо так, либо поддерживайте на стороне линкеда транспонированный EAV с нужной гранулярностью по дате.
Из 4*4 можно легко получить 4*2, перенеся коррелированные подзапросы из select в outer apply и объединив в одном получение значения и даты.
Индекс для этого хозяйства нужен (FIELD_NAME, id, created_time) include (new_simple_val). FIELD_NAME и id можно в другом порядке.
17 дек 18, 20:59    [21766400]     Ответить | Цитировать Сообщить модератору
 Re: история  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30697
andreymx
Надо по каждой строке данных подтянуть историю - значение параметра на начало периода и на конец периода и дату изменения этих параметров на начало и конец периода

Собственно, данные я получил
Но напрягает читать эти данные тучей скалярных подзапросов, да еще по линкедсерверу
Не пойму, а что мешает получить эти данные одним запросом, а не кучей?
17 дек 18, 21:15    [21766423]     Ответить | Цитировать Сообщить модератору
 Re: история  [new]
invm
Member

Откуда: Москва
Сообщений: 9112
Или можете попробовать примерно так:
+
declare
    @p_date_from datetime = {ts '2018-12-17 11:00:00'},
    @p_date_to   datetime = {ts '2018-12-17 17:00:00'};

with
data as
(
select 'CI1189072' id  union all
select 'CI1190919' id  union all
select 'CI1191312' id  union all
select 'CI1191970' id  union all
select 'CI1192025' id  union all
select 'CI1192060' id  union all
select 'CI1193174' id  union all
select 'CI1197787' id  union all
select 'CI1197822' id  union all
select 'CI1198778' id),
hist as
(
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 Ч/Б:' field_name, '21770' new_simple_val union all
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 Ч/Б:' field_name, '66601' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1189072' id, { ts '2018-12-17 12:30:03'} created_time, N'Счетчик А4 Ч/Б:' field_name, '1268820' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '17375' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 Ч/Б:' field_name, '14303' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '42852' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 Ч/Б:' field_name, '3068' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1198778' id, { ts '2018-12-17 03:41:38'} created_time, N'Счетчик А4 Ч/Б:' field_name, '9254' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 Ч/Б:' field_name, '8870' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1193174' id, { ts '2018-12-17 04:13:26'} created_time, N'Счетчик А4 ЦВ:' field_name, '1' new_simple_val
)
select
 d.id, t.*
from
 data d outer apply
 (
  select
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.created_time end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.new_simple_val end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.created_time end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.new_simple_val end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.created_time end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.new_simple_val end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.created_time end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.new_simple_val end)
  from
   (
    select
     h.field_name, h.created_time, h.new_simple_val,
     row_number() over (partition by h.field_name order by h.created_time) as rn1,
     row_number() over (partition by h.field_name order by h.created_time desc) as rn2
    from
     (values (N'Счетчик А4 ЦВ:'), (N'Счетчик А4 Ч/Б:')) a(field_name) join
     hist h on h.field_name = a.field_name and h.id = d.id
    where
     h.created_time between @p_date_from and @p_date_to 
   ) x
  where
   x.rn1 = 1 or x.rn2 = 1
 ) t(f1, f2, f3, f4, f5, f6, f7, f8);
17 дек 18, 21:22    [21766428]     Ответить | Цитировать Сообщить модератору
 Re: история  [new]
invm
Member

Откуда: Москва
Сообщений: 9112
Можно, как предлагает alexeyvg, и одним запросом:
+
declare
    @p_date_from datetime = {ts '2018-12-17 11:00:00'},
    @p_date_to   datetime = {ts '2018-12-17 17:00:00'};

with
data as
(
select 'CI1189072' id  union all
select 'CI1190919' id  union all
select 'CI1191312' id  union all
select 'CI1191970' id  union all
select 'CI1192025' id  union all
select 'CI1192060' id  union all
select 'CI1193174' id  union all
select 'CI1197787' id  union all
select 'CI1197822' id  union all
select 'CI1198778' id),
hist as
(
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 Ч/Б:' field_name, '21770' new_simple_val union all
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 Ч/Б:' field_name, '66601' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1189072' id, { ts '2018-12-17 12:30:03'} created_time, N'Счетчик А4 Ч/Б:' field_name, '1268820' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '17375' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 Ч/Б:' field_name, '14303' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '42852' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 Ч/Б:' field_name, '3068' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1198778' id, { ts '2018-12-17 03:41:38'} created_time, N'Счетчик А4 Ч/Б:' field_name, '9254' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 Ч/Б:' field_name, '8870' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1193174' id, { ts '2018-12-17 04:13:26'} created_time, N'Счетчик А4 ЦВ:' field_name, '1' new_simple_val
)
select
 d.id, t.f1, t.f2, t.f3, t.f4, t.f5, t.f6, t.f7, t.f8
from
 data d left join
 (
  select
   x.id,
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.created_time end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.new_simple_val end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.created_time end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.new_simple_val end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.created_time end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.new_simple_val end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.created_time end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.new_simple_val end)
  from
   (
    select
     h.id, h.field_name, h.created_time, h.new_simple_val,
     row_number() over (partition by h.id, h.field_name order by h.created_time) as rn1,
     row_number() over (partition by h.id, h.field_name order by h.created_time desc) as rn2
    from
     (values (N'Счетчик А4 ЦВ:'), (N'Счетчик А4 Ч/Б:')) a(field_name) join
     hist h on h.field_name = a.field_name
    where
     h.created_time between @p_date_from and @p_date_to 
   ) x
  where
   x.rn1 = 1 or x.rn2 = 1
  group by
   x.id
 ) t(id, f1, f2, f3, f4, f5, f6, f7, f8) on t.id = d.id;
18 дек 18, 10:33    [21766678]     Ответить | Цитировать Сообщить модератору
 Re: история  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 53624
коллеги, всем спасибо
как написать общий запрос я представляю
Думаю, что будет с ресурсами и временем выполнения

Или на пустой БД об этом думать рано?
18 дек 18, 11:09    [21766740]     Ответить | Цитировать Сообщить модератору
 Re: история  [new]
invm
Member

Откуда: Москва
Сообщений: 9112
andreymx
Или на пустой БД об этом думать рано?
Именно.
18 дек 18, 11:14    [21766746]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить