как ускорить обработку данных

Александр Журавлев · 1 мая, 2020

Добрый день. только начал разбираться с базами данных и возникли сложности при обработке больших объемов.

в таблице есть записи, некоторые числовые данные за несколько минутный интервал. поля TimeFrom и TimeTo есть соответственно, по которым ясен интервал.

Нужно собрать таблицу почасовых данных. где каждая запись - сумма данных за 1 час.

  c:=chr(39);
  FDQueryL[0].DisableControls;  DBGridL0.DataSource.DataSet.DisableControls;
  DBGridL0.Visible:=false;

  idR:=0;
  d:=EncodeDate(2017,1,1)+EncodeTime(1,0,0,0);   d1:=d;
  while d<now do
  begin
    //for line := 4 to 20 do
    begin
      //выбитаем все что в 1 часе
      FDQueryL[0].Open('SELECT * FROM '+valL0.Values['Table']+' '+
        'WHERE ID_CL='+inttostr(line)+' AND '+
        'TimeFrom BETWEEN '+
        c+FormatDateTime('yyyy-mm-dd h:mm:ss', d)+c+
        ' AND '+
        c+FormatDateTime('yyyy-mm-dd h:mm:ss', IncSecond(d, 3599))+c+';');
      //считаем сумму минут
      m:=0; InA:=0; InC:=0; OutA:=0; OutC:=0;
      DBGridL0.DataSource.DataSet.First;
      while (not DBGridL0.DataSource.DataSet.Eof) do
      begin
        m:=m+SecondOfTheDay(DBGridL0.DataSource.DataSet.Fields[2].AsDateTime)-SecondOfTheDay(DBGridL0.DataSource.DataSet.Fields[1].AsDateTime);
        //суммирование непосредственно нужных данных      
        InA:=InA + DBGridL0.DataSource.DataSet.Fields[4].AsInteger;
        InC:=InC + DBGridL0.DataSource.DataSet.Fields[5].AsInteger;
        OutA:=OutA + DBGridL0.DataSource.DataSet.Fields[6].AsInteger;
        OutC:=OutC + DBGridL0.DataSource.DataSet.Fields[7].AsInteger;
        Application.ProcessMessages;
        DBGridL0.DataSource.DataSet.Next;
      end;
      //выставляем статус новой часовой записи. нолные данные или нет
      if m=3600 then status:=1 else status:=0; //если запись состоит из 3600 секунд данных 
      if m<>0 then //добавляем запись в приемник, если данные вообще есть 
      begin
        FDQueryL[0].ExecSQL(
          'INSERT INTO xxx (ID, TimeFrom, TimeTo, ID_CL, InA, InС, OutA, OutC, Status) VALUES ('+
              c + IntToStr(idR) + c + ', ' +    //ID
              c + FormatDateTime('yyyy-mm-dd h:mm:ss', d) + c + ', ' +                     //TimeFrom
              c + FormatDateTime('yyyy-mm-dd h:mm:ss', d+EncodeTime(1,0,0,0)) + c + ', ' +                   //TimeTo
              c + IntToStr(line) + c +                         ', ' +                   //ID_CountingLine
              c + IntToStr(InA) + c +                          ', ' +
              c + IntToStr(InC) + c +                          ', ' +
              c + IntToStr(OutA) + c +                         ', ' +
              c + IntToStr(OutC) + c +                         ', ' +
              c + IntToStr(status) + c +                       '); ');
        idR:=idR+1;
      end;
    end;
    d:=RecodeMilliSecond(d, 0); // это чтобы не накапливалась ошибка  инкремента дат
    d:=IncSecond(d,3600);
    Application.ProcessMessages;
  end;

  FDQueryL[0].EnableControls;  DBGridL0.DataSource.DataSet.EnableControls;
  DBGridL0.Visible:=true;

Вот так костылём кое-как делаю, как видно я тут и прям по DBGrid лопачу и FDQuery, что на самом деле с одной базой связано, одним дата сетом. данные за 1 год обрабатываются СУТКИ!

Я правильно понимаю, что делаю это в режиме LiveDataWindowMode ? Я даже не использую FDTable вообще.

Толковой статьи как правильно организовать обработку не нашёл. только какие-то отдельные статьи.

пока что в них увидел такие советы, как кэширование:

FDQuery1.CachedUpdates := True;
...
FDQuery1.Edit;
...
FDQuery1.Post;
...
FDQuery1.Append;
...
FDQuery1.Post;
...
FDQuery1.ApplyUpdates;
FDQuery1.CommitUpdates;
FDQuery1.CachedUpdates := False;

Но тут мне не понятно сколько таких команд Append может быть максимум? как часто делать ApplyUpdates...

Видел советы использовать TFDMemTable. Но я т.к. новичок не понимаю даже как в общем это организовать?

сделать два TFDMemTable (источника и приемника) ? какими методами внутри пользоваться, edit/append/delete или sql запросами, что быстрее?

