Xpoint
   [напомнить пароль]

Оптимизация процедуры - фильтрованный список

Метки: [без меток]
2011-06-21 14:21:27 [обр] Alexey V.Zelenin(0/8)[досье]

Итак, для простоты допустим, что имеются две таблицы.

Products

FieldType
ProductIdint
CategoryIdint
ManufacturerIdint
Pricemoney

ProductAttributes

FieldType
ProductIdint
ValueIdint

В процедуру передаётся CategoryId, ManufacturerId, Min Price, Max Price и некий набор атрибутов. Требуется выбрать все продукты, которые удовлетворяют условиям - имеют нужные categoryId, manufacturerId, цена попадает в интервал между Min/Max и для каждого продукта должны быть записи в таблице ProductAttributes для всех атрибутов из набора.

Выборка по всем параметрам кроме атрибутов — достаточно несложная, а вот с атрибутами - закавыка.

Набор атрибутов собирается на уровне апликейшена, так что его можно варьировать как угодно. В данный момент используется следующая схема: апликейшен имеет набор ValueId, которые требуются и из них "вручную" склеивается строка типа

inner join ProductAttributes pa23
on pa23.productId = products.productId
and pa23.valueId = 23

повторяющаяся для каждого из необходимых атрибутов. Эта склеенная строка передаётся в процедуру, где происходит вызов примерно следующий:

exec('
select ProductId
from products ' + @filters)

Ну и в результате получается

select ProductId
from products 
inner join ProductAttributes pa23
  on pa23.productId = products.productId
     and pa23.valueId = 40
inner join ProductAttributes pa40
  on pa40.productId = products.productId
     and pa40.valueId = 40

Я не знаю, в чём именно проблема, но на таблицах products - 1538 записей и productAttributes - 7192 записей, процедура выполняется несколько секунд. Профайлер утверждает, что львиная часть времени уходит на поиск в кластерном индексе по полю ValueId. Меж тем, у таблицы явно указан вот такой индекс

CREATE NONCLUSTERED INDEX [IDX_VALUE_ID] ON [dbo].[productAttributes] 
(
   [value_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Я подозреваю, что проблема в том, что селект вызывается через Exec, так что сервер не может заранее выбрать, какие где индексы использовать.

Так что проблема: нужно ускорить этот процесс. Может быть можно как-то побороть тормоза в "exec", может быть можно сделать этот селект каким-то другим способом. Количество атрибутов, которые будут использоваться, заранее неизвестно.

PS: я пробовал передавать в процедуру в строке список айдишников, которые потом расклеивались и записывались во временную таблицу. Затем сохранять в другую временную таблицу все productId и шаг за шагом удалять из неё записи, для которых не находится пара в таблице productAttributes. Но это получается ещё медленнее.

спустя 4 часа 49 минут [обр] Филипп Ткачев(0/115)[досье]
А нельзя ли на уровне приложения список из конкретных value объединить в набор и потом производить выборку?
select * from Products
inner join ProductAttributes on  (Products.ProductId = ProductAttributes.ProductId  )
where ProductAttributes.ValueId IN (23,40, и так далее)
спустя 2 минуты [обр] Филипп Ткачев(0/115)[досье]
Т.е. я предлагаю конструировать запрос на уровне бизнес-логики, а не на уровне СУБД.
спустя 57 минут [обр] Alexey V.Zelenin(0/8)[досье]
Насколько я понимаю, такой вариант будет возвращать все продукты, для которых ValueId совпадает хотя бы с одним значением из списка. А мне нужно совпадение всех значений. То бишь AND, а не OR
спустя 2 часа 2 минуты [обр] Евгений Седов aka KPbIC(0/187)[досье]
сообщение промодерировано
SELECT
    ProductId,
    MAX(rn) AS maxrn
FROM
    (
        SELECT DISTINCT
            ProductAttributes.ProductId,
            ValueId,
            ROW_NUMBER() OVER (PARTITION BY ProductAttributes.ProductId) AS rn
        FROM
                ProductAttributes
            JOIN
                Products
                    ON ProductAttributes.ProductId = Products.ProductId
        WHERE
                ValueId IN(23, 40)
            AND
                price > 1000
    ) AS precondition
GROUP BY
    ProductId
HAVING
    MAX(rn) = количество_атрибутов_в_наборе;
спустя 1 час 11 минут [обр] Thirteensmay(0/157)[досье]
select productid
from product p
where not exists (select valueid
                  from (select 23 as valueid union select 40) vid
                  where valueid not in (select valueid
                                        from productattribute
                                        where productid = p.productid))
and price > 1000
Но есть подозрение что это может быть медленнее варианта Евгения.
MSSQL живьем не разу не видел. Алексей, если вас не затруднит, донесите пожалуйста результаты, интересно ;)
спустя 1 минуту [обр] Alexey V.Zelenin(0/8)[досье]
Оба варианта буду пробовать, погоняю вживую с профайлером, тогда расскажу. Вариант Евгения мне пока сложновато даже осознать :)
спустя 22 минуты [обр] Thirteensmay(0/157)[досье]
ROW_NUMBER() проставляет номера строк, в пределах (PARTITION) одного продукта, тупо начиная с 1 и до того сколько их там подпадет под условие IN. Ну а дальше стандартная группировка по продукту, вычисление максимального номера строки в каждом продукте, и HAVING отсечение тех групп (продуктов) у которых строк получилось меньше чем должно быть по условию IN, т.е. тех которые это условие выполнили не полностью. ROW_NUMBER() это оконная (аналитическая) функция, их много, поддерживаются в нормальных СУБД, и весьма полезны, ROW_NUMBER() пожалуй самая полезная, изучите не пожалеете ;)
спустя 1 час 40 минут [обр] Alexey V.Zelenin(0/8)[досье]

