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

#sql #sql_server

Мне нужно сделать выборку с таблиц со следующим условием:


  сделать запрос, который получает список всех продуктов, и цены на
  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
)


Все работает отлично, но хотелось бы как то красивее все написать.
Какие есть идеи?
    

Ответы

Ответ 1

Проблема не столько в запросе, сколько в схеме базы.

В таблице 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 и план. Все остальное - гадание.