У меня есть следующий огромный запрос, который содержит повторяющиеся подзапросы. Мне он кажется очень неэффективным. Как я могу его оптимизировать?
SELECT T2.date1, T2.date2, T2.period, T1.market, T1.ticker, 0 AS scenario
FROM
(SELECT DISTINCT
Q.market AS market,
Q.ticker AS ticker
FROM portfolio.scenario S RIGHT JOIN portfolio.quote Q
ON S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
Q.market = S.market AND
Q.ticker = S.ticker
WHERE Q.date = '2010-07-01' AND
S.date1 IS NULL) AS T1
JOIN
(SELECT DISTINCT S.date1, S.date2, S.period
FROM scenario S
WHERE S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01') AS T2
UNION
SELECT S.date1 AS date1,
S.date2 AS date2,
S.period AS period,
Q.market AS market,
Q.ticker AS ticker,
Q.close * EXP(S.ratio) AS scenario
FROM portfolio.scenario S , portfolio.quote Q
WHERE S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
Q.date = '2010-07-01' AND
Q.market = S.market AND
Q.ticker = S.ticker
UNION
SELECT T2.date1, T2.date2, T2.period, T1.market, T1.ticker, 0 AS scenario
FROM
(SELECT DISTINCT
Q.market AS market,
Q.ticker AS ticker
FROM portfolio.scenario S , portfolio.quote Q
WHERE Q.date = '2010-07-01' AND
Q.market = S.market AND
Q.ticker = S.ticker AND
S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' ) AS T1
JOIN
(SELECT DISTINCT S.date1, S.date2, S.period
FROM scenario S
WHERE S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01') AS T2
WHERE (T2.date1, T2.date2, T2.period, T1.market, T1.ticker)
NOT IN (SELECT S.date1 AS date1,
S.date2 AS date2,
S.period AS period,
Q.market AS market,
Q.ticker AS ticker
FROM portfolio.scenario S , portfolio.quote Q
WHERE Q.date = '2010-07-01' AND
Q.market = S.market AND
Q.ticker = S.ticker AND
S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' )
ORDER BY
date1,date2,period,market,ticker
После комментария @Bruce и некоторой логики для сокращения подзапроса мой запрос теперь выглядит так:
(SELECT S.date1,
S.date2,
S.period,
Q.market,
Q.ticker,
Q.close * EXP(S.ratio) AS scenario
FROM portfolio.scenario S , portfolio.quote Q
WHERE
S.date1 >= (@date1 := '2009-09-01') AND
S.date2 <= (@date2 := '2010-07-01') AND
Q.date = (@qdate := '2010-07-01') AND
S.series =
(@series :=
(SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1)) AND
Q.market = S.market AND
Q.ticker = S.ticker)
UNION
(SELECT T2.date1, T2.date2, T2.period, T1.market, T1.ticker, 0 AS scenario
FROM
(SELECT Q.market, Q.ticker
FROM quote Q
WHERE Q.date = @qdate) AS T1
JOIN
(SELECT DISTINCT S.date1, S.date2, S.period
FROM scenario S
WHERE S.series = @series AND
S.date1 >= @date1 AND
S.date2 <= @date2) AS T2
WHERE (T2.date1, T2.date2, T2.period, T1.market, T1.ticker)
NOT IN
(SELECT S.date1,
S.date2,
S.period,
Q.market,
Q.ticker
FROM portfolio.scenario S , portfolio.quote Q
WHERE Q.date = @qdate AND
Q.market = S.market AND
Q.ticker = S.ticker AND
S.series = @series AND
S.date1 >= @date1 AND
S.date2 <= @date2 ))
Однако, если я изменил
(@series :=
(SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1))
to be
(@series :=
(SELECT S.series
FROM scenario S
WHERE S.date1 >= @date1 AND
S.date2 <= @date2 AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1))
Его обработка занимает слишком много времени (я выполнил запрос 10 минут назад и до сих пор не получил результата), в то время как запрос обычно возвращается через 5 секунд.
Кроме того, когда я сбрасываю переменные, выполнение приводит к неверному результату (возможно, используется значение переменной из предыдущего выполнения). Как я могу изменить это, не добавляя операторы SET (я бы хотел, чтобы это был один запрос)