применять ли мне кэширование и tfdmemTable одновременно ?

Вопрос: как правильно и БЫСТРО обработать большой объем данных?

П.С. может дадите хорошую ссылку на статью, где описан принцип построения приложения, или классный исходник, где можно было бы подсмотреть как делают профессионалы (желательно с минимумом обращения к визуальным компонентам вообще, т.к. моё приложение я буду переделывать в консольное, для фоновой обработки). В будущем мне кстати понадобятся ещё потоки для одновременной обработки нескольких таких таблиц, есть ли готовые компоненты в firedac или пользоваться tthread, масса вопросов....

Изменено 1 мая, 2020 пользователем Александр Журавлев

Александр Журавлев · 1 мая, 2020

в общем, попробовал переписать код с использованием TFDMemTable:

var mtL, mtR, mt: TFDMemTable;
d,d1,d2: tdatetime;
s,id: string;
c:char;  idR, m, line, status, InA, InC, OutA, OutC, h:int64;
begin
  c:=chr(39);
  mtL:=TFDMemTable.Create(nil);  mtR:=TFDMemTable.Create(nil);

  FDQueryL[0].Connection:=FDConnectL[0];
  FDQueryR[0].Connection:=FDConnectL[0];
  FDQueryL[0].FetchOptions.Unidirectional := False;
  FDQueryR[0].FetchOptions.Unidirectional := False;
  FDQueryL[0].Sql.Text := 'SELECT * FROM source_table;';
  FDQueryL[0].Open;
  FDQueryL[0].FetchAll;
  FDQueryR[0].Sql.Text := 'SELECT * FROM destination_table;';
  FDQueryR[0].Open;
  FDQueryR[0].FetchAll;

//  mtL.Data:=FDQueryL[0].Data;
//  mtR.Data:=FDQueryR[0].Data;
  mtL.CopyDataSet(FDQueryL[0], [coStructure, coRestart, coAppend] );
  mtR.CopyDataSet(FDQueryR[0], [coStructure, coRestart, coAppend] );
  mtL.Open;
  mtR.Open;

  mtL.DisableControls;
  mtR.DisableControls;

  with mtR do
  begin
    LogChanges := False;
    FetchOptions.RecsMax := 300000;  //Sample value
    ResourceOptions.SilentMode := True;
    UpdateOptions.LockMode := lmNone;
    UpdateOptions.LockPoint := lpDeferred;
    UpdateOptions.FetchGeneratorsPoint := gpImmediate;
  end;
  mtR.BeginBatch;
  
  idR:=0;
  d:=EncodeDate(2019,6,1)+EncodeTime(1,0,0,0);   d1:=d;
  while d<now do
  begin
    for line := 4 to 20 do
    begin
      //выбитаем все что в 1 часе
      mtL.Filtered := False;
      //не обращайте внимания на кривизну кода фильтра
      mtL.Filter := '(ID_CL='+inttostr(line)+') AND '+
                    '(TimeFrom >= '+c+DateTimeToStr(d)+c+
                    ') AND ('+
                    'TimeFrom < '+c+FormatDateTime('dd.mm.yyyy h:mm:ss', IncSecond(d, 3599))+c+')';
      mtL.Filtered := true;
      //считаем сумму минут
      m:=0; InA:=0; InC:=0; OutA:=0; OutC:=0;
      mtL.First;
      while (not mtL.Eof) do
      begin
        m:=m+SecondOfTheDay(mtL.Fields[2].AsDateTime)-SecondOfTheDay(mtL.Fields[1].AsDateTime);
        InA:=InA + mtL.Fields[4].AsInteger;     InC:=InC + mtL.Fields[5].AsInteger;
        OutA:=OutA + mtL.Fields[6].AsInteger;   OutC:=OutC + mtL.Fields[7].AsInteger;
        Application.ProcessMessages;
        mtL.Next;
      end;
      //выставляем статус пакету
      if m=3600 then status:=1 else status:=0;
      if m<>0 then //добавляем записб в приемник
      begin
        with mtR do
        begin
          Append;
          Fields[0].AsInteger := idR;
          Fields[1].AsDateTime := d;
          Fields[2].AsDateTime := d+EncodeTime(1,0,0,0);
          Fields[3].AsInteger := line;
          Fields[4].AsInteger := InA;         Fields[5].AsInteger := InC;
          Fields[6].AsInteger := OutA;        Fields[7].AsInteger := OutC;
          Post;
        end;
        idR:=idR+1;
      end;
    end;
    d:=RecodeMilliSecond(d, 0);
    d:=IncSecond(d,3600);
    Application.ProcessMessages;
  end;
  mtR.EndBatch;

  mtL.EnableControls;
  mtR.EnableControls;

  FDQueryR[0].Close;
  FDQueryR[0].CopyDataSet(mtR);
  FDQueryR[0].Open;

  mtL.Free;     mtR.Free;

