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

Я разрабатываю веб-приложение магазина. Когда потенциальный клиент просматривает продукт на веб-сайте, я хотел бы предложить набор подобных продуктов из базы данных автоматически (vs требует, чтобы человек явно вводил данные / сопоставления данных о продуктах).

Фактически, когда вы думаете об этом, большинство баз данных магазинов уже имеют множество доступных данных о сходстве. В моем случае Products могут быть:

  • сопоставляются с Manufacturer (aka Brand ),
  • сопоставлены с одним или несколькими Categories и
  • сопоставлены с одним или несколькими Tags (aka Keywords ).

Фрагмент Datamodel для витрины магазина

Рассчитывая количество общих атрибутов между продуктом и всеми другими, вы можете рассчитать «SimilarityScore» для сравнения других продуктов с тем, который просматривается клиентом. Вот моя первоначальная реализация прототипа:

 ;WITH ProductsRelatedByTags (ProductId, NumberOfRelations) AS ( SELECT t2.ProductId, COUNT(t2.TagId) FROM ProductTagMappings AS t1 INNER JOIN ProductTagMappings AS t2 ON t1.TagId = t2.TagId AND t2.ProductId != t1.ProductId WHERE t1.ProductId = '22D6059C-D981-4A97-8F7B-A25A0138B3F4' GROUP BY t2.ProductId ), ProductsRelatedByCategories (ProductId, NumberOfRelations) AS ( SELECT t2.ProductId, COUNT(t2.CategoryId) FROM ProductCategoryMappings AS t1 INNER JOIN ProductCategoryMappings AS t2 ON t1.CategoryId = t2.CategoryId AND t2.ProductId != t1.ProductId WHERE t1.ProductId = '22D6059C-D981-4A97-8F7B-A25A0138B3F4' GROUP BY t2.ProductId ) SELECT prbt.ProductId AS ProductId ,IsNull(prbt.NumberOfRelations, 0) AS TagsInCommon ,IsNull(prbc.NumberOfRelations, 0) AS CategoriesInCommon ,CASE WHEN SimilarProduct.ManufacturerId = SourceProduct.ManufacturerId THEN 1 ELSE 0 END as SameManufacturer ,CASE WHEN SimilarProduct.ManufacturerId = SourceProduct.ManufacturerId THEN IsNull(prbt.NumberOfRelations, 0) + IsNull(prbc.NumberOfRelations, 0) + 1 ELSE IsNull(prbt.NumberOfRelations, 0) + IsNull(prbc.NumberOfRelations, 0) END as SimilarityScore FROM Products AS SourceProduct, Products AS SimilarProduct INNER JOIN ProductsRelatedByTags prbt ON prbt.ProductId = SimilarProduct.Id FULL OUTER JOIN ProductsRelatedByCategories prbc ON prbt.ProductId = prbc.ProductId WHERE SourceProduct.Id = '22D6059C-D981-4A97-8F7B-A25A0138B3F4' 

что приводит к следующим данным:

 ProductId TagsInCommon CategoriesInCommon SameManufacturer SimilarityScore ------------------------------------ ------------ ------------------ ---------------- --------------- 6416C19D-BA4F-4AE6-AB75-A25A0138B3A5 1 0 0 1 77B2ECC0-E2EB-4C1B-A1E1-A25A0138BA19 1 0 0 1 2D83276E-40CC-44D0-9DDF-A25A0138BE14 2 1 1 4 E036BFE0-BBB5-450C-858C-A25A0138C21C 3 0 0 3 

Я не являюсь гуру производительности SQL, поэтому у меня есть следующие вопросы для вас:

  • Являются ли общие / табличные выражения s (CTE) подходящими / оптимальными в этом случае использования? (Они, похоже, облегчают чтение / выполнение SQL). Есть ли способ сохранить соединение там где-нибудь с учетом представленной выше модели?

а также

  • Будет ли это хорошим кандидатом для индексированного представления (для стойкости) или это добавит чрезмерные затраты на изменения исходных данных? В этом случае я сделаю это хранимой процедурой, которая обновляет физическую таблицу SimilarProductMappings для любого данного продукта.

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

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

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

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

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

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

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

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

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

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

 ;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' 
  • Ошибка создания базы данных SQL
  • Сравнение производительности индексированных представлений и хранимых процедур в SQL Server
  • Виды в представлениях - что лучше всего
  • Просмотр vs Table в SQL Server
  • Несколько SQL-представлений, одна точка определения, как?
  • Как объединить строки в одну строку в SQL?
  • Использование COALESCE в представлении SQL
  • Могу ли я динамически вызывать хранимую процедуру из представления?
  • SQL - CTE vs VIEW
  • Проверьте, существует ли представление sql, а затем создайте его динамически, прежде чем изменять его.
  • Структура SQL EAV с индексированными представлениями
  • Interesting Posts

    Как добавить идентификатор в столбец SQL Server?

    Как добавить описания дочерних элементов в родительский заголовок в таблице формата дерева SQL Server

    Упорядочить по SQL

    Простой способ объединения многих таблиц с похожими именами в SSMS?

    Как эффективно отображать записи из двух таблиц в порядке их создания

    быстрое обновление с помощью sql

    Как добавить данные в таблицу SQL Server с помощью первичного ключа IDENTITY, используя функцию sqlSave () в R?

    Как использовать десятичный тип данных

    Перенос базы данных SQL Server на новый сервер

    Службы интеграции SQL Server – добавление параметра в оператор SQL

    Проверьте, обновлено ли значение поля при запуске SQL-сервера

    Как объединить две таблицы, распределяющие Проданные суммы vs Спрос без циклов / курсора

    Выбирать оператор ожидает даже после применения NOLOCK

    Апостроф вызывает проблему с запросом

    Получение информации об объектах

    Давайте будем гением компьютера.