Блог

У меня есть требование создать представление, и бизнес-сценарий описан ниже

Предположим, у меня есть таблица Products (вся информация о продукте) и Settings (настройки для страны / штата / города).

Теперь мне нужно создать представление, которое предоставляет информацию о продукте, учитывая настройки, возможно, города / штаты / страна будут иметь собственные настройки. Разработка представления Это означает, что сначала мне нужно проверить 1. любой город имеет там пользовательские настройки, затем вывести эти записи, ОБЪЕДИНИВ ВСЕ 2. в любом штате есть пользовательские настройки, затем вывести эти записи, исключив города в этом штате на шаге 1, ОБЪЕДИНИВ ВСЕ 3. в любой стране есть пользовательские настройки или нет, затем вывести эти записи, исключив записи городов и штатов на шаге 1 и шаге 2

Это дизайн, о котором я думал, есть ли что-то неправильное в дизайне?

Повышение производительности При таком существующем дизайне выполнение запроса без каких-либо индексов в view и базовых таблицах занимает 5 минут.

Теперь, какой для меня лучший вариант для повышения производительности. Создание индексированных представлений или создание индекса для базовых таблиц? какой из них помогает мне выполнить запрос за считанные секунды 🙂

Примеры данных

Таблица продуктов

Таблица настроек

Ожидаемый результат

Я не могу понять, почему отображается ваш результат (P2 — Blue). Я переписал ваши образцы как SQL и создал то, что, как я думал, вы хотели (в ожидании ожидаемого результата), а мой выдает только одну строку (P1 — Red)

 create table dbo.Product (
    ProductID int not null,
    Name char(2) not null,
    StateId char(2) not null,
    CityId char(2) not null,
    CountryId char(2) not null,
    Price int not null,
    Colour varchar(10) not null,
    constraint PK_Product PRIMARY KEY (ProductID)
)
go
insert into dbo.Product (ProductID,Name,StateId,CityId,CountryId,Price,Colour)
select 1,'P1','S1','C1','C1',150,'Red' union all
select 2,'P2','S2','C2','C1',100,'Blue' union all
select 3,'P3','S1','C3','C1',200,'Green'
go
create table dbo.Settings (
    SettingsID int not null,
    StateId char(2) null,
    CityId char(2) null,
    CountryId char(2) null,
    MaxPrice int not null,
    MinPrice int not null,
    constraint PK_Settings PRIMARY KEY (SettingsID)
)
go
insert into dbo.Settings (SettingsID,StateId,CityId,CountryId,MaxPrice,MinPrice)
select 1,null,null,'C1',1000,150 union all
select 2,'S1',null,'C1',2000,100 union all
select 3,'S1','C3','C1',3000,300
go
  

А теперь собственно представление:

 create view dbo.Products_Filtered
with schemabinding
as
    with MatchedSettings as (
    select p.ProductID,MAX(MinPrice) as MinPrice,MIN(MaxPrice) as MaxPrice
    from
        dbo.Product p
            inner join
        dbo.Settings s
            on
                (p.CountryId = s.CountryId or s.CountryId is null) and
                (p.CityId = s.CityId or s.CityId is null) and
                (p.StateId = s.StateId or s.StateId is null)
    group by
        p.ProductID
    )
    select
        p.ProductID,p.Name,p.CityID,p.StateId,p.CountryId,p.Price,p.Colour
    from
        dbo.Product p
            inner join
        MatchedSettings ms
            on
                p.ProductID = ms.ProductID and
                p.Price between ms.MinPrice and ms.MaxPrice
  

Что я сделал, так это объединил все применимые настройки, а затем предположил, что мы применили наиболее ограничительные настройки (поэтому возьмите указанную МАКСИМАЛЬНУЮ минимальную цену и минимальную максимальную цену).

При использовании этих правил строка (P2 — Blue) исключается, поскольку единственным применимым параметром является параметр 1, минимальная цена которого равна 150.

Если я изменю это, чтобы мы попытались быть как можно более всеобъемлющими (МИНИМАЛЬНАЯ цена и МАКСИМАЛЬНАЯ цена maxPrice), то это вернет (P1 — красный) и (P3 — зеленый) — но все равно не (P2 — синий)