Страница 1 из 1
Подскажите по оптимизации запроса UPDATE
Добавлено: 25 янв 2011, 14:10
checker
Здравствуйте.
Подскажите, пожалуйста, по оптимизации запроса.
Есть таблица с 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
Как сделать более быстрый запрос?
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 25 янв 2011, 14:53
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;
Читай учебник по проектированию БД.
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 25 янв 2011, 16:47
checker
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. Будет ли запрос по типу того, который я предложил выше, оптимальным?
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 26 янв 2011, 14:46
Dimitry Sibiryakov
Нет. RTFM MERGE.
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 26 янв 2011, 15:16
checker
Dimitry Sibiryakov писал(а):Нет. RTFM MERGE.
MERGE INTO появился только в Firebird 2.1. Мне необходимо, чтобы запрос работал на 1.5.Х и даже 1.0.Х. Как будет выглядеть запрос для этих версий?
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 27 янв 2011, 14:45
Dimitry Sibiryakov
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 27 янв 2011, 19:38
checker
Спасибо, эту статью я читал. Только не пойму, как она может мне помочь? Во-первых, я спрашивал про 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 из первого сообщения самый быстрый. И что, быстрее сделать никак? И кроме того, я так и не понял, чем плох этот запрос?
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 28 янв 2011, 14:36
Dimitry Sibiryakov
Тот запрос неплох пока обновляется только одно поле. С увеличением числа обновляемых полей (в своём позднем сообщении ты назвал три поля) время его работы растёт в геометрической прогрессии.
Процедура, которую ты от числа полей не зависит. Но её оптимальность зависит от числа записей, которые надо обновить (а точнее - от отношения их числа к общему числу записей в таблице). Если обновляемых записей намного меньше общего числа, то запросы в ней надо поменять местами: внешним выбирать записи для обновления, а внутренним - новые значения. В этом случае процедура будет полностью эквивалентна запросу (опять же за исключением зависимости от числа полей).
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 28 янв 2011, 18:44
checker
Хм... Поменять запросы местами - на словах понятно, а на деле как? У меня получилось так:
Код: Выделить всё
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 с запроса . Или я ошибся в процедуре?
Re: Подскажите по оптимизации запроса UPDATE
Добавлено: 29 янв 2011, 17:01
Dimitry Sibiryakov
Я не вижу ошибки. Возможно, я ошибся в оценке эффективности алгоритма...