Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проиндексировать диапазоны дат  [new]
SergASh
Member

Откуда:
Сообщений: 167
Привет всем!

Есть такая таблица
CREATE TABLE [dbo].[tblRelevanceInterval]
(
  RelevanceIntervalId TYP_ID IDENTITY
 ,MasterId TYP_ID -- TYP_ID это BIGINT NOT NULL
 ,Amount MONEY NOT NULL
 ,EffectiveFrom DATETIME NOT NULL
 ,ValidUntil DATETIME NOT NULL
 ,CONSTRAINT PK_RelevanceInterval
    PRIMARY KEY ( RelevanceIntervalId )
 ,CONSTRAINT CK_RelevanceInterval_ValidDateRange
    CHECK ( EffectiveFrom <= ValidUntil )
)
CREATE NONCLUSTERED INDEX IX_RelevanceInterval_ValidUntil
  ON [dbo].[tblRelevanceInterval]
(
  ValidUntil
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_RelevanceInterval_EffectiveFrom
  ON [dbo].[tblRelevanceInterval]
(
  EffectiveFrom
) ON [PRIMARY]


И запрос (упрощенно)
CREATE PROCEDURE prcTest
 @TargetDate DATETIME
AS
  SELECT *
    FROM tblRelevanceInterval
    WHERE @TargetDate BETWEEN EffectiveFrom AND ValidUntil


План показывает, что выполняется clustered index scan. Индексы по датам не применяются, а хотелось бы как-то использовать тот факт, что это по сути таблица истории цен, и для любой заданной даты после фильтрации останется процентов 5 записей, так что сканировать всю таблицу как-то неумно.

Посоветуйте как лучше проиндексировать.
5 авг 13, 16:11    [14665024]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
SergASh,
покрывающий индекс
5 авг 13, 16:13    [14665035]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
SergASh
Member

Откуда:
Сообщений: 167
Мистер Хенки,

Конкретней можно?
5 авг 13, 16:21    [14665063]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
SELECT *
Guest
Мистер Хенки
SergASh,
покрывающий индекс

в случае

SELECT *
FROM tblRelevanceInterval

сразу кластерный.
5 авг 13, 16:33    [14665127]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
SELECT *
Мистер Хенки
SergASh,
покрывающий индекс

в случае

SELECT *
FROM tblRelevanceInterval

сразу кластерный.

так может человек сразу и подумает зачем ему select *
5 авг 13, 16:34    [14665148]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
SergASh
Member

Откуда:
Сообщений: 167
Так а по какому полю или полям индекс-то предлагаете?
5 авг 13, 16:54    [14665267]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
temp-for-testing
Member

Откуда:
Сообщений: 384
SergASh,

А какие поля вам конкретно нужны?
Для запроса
SELECT Amount
    FROM tblRelevanceInterval
    WHERE @TargetDate BETWEEN EffectiveFrom AND ValidUntil

нужен такой индекс
CREATE NONCLUSTERED INDEX IX_RelevanceInterval_EffectiveFromValidUntilAmount
  ON [dbo].[tblRelevanceInterval] (EffectiveFrom, ValidUntil) INCLUDE (Amount)


Для запроса
SELECT *
    FROM tblRelevanceInterval
    WHERE @TargetDate BETWEEN EffectiveFrom AND ValidUntil

нужно в INCLUDE включить все столбцы таблицы.
5 авг 13, 17:10    [14665352]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
если предположить, что MasterId - это ID из таблицы dbo.tblMaster и для каждой MasterId диапазоны EffectiveFrom-ValidUntil не пересекаются, максимум соприкасаются.
то делаешь составной индекс по MasterID, EffectiveFrom

и запрос что-то вроде

select m.MasterID, x.EffectiveFrom
from dbo.tblMaster as m
outer apply( select top 1 r.EffectiveFrom
               from [dbo].[tblRelevanceInterval] as r
               where r.MasterID = m.ID 
                 and r.EffectiveFrom >= @TargetDate 
                 and r.ValidUntil > @TargetDate
                order by r.MasterID, r.EffectiveFrom ) as x
where x.EffectiveFrom is not null
6 авг 13, 01:40    [14667092]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
and r.EffectiveFrom <= @TargetDate
6 авг 13, 01:42    [14667093]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
еще

order by r.MasterID desc, r.EffectiveFrom desc

select m.MasterID, x.EffectiveFrom
from dbo.tblMaster as m
outer apply( select top 1 r.EffectiveFrom
               from [dbo].[tblRelevanceInterval] as r
               where r.MasterID = m.ID 
                 and r.EffectiveFrom <= @TargetDate 
                 and r.ValidUntil > @TargetDate
                order by r.MasterID desc, r.EffectiveFrom desc ) as x
where x.EffectiveFrom is not null
6 авг 13, 01:44    [14667098]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Exproment
Member

Откуда:
Сообщений: 416
SergASh, на самом деле только вы можете принять решенеи, какой именно индекс вам нужен. Покрывающий по всем столбцам таблицы обычно никому не нужен. Вы сами должны принять решение, сходя из перманентности ваших данных, нагрузки на них, скорости обработки и т.д.

Т.е. необходимо проанализировать все запросы к данной таблице в базе - через закешированные планы и XQuery.

В данном конкретном случае вам можно посоветовать только индекс с ключами по двум полям: EffectiveFrom, ValidUntil, и то надо анализировать остальные запросы, чтобы понять какое из полей должно быть левым в индексе. Или это должны быть два разных индекса. Насчет - же необходимости добавления include полей никто вам никто ничего сказать не сможет. Ибо каждый раз добавлять их ради одного запроса - умрете на операциях модификации(но если у вас данный очень перманентны, то почему бы и нет).
6 авг 13, 11:02    [14668018]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SergASh
это по сути таблица истории цен
Надо не путать логическое представление данных с физическим.
Физически намного эффективнее в таком виде:
USE tempdb
GO
CREATE TABLE dbo.TProduct (
	 ID	Int	IDENTITY
	 CONSTRAINT PK_TProduct PRIMARY KEY
	,Name	NVarChar(250)	NOT NULL
)
CREATE TABLE dbo.TProductPrice (
	 Product	Int
	 CONSTRAINT FK_TProductPrice REFERENCES  dbo.TProduct (ID)
	,[From]		Date
	,CONSTRAINT PK_TProductPrice PRIMARY KEY (
		 Product
		,[From]
	)
	,Amount		Money	    NULL
)
-- CREATE INDEX IX_TProductPrice_Date ON dbo.TProductPrice ([Date])
GO
CREATE FUNCTION dbo.fnProductPrice (
	 @Product	Int
	,@Date		Date	= NULL
) RETURNS TABLE AS RETURN
SELECT	Top(1) P.*
FROM	dbo.TProductPrice	P
WHERE	P.Product	 = @Product
AND	P.[From]	<= IsNull(@Date,GetDate())
ORDER BY P.[From] DESC
GO
INSERT	dbo.TProduct VALUES (N'A'),(N'B')
INSERT	dbo.TProductPrice VALUES
 (1,'20130801',10)
,(1,'20130820',12)
,(2,'20130810',5)
,(2,'20130920',NULL)
GO -- Тест
SELECT	*
FROM	dbo.TProduct			P OUTER APPLY
	dbo.fnProductPrice(P.ID,Default)A

SELECT	*
FROM	dbo.TProduct			P OUTER APPLY
	dbo.fnProductPrice(P.ID,'20130812')A

SELECT	*
FROM	dbo.TProduct			P OUTER APPLY
	dbo.fnProductPrice(P.ID,'20130901')A

SELECT	*
FROM	dbo.TProduct			P OUTER APPLY
	dbo.fnProductPrice(P.ID,'20131001')A
GO
DROP FUNCTION dbo.fnProductPrice;
DROP TABLE dbo.TProductPrice,dbo.TProduct;
6 авг 13, 11:20    [14668137]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
В дополнении к предыдущему посту:
В массовом порядке будут запросы на получении текущей стоимости.
Поэтому данный вариант лучше. А получение истории периодов с колонкой To/До - уже можно и под-запросом.

Но ещё лучше другой вариант: Хранить текущую стоимость явно.

Частенько тема подымается на форуме, при мне раз 20ть, вот последняя 14624760:
Mnior
Во вторых, надо понимать первую проблему диапазонов в скуле: Модель с двумя полями (From,To) нормально не оптимизируется, ибо индексы линейны, см подробности (12610105).
Т.е. поиск, аля: From >= X AND To < Y - вываливается в полу-скан.
В моём случае диапазон указывается по одному полю: From >= Y AND From < Y что сильно ограничивает скан (в основном до одного значения).
6 авг 13, 11:42    [14668313]     Ответить | Цитировать Сообщить модератору
 Re: Проиндексировать диапазоны дат  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
Exproment
В данном конкретном случае вам можно посоветовать только индекс с ключами по двум полям: EffectiveFrom, ValidUntil

Составной индекс после поля датавремя (тоже самое для real) не эффективен, т.е. индекс EffectiveFrom, ValidUntil будет аналогичен индексу EffectiveFrom include(ValidUntil)
6 авг 13, 11:45    [14668334]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить