Проблема производительности запросов MS Access
Следующий запрос иногда работает.
Обычная проблема, когда я запускаю запрос, он не показывает количество записей этих записей старше 180 лет, хотя он показывает все другие данные.
l_section & dim_performance_score - списки SharePoint 2010.
Мне нужен отчет, который показывает записи, которые имеют возраст менее 90 дней, >=90 и < 180 дней и>=180 дней.
Если я сделаю пять разных сохраненных запросов и оставлю их присоединенными, запрос будет работать последовательно. Однако, когда я объединяю весь SQL в один хранимый запрос, все становится не так. Несмотря на проблему с производительностью, если я пытаюсь посмотреть на консолидированный запрос в окне разработки, MS Access падает. Это известная проблема с JET?
Кажется неэффективным поддерживать 5 или 6 разных запросов, чтобы получить один ответ.
Что я делаю, это лучший способ получить нужные данные?
PARAMETERS [compare date] DateTime;
SELECT
l_section.section,
[compare date] AS [As of Date],
total_count.[Total Records],
IIf([less_than_90].[<90 Days] Is Null,0,[less_than_90].[<90 Days]) AS [<90 Days],
IIf([greater_than_90].[>=90 & <180Days] Is Null,0,[greater_than_90].[>=90 & <180Days]) AS [>=90 & <180Days],
IIf([greater_than_180].[>=180 Days] Is Null,0,[greater_than_180].[>=180 Days]) AS [>=180 Days]
FROM
(
(
(
l_section
LEFT JOIN
(
SELECT
since_modified.section,
Count(since_modified.section) AS [>=180 Days]
FROM
(
SELECT
l_section.section,
IIf(dim_performance_score.[Modified] Is Null,0,DateDiff("d",dim_performance_score.[Modified],[compare date])) AS days_since_update
FROM
l_section
LEFT JOIN
dim_performance_score
ON
l_section.section = dim_performance_score.section
) as since_modified
WHERE
(((since_modified.days_since_update)>=180))
GROUP BY
since_modified.section
) as greater_than_180
ON
l_section.section = greater_than_180.section
)
LEFT JOIN
(
SELECT
since_modified.section,
Count(since_modified.section) AS [<90 Days]
FROM
(
SELECT
l_section.section,
IIf(dim_performance_score.[Modified] Is Null,0,DateDiff("d",dim_performance_score.[Modified],[compare date])) AS days_since_update
FROM
l_section
LEFT JOIN
dim_performance_score
ON
l_section.section = dim_performance_score.section
) as since_modified
WHERE
(((since_modified.days_since_update)<90))
GROUP BY
since_modified.section
) as less_than_90
ON
l_section.section = less_than_90.section
)
LEFT JOIN
(
SELECT
since_modified.section,
Count(since_modified.section) AS [>=90 & <180Days]
FROM
(
SELECT
l_section.section,
IIf(dim_performance_score.[Modified] Is Null,0,DateDiff("d",dim_performance_score.[Modified],[compare date])) AS days_since_update
FROM
l_section
LEFT JOIN
dim_performance_score
ON
l_section.section = dim_performance_score.section
) as since_modified
WHERE
(((since_modified.days_since_update)>=90
And
(since_modified.days_since_update)<180))
GROUP BY
since_modified.section
) as greater_than_90
ON
l_section.section = greater_than_90.section
)
LEFT JOIN
(
SELECT
l_section.section,
Count(IIf([section] Is Null,0,[section])) AS [Total Records]
FROM
l_section
LEFT JOIN
dim_performance_score
ON
l_section.section = dim_performance_score.section
GROUP BY l_section.section
) as total_count
ON
l_section.section = total_count.section
ORDER BY l_section.section;
2 ответа
Ну, я нашел другой способ получить то, что я хотел, и это намного проще. Я публикую свой собственный ответ, чтобы показать до и после.
SELECT
section_last_modified.section,
Count(section_last_modified.section) AS [Total Records],
Sum(IIf([days_since_modified]<90,1,0)) AS [< 90 days],
Sum(IIf([days_since_modified] Between 90 And 180,1,0)) AS [>=90 and <180 days],
Sum(IIf([days_since_modified]>=180,1,0)) AS [>=180 days]
FROM section_last_modified
GROUP BY section_last_modified.section;
-----section_last_modified-------
PARAMETERS [Compare Date] DateTime;
SELECT
dim_performance_score.section,
DateDiff("d",dim_performance_score.[Modified],[Compare Date]) AS days_since_modified
FROM
dim_performance_score
WHERE
(((dim_performance_score.[Content Type])="stacker maintenance"))
ORDER BY
dim_performance_score.section;
Тройное вложение? Это не может быть правдой. Похоже, что каждое соединение находится (или должно быть) на одном уровне.
PARAMETERS [compare date] DateTime;
SELECT
l_section.section,
[compare date] AS [As of Date],
total_count.[Total Records],
IIf([less_than_90].[<90 Days] Is Null,0,[less_than_90].[<90 Days]) AS [<90 Days],
IIf([greater_than_90].[>=90 & <180Days] Is Null,0,[greater_than_90].[>=90 & <180Days]) AS [>=90 & <180Days],
IIf([greater_than_180].[>=180 Days] Is Null,0,[greater_than_180].[>=180 Days]) AS [>=180 Days]
FROM l_section
LEFT JOIN
(
SELECT
since_modified.section,
Count(since_modified.section) AS [>=180 Days]
FROM
(
SELECT
l_section.section,
IIf(dim_performance_score.[Modified] Is Null,0,DateDiff("d",dim_performance_score.[Modified],[compare date])) AS days_since_update
FROM
l_section
LEFT JOIN
dim_performance_score
ON
l_section.section = dim_performance_score.section
) as since_modified
WHERE
(((since_modified.days_since_update)>=180))
GROUP BY
since_modified.section
) as greater_than_180
ON
l_section.section = greater_than_180.section
LEFT JOIN
(
SELECT
since_modified.section,
Count(since_modified.section) AS [<90 Days]
FROM
(
SELECT
l_section.section,
IIf(dim_performance_score.[Modified] Is Null,0,DateDiff("d",dim_performance_score.[Modified],[compare date])) AS days_since_update
FROM
l_section
LEFT JOIN
dim_performance_score
ON
l_section.section = dim_performance_score.section
) as since_modified
WHERE
(((since_modified.days_since_update)<90))
GROUP BY
since_modified.section
) as less_than_90
ON
l_section.section = less_than_90.section
LEFT JOIN
(
SELECT
since_modified.section,
Count(since_modified.section) AS [>=90 & <180Days]
FROM
(
SELECT
l_section.section,
IIf(dim_performance_score.[Modified] Is Null,0,DateDiff("d",dim_performance_score.[Modified],[compare date])) AS days_since_update
FROM
l_section
LEFT JOIN
dim_performance_score
ON
l_section.section = dim_performance_score.section
) as since_modified
WHERE
(((since_modified.days_since_update)>=90
And
(since_modified.days_since_update)<180))
GROUP BY
since_modified.section
) as greater_than_90
ON
l_section.section = greater_than_90.section
LEFT JOIN
(
SELECT
l_section.section,
Count(IIf([section] Is Null,0,[section])) AS [Total Records]
FROM
l_section
LEFT JOIN
dim_performance_score
ON
l_section.section = dim_performance_score.section
GROUP BY l_section.section
) as total_count
ON
l_section.section = total_count.section
ORDER BY l_section.section;