Подскажите по оптимизации запроса UPDATE

Модераторы: kdv, CyberMax

Ответить
checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

Подскажите по оптимизации запроса UPDATE

Сообщение checker » 25 янв 2011, 14:10

Здравствуйте.

Подскажите, пожалуйста, по оптимизации запроса.

Есть таблица с 3 полями TABLE1(A, B, C), PRIMARY KEY (A, B). Содержимое примерно такое:

Код: Выделить всё

SELECT * FROM TABLE1

A    B     C
==== ===== =====
   1     1     1
   1     2     2
   2     1     0
   2     2     0
Надо скопировать значение поля C из записей с A=1 в соответствующие записи с A=2, у которых значение поля B одинаковое. Я написал такой запрос:

Код: Выделить всё

UPDATE TABLE1 T1
SET C = (
  SELECT C
  FROM TABLE1 T2 
  WHERE T2.A = 1 AND T2.B = T1.B)
WHERE A = 2
Как сделать более быстрый запрос?

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: Подскажите по оптимизации запроса UPDATE

Сообщение Dimitry Sibiryakov » 25 янв 2011, 14:53

Код: Выделить всё

create table table2 (b integer references table 1 (b), c integer);
insert into table2 (b, c) select b, max(c) from table group by b;
alter table table1 drop c;
Читай учебник по проектированию БД.

checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

Re: Подскажите по оптимизации запроса UPDATE

Сообщение checker » 25 янв 2011, 16:47

Dimitry Sibiryakov писал(а):

Код: Выделить всё

create table table2 (b integer references table 1 (b), c integer);
insert into table2 (b, c) select b, max(c) from table group by b;
alter table table1 drop c;
Читай учебник по проектированию БД.
Ну что же, понятно. Каков вопрос - таков и ответ. Действительно, таблицы в примере выше лучше сделать вторую таблицу с внешним ключом. Но тогда более сложное условие.

Допустим, что пара значений B, C не уникальна. Да и в самой таблице гораздо больше полей. Т.е. содержимое таблицы примерно такое:

Код: Выделить всё

SELECT * FROM TABLE1

A    B     C     D     E
==== ===== ===== ===== =====
   1     1     1     1     5
   1     2     2     4     5
   2     1     0     8     1
   2     2     0     0     1
   3     1     5     1     2
   3     2     6     1     1
   4     1     1     2     1
   5     1     1     1     1
Надо скопировать значения полей C, D, E из записей с A=1 в записи с A=2 и одинаковым значением B. Будет ли запрос по типу того, который я предложил выше, оптимальным?

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: Подскажите по оптимизации запроса UPDATE

Сообщение Dimitry Sibiryakov » 26 янв 2011, 14:46

Нет. RTFM MERGE.

checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

Re: Подскажите по оптимизации запроса UPDATE

Сообщение checker » 26 янв 2011, 15:16

Dimitry Sibiryakov писал(а):Нет. RTFM MERGE.
MERGE INTO появился только в Firebird 2.1. Мне необходимо, чтобы запрос работал на 1.5.Х и даже 1.0.Х. Как будет выглядеть запрос для этих версий?

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: Подскажите по оптимизации запроса UPDATE

Сообщение Dimitry Sibiryakov » 27 янв 2011, 14:45

Примерно так: http://ibase.ru/devinfo/testiu.htm

checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

Re: Подскажите по оптимизации запроса UPDATE

Сообщение checker » 27 янв 2011, 19:38

Dimitry Sibiryakov писал(а):Примерно так: http://ibase.ru/devinfo/testiu.htm
Спасибо, эту статью я читал. Только не пойму, как она может мне помочь? Во-первых, я спрашивал про SQL запрос, а не процедуру. А во вторых, цикл FOR все равно работает медленно.

Например, только что сделал тест: таблица с тремя INTEGER полями A, B, C из первого сообщения, 100 000 записей с A=1, B=1,2,3,..., C=1, 100 000 записей с A=2, B=1,2,3..., C=NULL. Так вот запрос UPDATE из первого сообщения выполняется 2.5 с, процедура ниже выполняется 3.8 с. Процедура написана по стопам указанной выше статьи. Цифры для теста взял не случайно. В моих реальных таблицах несколько раз по 500 000 записей.

Код: Выделить всё

CREATE PROCEDURE PROCEDURE_2 
AS
DECLARE VARIABLE B INTEGER;
DECLARE VARIABLE C INTEGER;
BEGIN
  FOR
    SELECT B, C FROM TABLE_1 WHERE A=1 INTO :B, :C
  DO
    UPDATE TABLE_1 SET C=:C WHERE A=2 AND B=:B;
END
Получается, что запрос UPDATE из первого сообщения самый быстрый. И что, быстрее сделать никак? И кроме того, я так и не понял, чем плох этот запрос?

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: Подскажите по оптимизации запроса UPDATE

Сообщение Dimitry Sibiryakov » 28 янв 2011, 14:36

Тот запрос неплох пока обновляется только одно поле. С увеличением числа обновляемых полей (в своём позднем сообщении ты назвал три поля) время его работы растёт в геометрической прогрессии.

Процедура, которую ты от числа полей не зависит. Но её оптимальность зависит от числа записей, которые надо обновить (а точнее - от отношения их числа к общему числу записей в таблице). Если обновляемых записей намного меньше общего числа, то запросы в ней надо поменять местами: внешним выбирать записи для обновления, а внутренним - новые значения. В этом случае процедура будет полностью эквивалентна запросу (опять же за исключением зависимости от числа полей).

checker
Сообщения: 16
Зарегистрирован: 09 авг 2005, 14:54

Re: Подскажите по оптимизации запроса UPDATE

Сообщение checker » 28 янв 2011, 18:44

Хм... Поменять запросы местами - на словах понятно, а на деле как? У меня получилось так:

Код: Выделить всё

CREATE PROCEDURE PROCEDURE_2
AS
DECLARE VARIABLE B INTEGER;
DECLARE VARIABLE C INTEGER;
BEGIN
  FOR
    SELECT B FROM TABLE_1 WHERE A=2 INTO :B
  DO
  BEGIN
    SELECT C FROM TABLE_1 WHERE A=1 AND B=:B INTO :C;
    IF (C IS NOT NULL) THEN 
      UPDATE TABLE_1 SET C=:C WHERE A=2 AND B=:B;
  END
END
Так эта процедура выполняется 4.1 секунды против 3.8 с предыдущей и 2.5 с запроса . Или я ошибся в процедуре?

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: Подскажите по оптимизации запроса UPDATE

Сообщение Dimitry Sibiryakov » 29 янв 2011, 17:01

Я не вижу ошибки. Возможно, я ошибся в оценке эффективности алгоритма...

Ответить