Медленное выполнение запроса в Firebird

sql performance firebird

1122 просмотра

3 ответа

149 Репутация автора

Мой выполненный запрос SQL выглядит следующим образом:

update elements E
    set E.END_I = (select n.node_num
                   from nodes N 
                   where abs(E.X_I - N.XI) < 0.001 and
                         abs(E.Y_I - N.YI) < 0.001 and
                         abs(E.Z_I - N.ZI) < 0.001
                  )

На это уходит около 24 секунд, я читал об устранении неполадок Firebird Почему мой запрос к базе данных медленный? Он инструктирует создавать индексы для связанных полей в таблице, и я добавил уменьшающие / увеличивающие индексы для полей XI, YI, ZI в обеих таблицах Nodes и Elements. Но, тем не менее, производительность очень низкая, в базе данных 6677 строк, и я использую FlameRobin в качестве редактора SQL.

Интересно, что: как показано в руководстве по устранению неисправностей Firebird,

Если вы видите, что ЕСТЕСТВЕННЫЙ план идет против большого стола, вы нашли проблему

эта ошибка описывается как плохой случай и источник замедления, рекомендуемое решение - создать уменьшающиеся индексы для связанных полей. Но в моем случае, даже после определения индексов, мне кажется, что я все еще страдаю от этого ПЛАНА (N НАТУРАЛЬНОГО), ПЛАНА (Е НАТУРАЛЬНОГО), который сообщается в выходных данных фламеробина, как показано ниже.

Как я должен устранить это?

Preparing query: update elements E set E.END_I = (select n.node_num from nodes N 
where abs(E.X_I-N.XI)<0.001 and abs(E.Y_I - N.YI)<0.001 and abs(E.Z_I-N.ZI)<0.001 )
Prepare time: 0.004s
PLAN (N NATURAL)
PLAN (E NATURAL)

Executing...
Done.
108818273 fetches, 79227 marks, 4050 reads, 9380 writes.
0 inserts, 6677 updates, 0 deletes, 0 index, 14549183 seq.
Delta memory: 212 bytes.
ELEMENTS: 6677 updates. 
6677 rows affected directly.
Total execution time: 24.038s
Script execution finished.

CREATE DESCENDING INDEX IDX_ELEMENTS1 ON ELEMENTS (Z_I);
CREATE DESCENDING INDEX IDX_XI ON ELEMENTS (X_I);
CREATE DESCENDING INDEX IDX_YI ON ELEMENTS (Y_I);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ELEMENTS TO  SYSDBA WITH GRANT OPTION;

CREATE DESCENDING INDEX IDX_NODES1_XI ON NODES (XI);
CREATE DESCENDING INDEX IDX_NODES1_YI ON NODES (YI);
CREATE DESCENDING INDEX IDX_NODES1_ZI ON NODES (ZI);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON NODES TO  SYSDBA WITH GRANT OPTION;
Автор: Sonya Blade Источник Размещён: 07.01.2016 01:05

Ответы (3)


2 плюса

11043 Репутация автора

Решение

Ваш запрос замедлен из-за того, abs()что индекс пустого столбца не работает с выражениями. Попробуйте изменить запрос, чтобы хотя бы дать БД возможность использовать индекс

update elements E
    set E.END_I = (select n.node_num
                   from nodes N 
                   where N.XI < E.X_I + 0.001 AND N.XI > E.X_I - 0.001
                   AND N.YI < E.Y_I + 0.001 AND N.YI > E.Y_I - 0.001
                   AND N.ZI < E.Z_I + 0.001 AND N.ZI > E.Z_I - 0.001
                  )
Автор: Jakub Kania Размещён: 07.01.2016 01:42

0 плюса

4426 Репутация автора

Создайте индексы для столбцов X_I, Y_I, Z_I, затем выполните оператор:

MERGE INTO elements dst
   USING (
    SELECT e.x_i, e.y_i, e.z_i, n.node_num
    FROM nodes N JOIN elements E ON 
      abs(E.X_I - N.XI) < 0.001 and
      abs(E.Y_I - N.YI) < 0.001 and
      abs(E.Z_I - N.ZI) < 0.001
   ) src
ON dst.X_I = src.X_I AND dst.Y_I = src.Y_I AND dst.Z_I = src.Z_I
WHEN MATCHED THEN UPDATE SET dst.END_I = src.NODE_NUM

Как уже упоминалось в ответе здесь https://stackoverflow.com/a/34656659/55350, вы можете избавиться от функций ABS и создать индексы для столбцов XI, YI, ZI таблицы N для дальнейшего ускорения процесса.

Автор: Andrej Kirejeŭ Размещён: 07.01.2016 01:54

0 плюса

51 Репутация автора

Может быть, что-то подобное может помочь:

1) Создайте декартово произведение между таблицами, рассчитав X, Y и Z.

2) Фильтровать только нужные записи.

3) Обновить записи элементов со значением node_num.

execute block
as
declare variable key integer; -- primary key type
declare variable node_num integer; -- node_num type
begin
  for
    select
      key,
      node_num
    from (
      select
        E.key, -- primary key name
        N.node_num,
        abs(E.X_I - N.XI) as X,
        abs(E.Y_I - N.YI) as Y,
        abs(E.Z_I - N.ZI) as Z
      from elements E, nodes N)
    where (X < 0.001)
      and (Y < 0.001)
      and (Z < 0.001)
    into
      :key,
      :node_num
  do
  begin
    update elements set
      END_ID = :node_num
    where elements.key = :key; -- primary key name
  end
end
Автор: dsonda Размещён: 07.01.2016 03:54
Вопросы из категории :
32x32