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

Я перефразировал предыдущий вопрос MS Access Проблема производительности запросов SQL Top N 2010 по группам (продолжение), так как я считаю, что контекст не был четко описан ранее. Ответ на мой предыдущий вопрос не дал лучший результат n по группе. Перефразированный вопрос является более общим. Теперь у меня все данные в одной таблице.

Вот моя ситуация: у меня есть таблица (Анализ), которая содержит продукты (Номер детали) различных категорий (Категория). Каждый продукт имеет цену (значение). Цель запроса — показать 10 продуктов с самой высокой ценой в каждой категории. Таблица содержит 15000 записей и будет продолжать расти.

Это запрос:

  SELECT 
    a.Location,
    a.Category,
    a.Partnumber
    a.Value

  FROM Analysis a

  WHERE a.Partnumber IN (
    SELECT TOP 10 aa.Partnumber
    FROM Analysis aa
    WHERE aa.Category = a.Category
    ORDER BY aa.Value DESC
  )


  ORDER BY 
    a.Category;

Вот мой вопрос: Мой текущий запрос работает с 1000 записями в таблице (время ответа 3 секунды). С 15000 записей запрос выполняется бесконечно долго. Как я могу перестроить запрос, чтобы значительно повысить производительность?

Ответ на мой предыдущий вопрос заключался в том, чтобы не использовать операцию в списке. Но это исключило функцию, позволяющую получить первые n записей по группам. Запрос дал первые n из всех записей.


sql performance ms-access-2010 query-performance
person Daniel    schedule 25.06.2014    source источник
comment
Вы можете попробовать использовать ранжирующий запрос, как показано здесь   -  person T I    schedule 25.06.2014

Ответы (1)


arrow_upward
1
arrow_downward

Для примера данных в таблице под названием [Анализ]

ID  Location   Category  Partnumber  Value
--  ---------  --------  ----------  -----
 1  here       cat1      part001         1
 2  there      cat1      part002         2
 3  wherever   cat1      part003         3
 4  someplace  cat2      part004         4
 5  nowhere    cat2      part005         5
 6  unknown    cat2      part006         6

"рейтинговый запрос"

SELECT 
    a1.ID,
    a1.Location,
    a1.Category,
    a1.Partnumber,
    a1.Value,
    COUNT(*) AS CategoryRank
FROM
    Analysis a1
    INNER JOIN
    Analysis a2
        ON a1.Category = a2.Category
            AND a1.Value <= a2.Value
GROUP BY
    a1.ID,
    a1.Location,
    a1.Category,
    a1.Partnumber,
    a1.Value

возвращается

ID  Location   Category  Partnumber  Value  CategoryRank
--  ---------  --------  ----------  -----  ------------
 1  here       cat1      part001         1             3
 2  there      cat1      part002         2             2
 3  wherever   cat1      part003         3             1
 4  someplace  cat2      part004         4             3
 5  nowhere    cat2      part005         5             2
 6  unknown    cat2      part006         6             1

поэтому, если вам нужны только 2 верхних элемента в каждой категории, просто оберните приведенный выше запрос в SELECT... WHERE

SELECT *
FROM
(
        SELECT 
            a1.ID,
            a1.Location,
            a1.Category,
            a1.Partnumber,
            a1.Value,
            COUNT(*) AS CategoryRank
        FROM
            Analysis a1
            INNER JOIN
            Analysis a2
                ON a1.Category = a2.Category
                    AND a1.Value <= a2.Value
        GROUP BY
            a1.ID,
            a1.Location,
            a1.Category,
            a1.Partnumber,
            a1.Value
) AS RankingQuery
WHERE CategoryRank <= 2
ORDER BY Category, CategoryRank

чтобы дать тебе

ID  Location  Category  Partnumber  Value  CategoryRank
--  --------  --------  ----------  -----  ------------
 3  wherever  cat1      part003         3             1
 2  there     cat1      part002         2             2
 6  unknown   cat2      part006         6             1
 5  nowhere   cat2      part005         5             2

Примечание. Убедитесь, что поля [Категория] и [Значение] проиндексированы для лучшей производительности.

person Gord Thompson    schedule 25.06.2014
comment
Я просто играл с собственным запросом внутреннего ранжирования, и он обрабатывает повторяющиеся значения шатким способом. Решение отличное, только небольшая оговорка с техникой в ​​целом. - person VBlades; 25.06.2014
comment
@VBlades Да, хорошая мысль. Дубликаты могут бросить ключ в вещи при использовании этого подхода. :( - person Gord Thompson; 25.06.2014
comment
Я протестировал предложение с ранжирующим запросом. Время ответа было таким же или даже хуже, чем на мой запрос. Подсказка с индексом очень помогла. Я проиндексировал [Категорию] и [Номер детали], благодаря чему время отклика значительно улучшилось. Я заметил, что чем меньше номеров деталей в категории, тем быстрее время отклика при том же общем количестве записей. Например. с 9 категориями и 16000 номерами деталей время отклика составляет 40 секунд. При 60 категориях и 16000 номеров деталей время отклика составляет 11 секунд. Поэтому на вопрос был дан ответ. СПАСИБО! - person Daniel; 26.06.2014
comment
@Daniel Рад слышать об улучшении. Да, распределение данных, безусловно, может повлиять на время выполнения, поэтому вам, вероятно, следует протестировать оба запроса, чтобы увидеть, какой из них лучше работает для вашего конкретного набора данных. Также обратите внимание, что [Value] также должен быть проиндексирован. - person Gord Thompson; 26.06.2014