Как улучшить SQL запрос

Мне нужно сделать выборку с таблиц со следующим условием:
сделать запрос, который получает список всех продуктов, и цены на 2013-03-01
Я написал вот такой скрипт:
select st.[ProductID], st.[ProductName], [Price].Price from ( select [Product].[ProductID], [ProductName], MAX(OnDate) as [OnDate] from [Product] inner join [dbo].[Price] on [Product].[ProductID] = [Price].[ProductID] where [OnDate] <= '2013-03-01' group by [Product].[ProductID], [ProductName] ) st inner join [dbo].[Price] on st.[ProductID] = [Price].[ProductID] and [Price].[OnDate] = st.[OnDate] order by [ProductID]
И структура таблиц:
create table Product ( ProductID int not null identity(1,1) primary key, ProductName varchar(255), Description varchar(max), Color varchar(15) ) create table Price ( ProductID int not null, OnDate datetime not null, Price int not null )
Все работает отлично, но хотелось бы как то красивее все написать. Какие есть идеи?

Ответ

Проблема не столько в запросе, сколько в схеме базы.
В таблице Price нет PK. Совсем. Единственный способ, которым SQL Server может выбрать что-то из таблицы без PK - это перечитать ее целиком. На большом объеме это будет тормозить вне зависимости от красоты запроса. Добавьте ключ - или составной (ProductID + OnDate, или какой-нибудь PriceID identity).
После этого посмотрите план запроса и добавляйте индексы по необходимости. Я бы предсказал

CREATE NONCLUSTERED INDEX [NonClusteredIndex-OnDate_Prod] ON [dbo].[Price] ( [OnDate] ASC, [ProductID] ASC )
что (на паре сотен строк) приведет к плану вида

т.е. хоть вы и упомянули Price дважды, реально данные из нее будут вычитаны один раз - при чтении конкретной цены.
Если очень хочется - можно переписать запрос с одним JOIN:

;WITH PricesBeforeDate as ( SELECT * from Price WHERE [OnDate] <= '2013-03-01' ), IndexedPrices as ( SELECT ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY OnDate DESC) AS Row, ProductID, Price FROM PricesBeforeDate ), LastPrices as ( SELECT * FROM IndexedPrices WHERE Row = 1 ) SELECT LastPrices.ProductID, ProductName, Price FROM LastPrices INNER JOIN Product on LastPrices.ProductID = Product.ProductID
этот запрос дает чуть меньше чтений на тех данных, что я у себя навбивал, но всегда стоит сравнить на реальных значениях:

видно что есть Sort, кушающий CPU. Это сортировка по ProductID и OnDate, вызванная тем, что я использовал PriceID в качестве ключа. Если использовать ключ по ProductID ASC, OnDate DESC, то сортировка исчезнет

ALTER TABLE dbo.Price ADD CONSTRAINT PK_Price_1 PRIMARY KEY CLUSTERED ( ProductID, OnDate DESC )
план:

Но в целом выбирать между разными вариантами запроса и разными индексами стоит на реальных данных. Включаете

SET Statistics io on SET Statistics time on
и смотрите output и план. Все остальное - гадание.