Результата нет - памяти жрет 170 Мб (вместо 5), а работает так же медленно.

помогите. что я делаю не так?

Дмитрий Ш. · 1 мая, 2020

5 часов назад, Александр Журавлев сказал:

Нужно собрать таблицу почасовых данных. где каждая запись - сумма данных за 1 час.

изучаем SQL.

Insert into T1 (id, cnt)
select clnt_id, count(*) from T2 group by 1

Евгений Корепов · 17 мая, 2020

Как верно подметил Дмитрий - знание SQL наше все.

Вот к примеру я создал тестовую таблицу и наполнил ее тестовыми данными:

CREATE TABLE `test001` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `TimeFrom` datetime DEFAULT NULL,
  `InA` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

INSERT INTO `test001` VALUES ('1', '2020-05-17 16:14:36', '1');
INSERT INTO `test001` VALUES ('2', '2020-05-17 16:12:56', '2');
INSERT INTO `test001` VALUES ('3', '2020-05-17 16:12:56', '3');
INSERT INTO `test001` VALUES ('4', '2020-05-17 17:12:56', '4');
INSERT INTO `test001` VALUES ('5', '2020-05-17 17:12:56', '5');
INSERT INTO `test001` VALUES ('6', '2020-05-17 17:12:56', '6');
INSERT INTO `test001` VALUES ('7', '2020-05-17 18:12:56', '7');
INSERT INTO `test001` VALUES ('8', '2020-05-17 18:12:56', '8');
INSERT INTO `test001` VALUES ('9', '2020-05-17 18:12:56', '9');
INSERT INTO `test001` VALUES ('10', '2020-05-17 19:12:56', '10');
INSERT INTO `test001` VALUES ('11', '2020-05-17 19:12:56', '11');
INSERT INTO `test001` VALUES ('12', '2020-05-17 19:12:56', '12');
INSERT INTO `test001` VALUES ('13', '2020-05-17 20:12:56', '13');
INSERT INTO `test001` VALUES ('14', '2020-05-17 20:12:56', '14');
INSERT INTO `test001` VALUES ('15', '2020-05-17 20:12:56', '15');
INSERT INTO `test001` VALUES ('16', '2020-05-17 21:12:56', '16');
INSERT INTO `test001` VALUES ('17', '2020-05-17 21:12:56', '17');
INSERT INTO `test001` VALUES ('18', '2020-05-17 21:12:56', '18');

Для получения почасовых сумм по полю InA мне достаточно очень простого запроса:

SELECT
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(test001.TimeFrom) / 3600) * 3600),
SUM(test001.InA) as SumInA
FROM
test001
GROUP BY
FLOOR(UNIX_TIMESTAMP(test001.TimeFrom) / 3600) * 3600

Результат запроса будет выглядеть вот так:

2020-05-17 16:00:00	6
2020-05-17 17:00:00	15
2020-05-17 18:00:00	24
2020-05-17 19:00:00	33
2020-05-17 20:00:00	42
2020-05-17 21:00:00	51

Скорость выдачи результата будет большой - при миллионах записей в исходной таблице дело нескольких секунд или десятков секунд (зависит от железа сервера).

Но это все равно медленно, потому что на каждую запись таблицы будет производится вычисление "FLOOR(UNIX_TIMESTAMP(test001.TimeFrom) / 3600) * 3600" - деление, округление и умножение.

Если это разовая выборка - проблем нет. Но если вам нужно регулярно дергать из таблицы данные, то лучше добавить в таблицу поле TimeFromHour, в которое сразу записывать значение FLOOR(UNIX_TIMESTAMP(test001.TimeFrom) / 3600) * 3600. Это можно сделать руками или триггером при вставке. Добавить Индекс по полю TimeFromHour и в запросе тоже группировать (GROUP BY) по этому полю:

ALTER TABLE `test001`
ADD INDEX `Index-TimeFromHour` (`TimeFromHour`) USING BTREE ;

Тогда вы получите максимально возможное быстродействие.

Если нужно результат вставить в другую таблицу, то используйте запрос вроде такого:

INSERT INTO 
test_sum
(TimeFrom, SumInA)
(
	SELECT
		FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(test001.TimeFrom) / 3600) * 3600),
		SUM(test001.InA) as SumInA
	FROM
		test001
	GROUP BY
		FLOOR(UNIX_TIMESTAMP(test001.TimeFrom) / 3600) * 3600
)

Изменено 17 мая, 2020 пользователем Евгений Корепов

Войти

как ускорить обработку данных

Вопрос

Александр Журавлев

Ссылка на комментарий

3 ответа на этот вопрос

Рекомендуемые сообщения

Александр Журавлев

Ссылка на комментарий

Дмитрий Ш.

Ссылка на комментарий

Евгений Корепов

Ссылка на комментарий

Присоединяйтесь к обсуждению

Последние посетители 0 пользователей онлайн

Сайт

FGX

Активность