Запрос MS Access 2010 SQL Top N по проблеме производительности группы (продолжение)

У меня есть важные проблемы с производительностью (вплоть до тайм-аута) в MS Access 2010 с запросом ниже. Таблица TempTableAnalysis содержит от 10'000-15'000 записей. Я уже получил вход с этого форума для работы с временной таблицей в первом запросе (запрос MS Access 2010 SQL Top N по проблеме производительности группы )

Может ли кто-нибудь объяснить, как реализовать временную таблицу в подзапросе и как присоединиться к ней? Я не могу заставить его работать.

Любые другие предложения по повышению производительности высоко ценятся.

Вот мой запрос:

SELECT t2.Loc, t2.ABCByPick, t2.Planner, t2.DmdUnit, ROUND(t2.MASE,2) AS MASE, ROUND(t2.AFAR,2) AS AFAR FROM TempTableAnalysis AS t2 WHERE t2.MASE IN ( SELECT TOP 10 t1.MASE FROM TempTableAnalysis AS t1 WHERE t1.ABCByPick = t2.ABCByPick ORDER BY t1.MASE DESC ) ORDER BY t2.ABCByPick, t2.MASE DESC; 

Оптимизация производительности запросов доступа для больших наборов данных

Основываясь на вашем размещенном SQL-запросе, у вас есть несколько возможностей для оптимизации и ускорения производительности.

 SELECT t2.Loc, t2.ABCByPick, t2.Planner, t2.DmdUnit, ROUND(t2.MASE,2) AS MASE, ROUND(t2.AFAR,2) AS AFAR FROM TempTableAnalysis AS t2 ... 

Это первая часть, где TempTableAnalysis – многотысячный подзапрос записей. Если вы хотите выжать немного больше производительности из-за использования этой таблицы «Temp», не используйте ее как динамический запрос (т. Е. Вычисляемый по требованию при каждом открытии запроса), попробуйте создать макрос, который подталкивает вывод в статическую таблицу:

Добавление данных подзапроса в статическую таблицу:

  1. Создайте объект QUERY и измените его тип на DELETE . Создайте его, чтобы удалить содержимое вашего «временного» объекта таблицы. Если вы предпочитаете использовать SQL, команда будет выглядеть так:

     DELETE My_Table.* FROM My_Table; 
  2. Создайте объект QUERY и измените его тип на APPEND . Создайте его, чтобы запросить все поля из вашего запроса, определенные оператором SQL этого OP. Опять же, SQL-версия этой задачи имеет следующий синтаксис:

     INSERT INTO StaticAnalysisTable ( ID, Loc, Item, AvgOfScaledError ) SELECT t1.ID, t1.Loc, t1.Item, t1.AvgOfScaledError FROM TempTableAnalysis as t1; 

    Следующим шагом будет автоматизация совокупности этой статической таблицы, и это необязательно. Это просто, однако, и сделает менее вероятным, что вы совершите ошибку, забыв «Обновить» и получить доступ к своей статической таблице, пока она имеет устаревшие данные … вызывая неточности в ваших результатах.

  3. Создайте макрос с двумя шагами. Каждый шаг будет иметь следующее определение: OPEN QUERY . Когда будет предложено открыть запрос, обратитесь к объектам, созданным в предыдущих двух шагах, в следующем порядке (важно): (1) DELETE Query: (your delete query name) затем (2) APPEND Query: (your append query name) .

Комментарии и предложения по SQL-запросам

Следующая часть опубликованного SQL-запроса может использовать некоторую помощь:

 ... WHERE t2.MASE IN ( SELECT TOP 10 t1.MASE FROM TempTableAnalysis AS t1 WHERE t1.ABCByPick = t2.ABCByPick ORDER BY t1.MASE DESC ) ORDER BY t2.ABCByPick, t2.MASE DESC; 
  1. Существует соединение по второму запросу, которое генерирует данные TOP-10 и самый внешний запрос, который коррелирует эти результаты с дополнительными MASE таблицы MASE . Это необязательно, если TempTableAnalysis.MASE представляет значение ключа.

  2. ORDER BY

    во внутреннем большинстве запросов не требуется, если только оно не предназначено для принудительного выбора каких-либо критериев отбора (как при использовании аналитических функций SQL), это не похоже на один из этих случаев. Заказ записей из больших наборов данных также является расточительным процессором и памятью.

EDIT: как аргумент встречной точки, предложение ORDER BY , используемое рядом с запросом TOP N, действительно имеет цель, но я все еще неясно, если это необходимо. Чтобы завершить обсуждение, другая SO-строка рассказывает о том, как выбрать Top 10 в Query Access .

  1. WHERE t2.MASE IN (...

    Вы можете испытывать блоки в производительности с очень большими операциями набора в списке. На сервере базы данных Oracle я обнаружил с другими разработчиками, что существует ограничение на количество дискретных элементов в операторе запроса в списке. Это значение было в тысячах … которые могут быть дополнительно ограничены на основе ресурсов сервера и базы данных.

    Рассмотрим использование SQL JOIN оператора. Место, где вы определяете объекты TABLE также может быть заполнено SQL-запросами с псевдонимами, известными как INLINE VIEWS . Поскольку вы используете ACCESS, если встроенный просмотр не работает напрямую, просто определите еще один объект ACCESS QUERY и укажите его в своем окончательном запросе, как если бы это была таблица …

    Возможная переписка с конечной частью исходного запроса:

     SELECT t2.Loc, t2.ABCByPick, t2.Planner, ... FROM TempTableAnalysis AS t2, (SELECT TOP 10 t1.MASE, t1.ABCByPick FROM TempTableAnalysis AS t1) AS ttop WHERE t2.MASE = ttop.MASE AND t2.ABCByPick = ttop.ABCByPick ORDER BY t2.ABCByPick, t2.MASE DESC; 

Вам определенно нужно будет выполнить эти рекомендации и проверить достоверность выходных данных. Это представляет собой подходы к захвату некоторых «низко висящих фруктов» (простых предметов), которые вы можете использовать, чтобы ускорить ваш запрос и операции с отчетами.

Выводы и заключительные комментарии

В качестве фона для других читателей объект базы данных TempTableAnalysis не является статичной таблицей. Это результат дополнительного запроса, представленного в другом сообщении SO, запрашивающего помощь в Access TOP N Query . Запрос поступает из нескольких таблиц, приближающихся к 10 000 записей по размеру (каждый?).

Совет. Результат запроса в Access ALSO имеет потенциальное поведение, подобное таблицам. Вы можете вывести вывод в таблицу для соединения (как описано выше) или просто присоединиться к самому объекту запроса (осторожно, особенно, когда вы получаете «цепочки» нескольких операций запроса …)

Стратегия этого решения заключалась в следующем:

  1. Чтобы свести к минимуму количество поездок через один или несколько экземпляров этой очень большой таблицы.

  2. Предварительно обрабатывать и индексировать оптимизировать любые данные, которые в противном случае были бы «статичными» в течение всего времени его анализа.

  3. Для аудита и анализа кода SQL, используемого для получения окончательных результатов.

Определенно изучите Access MACROS. В сочетании с определением статических данных в ваших наборах данных вы можете разгрузить обработку сложных фоновых аналитических запросов, чтобы улучшить пользовательский интерфейс, когда они просматривают и запрашивают конечные результаты. Удачи!