Ну что. По первому варианту после всех добавочных действий (разборка строки фильтров, заполнение двух промежуточных таблиц и финальный селект с джойном ещё нескольких таблиц), получаем Total execution time (Average): 81.000

Для второго запроса - чуть-чуть побольше. Чуть попозже погоняю с более наворочеными и сложными данными.

спустя 13 минут [обр] Евгений Седов aka KPbIC(0/187)[досье]
Без указания единиц измерения трудно понять хорошо это или плохо.
спустя 1 минуту [обр] Alexey V.Zelenin(0/8)[досье]
Евгений Седов aka KPbIC[досье]
А в каких единицах Management Studio показывает результаты "Client Statistics"? Судя по субъективным ощущениям, это милисекунды.
спустя 2 минуты [обр] Alexey V.Zelenin(0/8)[досье]
В любом случае спасибо. Похоже, оба способа работают как мне требовалось.
спустя 1 минуту [обр] Евгений Седов aka KPbIC(0/187)[досье]
Alexey V.Zelenin[досье] Ну, вы нашли у кого спросить...
спустя 48 секунд [обр] Alexey V.Zelenin(0/8)[досье]
Евгений Седов aka KPbIC[досье]
Прошу прощения, это к чему относилось? ):
спустя 10 минут [обр] Евгений Седов aka KPbIC(0/187)[досье]
сообщение промодерировано
Management Studio, Client Statistics... Я эти выражения сегодня первый раз услышал. Как уже было сказано, далеко не все видели MSSQL Server в жизни. Особенно под Linux.
спустя 20 минут [обр] Alexey V.Zelenin(0/8)[досье]
Евгений Седов aka KPbIC[досье]
Ага, так стало понятнее. Просто я рассчитывал, что раз раздел посвящён MS SQL, то народ как-то в курсе :) В любом случае вопрос с моей стороны был вполне даже риторический.
спустя 6 минут [обр] Евгений Седов aka KPbIC(0/187)[досье]
Вопрос про единицы измерения остается актуальным. Если почти одна десятая секунды, IMHO, это очень много.
спустя 19 дней [обр] Alexey V.Zelenin(0/8)[досье]
Единицы - милисекунды. А десятая доля секунды - может и много, но реально скорость в десятки раз больше, чем было. Правда, после некоторых манипуляций с индексами, старый запрос тоже стал работыть быстрее, но Ваш всё равно по производительности остаётся вне конкуренции.
Powered by POEM™ Engine Copyright © 2002-2005