Веб-сайт торговой марки ECommerce: поиск программных продуктов

Ответ 1

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

• CTEs и производные таблицы - это синтаксический сахар. Это не имеет разницы в производительности. Единственное преимущество в их использовании заключается в том, что вы можете повторно использовать их вместо копирования/вставки/ввода производной таблицы. Однако вы не используете их повторно в этом случае, поэтому это зависит от вас.

• Индексированные представления. Имейте в виду, что индексы в представлениях действуют как индексы в таблице (таблицах) с небольшим исключением. Представьте себе, что другая таблица создается для вашего конкретного запроса/представления и хранится на диске для более быстрого поиска. Когда базовые данные изменяются, эти индексы должны обновляться. Да, это может создать огромное влияние на ресурсы. В общем, я бы предпочел, чтобы кто-то написал запрос, который использует индексы в базовой таблице, и если им нужно больше индексов для определенной цели, то посмотрите на это подробно, а не на целостность в представлении с несколькими таблицами. Это намного проще в обслуживании и намного легче понять, почему ваш CRUD занимает больше времени, чем ожидалось. В индексированном представлении нет ничего неправильного. Но будьте очень осторожны, добавив это в модель базы данных приложения, подобную этой, из-за сложности таблиц, которые обновлены/вставлены/удалены. Большинство наиболее подходящих применений для индексированного представления относятся к хранилищу данных. Независимо от того, не помещайте индекс в представление, не понимая, что он будет делать с таблицами для CRUD (создавать, читать, обновлять, удалять) операции. И в CRM или приложении поддержки типа базы данных я бы держался подальше от них по большей части, если нет статической потребности, и это не влияет на производительность.

Прочтите эту статью: http://technet.microsoft.com/en-us/library/ms187864(v=sql.105).aspx

Обратите внимание на 3/4 пути вниз по странице, на которой он говорит о том, где НЕ использовать его, и я думаю, что ваш случай соответствует 4/5 сценариям, в которых вы НЕ должны его использовать.

• Что касается спасительных объединений... имейте в виду, что FULL OUTER объединяет одно из худших нарушителей эффективности. Мне кажется, единственная причина, по которой у вас есть, заключается в том, что вы не включаете производителя в ваши CTE. Вы можете просто включить его в свои CTE, а затем объединить количество совпадений cat/tag в последнем запросе, который вытаскивает его вместе, чтобы получить ваш результат. Таким образом, у вас есть только два левых внешних соединения (по одному на каждый CTE), а затем суммируйте два счета вместе и группируйте их по одному изготовителю (case case), productId и т.д.

• Наконец... Я бы подумал о том, чтобы разместить все это в де-нормированной таблице или, возможно, даже в кубе, где он был предварительно рассчитан. Давайте рассмотрим несколько вещей о вашем требовании: а. Нужен ли показатель корреляции для продуктов? Если да, то почему? Это не является критическим моментом при добавлении/удалении новых продуктов. Любой, кто говорит, что он должен жить, вероятно, на самом деле не означает этого. б. Скорость поиска. Я мог бы переписать ваш запрос, используя временные таблицы, убедиться, что индексы правильные и т.д., И придумать достаточно быстрый запрос в хранимой процедуре. Но я все еще собираю данные из БД, которые будут отображаться на каждом фронте магазина каждый раз, когда страница загружается. Если данные предварительно рассчитаны и хранятся в отдельной таблице продуктов и баллов для каждого продукта и индексируются по продукту, поиск будет очень быстрым. Вы могли бы перегружать и перезагружать таблицу в ETL почасово, ежечасно/независимо и не должны беспокоиться о поддержании индексов, которые перестраиваются каждый раз. Конечно, если ваш магазин находится на 24/7/365, вам нужно будет написать код на стороне базы данных, чтобы беспокоиться об управлении версиями, чтобы ваше приложение никогда не ожидало, если db находится в середине пересчета.

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

Надеюсь, что все это поможет.

Ответ 2

Как насчет несколько иного подхода?

;WITH ProductFindings (ProductId, NbrTags, NbrCategories)
AS
(
    SELECT  t2.ProductId, COUNT(t2.TagId), 0
    FROM    ProductTagMappings AS t1 
    INNER JOIN
            ProductTagMappings AS t2  ON t1.TagId      = t2.TagId
                                     AND t1.ProductId != t2.ProductId
    WHERE   t1.ProductId = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'
    GROUP BY t2.ProductId

  UNION ALL

    SELECT  c2.ProductId, 0, COUNT(c2.CategoryId)
    FROM    ProductCategoryMappings AS c1 
    INNER JOIN
            ProductCategoryMappings AS c2  ON c1.CategoryId = c2.CategoryId
                                          AND c1.ProductId != c2.ProductId
    WHERE c1.ProductId = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'
    GROUP BY c2.ProductId

), ProductTally (ProductId, TotTags, TotCategories) as
(
   SELECT ProductID, sum(NbrTags), sum(NbrCategories)
   FROM     ProductFindings
   GROUP BY ProductID
)
SELECT  Tot.ProductId      AS ProductId
       ,Tot.TotTags        AS TagsInCommon
       ,Tot.TotCategories  AS CategoriesInCommon
       ,CASE WHEN SimilarProduct.ManufacturerId = SourceProduct.ManufacturerId
               THEN 1
               ELSE 0 
         END               as SameManufacturer
       ,CASE WHEN SimilarProduct.ManufacturerId = SourceProduct.ManufacturerId 
               THEN 1
               ELSE 0
         END + Tot.TotTags + Tot.TotCategories
                           as SimilarityScore
FROM    ProductTally as Tot
INNER JOIN  Products     AS SimilarProduct   ON Tot.ProductID = SimilarProduct.Id 
INNER JOIN  Products     AS SourceProduct    ON SourceProduct.Id = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'