У меня есть представление sql, которое я использую для получения данных. Допустим, это большой список продуктов, которые связаны с покупателями, которые их купили. Представление должно возвращать только одну строку для каждого продукта, независимо от количества клиентов, с которыми оно связано. Для этого я использую функцию row_number. (Этот пример упрощен, общая ситуация представляет собой запрос, в котором должна возвращаться только одна строка для каждого уникального значения некоторого столбца X. Какая строка возвращается, не имеет значения)
CREATE VIEW productView AS
SELECT * FROM
(SELECT
Row_number() OVER(PARTITION BY products.Id ORDER BY products.Id) AS product_numbering,
customer.Id
//various other columns
FROM products
LEFT OUTER JOIN customer ON customer.productId = prodcut.Id
//various other joins
) as temp
WHERE temp.prodcut_numbering = 1
Теперь допустим, что общее количество строк в этом представлении это ~ 1 миллион, а выполнение select * из productView занимает 10 секунд. Выполнение запроса, такого как select * from productView, где productID = 10, занимает такое же количество времени. Я считаю, что это происходит потому, что запрос оценивается следующим образом
SELECT * FROM
(SELECT
Row_number() OVER(PARTITION BY products.Id ORDER BY products.Id) AS product_numbering,
customer.Id
//various other columns
FROM products
LEFT OUTER JOIN customer ON customer.productId = prodcut.Id
//various other joins
) as temp
WHERE prodcut_numbering = 1 and prodcut.Id = 10
. Я думаю, что это приводит к тому, что внутренний подзапрос каждый раз оценивается полностью.В идеале я бы хотел использовать что-то вроде следующих строк
SELECT
Row_number() OVER(PARTITION BY products.productID ORDER BY products.productID) AS product_numbering,
customer.id
//various other columns
FROM products
LEFT OUTER JOIN customer ON customer.productId = prodcut.Id
//various other joins
WHERE prodcut_numbering = 1
Но, похоже, это недопустимо. Есть ли способ сделать что-то подобное?
РЕДАКТИРОВАТЬ -
После долгих экспериментов настоящая проблема, с которой я столкнулся, заключается в том, как заставить соединение возвращать ровно 1 строку. Я попытался использовать внешнюю аппликацию, как предложено ниже. Пример кода.
CREATE TABLE Products (id int not null PRIMARY KEY)
CREATE TABLE Customers (
id int not null PRIMARY KEY,
productId int not null,
value varchar(20) NOT NULL)
declare @count int = 1
while @count <= 150000
begin
insert into Customers (id, productID, value)
values (@count,@count/2, 'Value ' + cast(@count/2 as varchar))
insert into Products (id)
values (@count)
SET @count = @count + 1
end
CREATE NONCLUSTERED INDEX productId ON Customers (productID ASC)
С приведенным выше набором примеров запрос «получить все» ниже
select * from Products
outer apply (select top 1 *
from Customers
where Products.id = Customers.productID) Customers
занимает ~ 1000 мсек. Добавление явного условия:
select * from Products
outer apply (select top 1 *
from Customers
where Products.id = Customers.productID) Customers
where Customers.value = 'Value 45872'
Занимает идентичное количество времени. Эти 1000 мс для довольно простого запроса уже слишком много и масштабируются неправильно (вверх) при добавлении дополнительных аналогичных объединений.