Оптимизация модели данных каталога товаров

Тема открыта по предложению из поста: ссылка.

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

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

Всё это работает довольно шустро (доли секунды) для базы в 500 тыс. наименований, примеры из минусов - в услугах адвоката можно задать единицу измерения и фасовку, однако у абстрактной Lamborgini Gallaro не ввести ни объём двигателя, ни тип кузова - всё это выносится в наименование товара.

Другое дело - цена. И именно здесь начинаются тормоза. Дело в том, что товар может обладать несколькими ценами. Как минимум, розничной и оптовой. Для некоего замысловатого удобства всё это вынесено в отдельную таблицу цены: таблица_товара -> таблица_цены <- таблица_валюты (схема один -> многие).

Чтобы выбрать десять товаров из таблицы товаров, потребуется пару раз (на каждый товар) пройтись по таблице цены с намерением присоединить сначала розничную, а затем и оптовую цену:

Код (Text):

1.  
2.     SELECT name,
3.         pcr.value AS retail, --# retail price
4.         pct.value AS trade --# trade price
5.  
6.         FROM product AS pt
7.  
8.             LEFT JOIN price AS pcr --# PriCe Retail
9.                 ON pcr.id_product = pt.id_product AND pcr.type = 1
10.  
11.             LEFT JOIN price AS pct --# PriCe Trade
12.                 ON pct.id_product = pt.id_product AND pct.type = 2

Казалось бы, ничего сложного. Однако без валюты цена никуда не годится и, следовательно, при её извлечении нужно обратиться к таблице валюты:

Код (Text):

1.  
2.     SELECT name,
3.         pcr.value AS retail, rcc.name AS rName, --# retail price with it's currency
4.         pct.value AS trade, tcc.name AS tName --# trade price with it's currency
5.  
6.         FROM product AS pt
7.  
8.             LEFT JOIN price AS pcr --# PriCe Retail
9.                 ON pcr.id_product = pt.id_product AND pcr.type = 1
10.             LEFT JOIN currency AS rcc --# Retail CurrenCy
11.                 ON pcr.id_currency = rcc.id_currency
12.  
13.             LEFT JOIN price AS pct --# PriCe Trade
14.                 ON pct.id_product = pt.id_product AND pct.type = 2
15.             LEFT JOIN currency AS tcc --# Trade CurrenCy
16.                 ON pct.id_currency = tcc.id_currency

И всё было бы замечательно, однако в таблице цен хранятся версии: при изменении цены оператором командой INSERT вставляется новая запись, отмеченная датой на момент создания. И именно эту запись нужно привязать к товару. Самое логичное - SELECT ... WHERE MAX(stale), но подобная конструкция не работает. Следовательно, нужно пойти на хитрость и набросать табличную функцию:

Код (Text):

1.  
2.     CREATE FUNCTION dbo.func_getPrice(@productId int, @priceType tinyint)
3.     RETURNS TABLE AS
4.         RETURN
5.             SELECT TOP(1) pc.value, cc.name AS currency
6.                 FROM price AS pc
7.                     JOIN currency AS cc
8.                         ON cc.id_currency = pc.id_currency
9.                 WHERE pc.id_price = @productId AND pc.type = @priceType
10.             ORDER BY pc.stale DESC --# последняя дата

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

Код (Text):

1.  
2.     SELECT pt.name,
3.         rp.value AS retail, rp.name AS rName, --# retail price with it's currency
4.         tp.value AS trade, tp.name AS tName --# trade price with it's currency
5.  
6.         FROM product AS pt --# ProducT
7.             OUTER APPLY mi_func_getPrice(pt.id_price, 1) AS rp --# Retail Price
8.             OUTER APPLY mi_func_getPrice(pt.id_price, 2) AS tp --# Trade Price

С вводом в интерфейс переключателя «розница/опт» на уровне PHP, остаётся один CROSS APPLY (цена должна обязательно присутствовать, в отличие от OUTER). В итоге всё это работает в районе 0.3-0.4 сек. Тяжеловато при том, что другие модули решают все свои дела за 12-40 мс.

 

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

Относительно WHERE IN ( , , , ) - мысль интересная и неожиданная. Попробую разделить, как подберусь к оптимизации после основных задач по проекту (денёк-другой). Спасибо за наводку