Среда MS Access. Все таблицы и запросы находятся в одной локальной БД.
У меня результирующий запрос выполняется где-то секунд 7-10, плюс по несколько секунд при получении записей (пачками). А нужно, чтобы почти мухой летало. Вот такие дела
Можно ли это как-то оптимизировать? (замену железа просьба не предлагать )
базовая таблица: t1 (~15000 записей)
LONG id1 (index)
LONG id2 (index)
базовая таблица: t2 (~10000 записей)
LONG id2 (uniq index)
DATETIME date (index)
промежуточный запрос: q1
SELECT t1.id1, t1.id2, t2.date
FROM t1 INNER JOIN t2 ON t1.id2 = t2.id2;
результирующий запрос: q2
PARAMETERS prmDate As DATETIME;
SELECT a1.id1, a1.id2
FROM q1 AS a1
WHERE a1.date = (SELECT Max(a2.date)
FROM q1 AS a2
WHERE a2.id1 = a1.id1 AND a2.date <= prmDate);
CS>базовая таблица: t1 (~15000 записей)
CS>LONG id1 (index)
CS>LONG id2 (index)
CS>базовая таблица: t2 (~10000 записей)
CS>LONG id2 (uniq index)
CS>DATETIME date (index)
CS>промежуточный запрос: q1
CS>SELECT t1.id1, t1.id2, t2.date
CS>FROM t1 INNER JOIN t2 ON t1.id2 = t2.id2;
CS>результирующий запрос: q2
CS>PARAMETERS prmDate As DATETIME;
CS>SELECT a1.id1, a1.id2
CS>FROM q1 AS a1
CS>WHERE a1.date = (SELECT Max(a2.date)
CS> FROM q1 AS a2
CS> WHERE a2.id1 = a1.id1 AND a2.date <= prmDate);
-- маленькая рекомендация, писать внутренний джойн так:SELECT T1.ID1, T1.ID2, T2.DATE
FROM T1, T2 WHERE T1.ID2=T2.ID2;
-- ну это лирическое отступление.
-- теперь о птицах, параметр - &D1SELECT T1.ID1, T1.ID2, MAX(T2.DATE) AS MAX_DATE
FROM T1, T2
WHERE T1.ID2=T2.ID2 AND T2.DATE<&D1
GROUP BY T1.ID1, T1.ID2
На оракле работает в 4-5 раз быстрее твоего запроса на выборке:
T1 — 400000 записей, T2 — 60000 записей, результирующий набор возвращает порядка 5000 записей, происходит полный фетч на клиента.
Хинтов оптимизации нет, оптимизатор по умолчанию CBO (стоймостной), индекса по T2.DATE не построено.
Появляется побочный эффект — поле MAX_DATE, но это, я думаю, обходится элементарно.
Благодарности выражать оценкой
Никогда не бойся браться делать то, что делать не умеешь. Помни, ковчег был построен любителем. Профессионалы построили Титаник...
VVP>-- теперь о птицах, параметр - &D1
VVP>SELECT T1.ID1, T1.ID2, MAX(T2.DATE) AS MAX_DATE
VVP>FROM T1, T2
VVP>WHERE T1.ID2=T2.ID2 AND T2.DATE<&D1
VVP>GROUP BY T1.ID1, T1.ID2
З.Ы.
Избавляемся от колонки MAX_DATE:
SELECT T1.ID1, T1.ID2 /*, MAX(T2.DATE) AS MAX_DATE*/FROM T1, T2
WHERE T1.ID2=T2.ID2 AND T2.DATE<&D1
GROUP BY T1.ID1, T1.ID2
HAVING MAX(T2.DATE)<&D1;
Скорость та же.
Никогда не бойся браться делать то, что делать не умеешь. Помни, ковчег был построен любителем. Профессионалы построили Титаник...
VVP>-- теперь о птицах, параметр - &D1
VVP>SELECT T1.ID1, T1.ID2, MAX(T2.DATE) AS MAX_DATE
VVP>FROM T1, T2
VVP>WHERE T1.ID2=T2.ID2 AND T2.DATE<&D1
VVP>GROUP BY T1.ID1, T1.ID2
VVP>
...
Спасибо, конечно, но боюсь что этот запрос не соответствует исходному. Здесь выбираются все записи из t1 (ведь группировка происходит по всем полям этой таблицы), а в исходном варианте из таблицы t1 выбирается по одной записи для каждого id1, причем именно те записи, где id2 в таблице t2 соответствует максимальная дата.
Здравствуйте, ChainSmoker, Вы писали:
CS>Спасибо, конечно, но боюсь что этот запрос не соответствует исходному. Здесь выбираются все записи из t1 (ведь группировка происходит по всем полям этой таблицы), а в исходном варианте из таблицы t1 выбирается по одной записи для каждого id1, причем именно те записи, где id2 в таблице t2 соответствует максимальная дата.
Ну ты бы выполнил этот запрос и сравнил бы результаты, а потом бы боялся.
Никогда не бойся браться делать то, что делать не умеешь. Помни, ковчег был построен любителем. Профессионалы построили Титаник...
Здравствуйте, VVP, Вы писали:
VVP>Здравствуйте, ChainSmoker, Вы писали:
CS>>Спасибо, конечно, но боюсь что этот запрос не соответствует исходному. Здесь выбираются все записи из t1 (ведь группировка происходит по всем полям этой таблицы), а в исходном варианте из таблицы t1 выбирается по одной записи для каждого id1, причем именно те записи, где id2 в таблице t2 соответствует максимальная дата. VVP>Ну ты бы выполнил этот запрос и сравнил бы результаты, а потом бы боялся.
Гонишь. Твой запрос будет возвращать все мыслимые сочетания id1 и id2. И для каждого такого сочетания будет браться максимальная дата. А надо-значение id2 при максимальной дате для заданного id1. Чтобы тебе было яснее, заполним таблички так:
Здравствуйте, ChainSmoker, Вы писали:
CS>Среда MS Access. Все таблицы и запросы находятся в одной локальной БД. CS>У меня результирующий запрос выполняется где-то секунд 7-10, плюс по несколько секунд при получении записей (пачками). А нужно, чтобы почти мухой летало. Вот такие дела CS>Можно ли это как-то оптимизировать? (замену железа просьба не предлагать )
CS>базовая таблица: t1 (~15000 записей) CS>LONG id1 (index) CS>LONG id2 (index)
CS>базовая таблица: t2 (~10000 записей) CS>LONG id2 (uniq index) CS>DATETIME date (index)
CS>промежуточный запрос: q1 CS>
CS>PARAMETERS prmDate As DATETIME;
CS>SELECT a1.id1, a1.id2
CS>FROM q1 AS a1
CS>WHERE a1.date = (SELECT Max(a2.date)
CS> FROM q1 AS a2
CS> WHERE a2.id1 = a1.id1 AND a2.date <= prmDate);
CS>
CS>Заранее благодарен.
Может поможет то, что дата являясь max — будет первой при выборке с order by дате ?
Попробовать использовать top ?
PARAMETERS prmDate As DATETIME;
select t1.id1, t1.id2
from t1 where t1.id2 in (select top 1 t2.id2 from t2 where t2.date <= prmDate order by t2.date desc);
Если кому интересно, то после многочасовых раздумий мне все-таки удалось кое-что придумать.
А именно, избавиться от главного узкого места — подчиненного запроса в q2. Вместо того, чтобы вычислять максимальную дату для каждой записи q2 с помощью подчиненного запроса, строится отдельный запрос, разом находящий максимальную дату для каждого id1. Вроде все работает... и никаких тормозов
q1
PARAMETERS prmDate DateTime;
SELECT t1.id1, max(t2.data) AS MaxDate
FROM t1 INNER JOIN t2 ON t1.id2 = t2.id2
WHERE t2.date <= prmDate
GROUP BY t1.id1
q2
SELECT q1.id1, t2.id2
FROM q1 INNER JOIN t2 WHERE q1.MaxDate = t2.date
)
