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

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

подсобите советом =)
пример, приближенный к жизни:
есть таблица заказов, в ней есть атрибуты - дата заведения заказа и дата его доставки, они могут совпадать, могут не совпадать, но всегда второе поле больше или равно первому.
При этом заказов, которые доставляются через несколько дней после заведения, прилично меньше, чем тех, которые доставляются сразу же.

возникла потребность найти все заказы, которые в определенном временном промежутке уже были созданы, но не были доставлены.
create table ord(OrderID bigint identity, OrderDate datetime, DeliveryDate datetime, Client int)
declare @start datetime = '2009'

;with a as
(
	select dateadd(day, cast(rand(CHECKSUM(newid()))*1825 as int),  @start) OrderDate, v.number
	from master..spt_values v, master..spt_values v1 , (select top(5) 1 number from master..spt_values) v3
	where v.type = 'P' and v1.type = 'P'
),
a1 as
(
	select OrderDate, 
		dateadd(day, cast(case	when a.number%10=0 and a.number%100!=0 then 11*rand(CHECKSUM(newid())) 
								when a.number%100=0 then 101*rand(CHECKSUM(newid())) 
								else 0 
							end as int), orderdate) as DeliveryDate
	from a
)
insert into ord
select OrderDate, DeliveryDate, cast(rand(CHECKSUM(newid()))*500 as int) from a1
go 2

create clustered index IX_c on ord(OrderID)
create index IX1 on ord(DeliveryDate, Client)


При имещихся индексах и периоде, близком к "концу" всех данных получаю отличную ситуацию - маленький поиск по некластерному индексу и лукап в кластерный за недостающими полями.
select Client, OrderID, OrderDate, DeliveryDate from #ord 
where OrderDate < '20140103' and DeliveryDate > '20140105'

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [dbo].[ord].[OrderID], [Expr1007]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([dbo].[ord].[IX1]), SEEK:([dbo].[ord].[DeliveryDate] > '2014-01-05 00:00:00.000'), WHERE:([dbo].[ord].[Client]=(300)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dbo].[ord].[IX_c]), SEEK:([dbo].[ord].[OrderID]=[dbo].[ord].[OrderID] AND [Uniq1002]=[Uniq1002]), WHERE:([dbo].[ord].[OrderDate]<='2014-01-05 00:00:00.000') LOOKUP ORDERED FORWARD)

При поиске же в "начале" данных получаю скан кластерного индекса
select Client, OrderID from ord 
where OrderDate <= '20090105' and DeliveryDate > '20090105' and Client = 300

  |--Parallelism(Gather Streams)
|--Clustered Index Scan(OBJECT:([dbo].[ord].[IX_c]), WHERE:([dbo].[ord].[OrderDate]<='2009-01-05 00:00:00.000' AND [dbo].[ord].[DeliveryDate]>'2009-01-05 00:00:00.000' AND [dbo].[ord].[Client]=(300)))

данных в модели - около 40 млн строк
в реальности же их на полтора порядка больше, плюс таблица пошире =(

в голову приходит вариант построить индекс на OrderDate, который будет работать хорошо в "начале" данных, но это не исключает проблем при поиске в "середине".

подскажите, какие могут быть подходы?
добавлю еще, что это "отчетная" часть системы, то есть лишних индексов можно достаточно спокойно понастроить
16 янв 14, 12:08    [15423863]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
aleks2
Guest
select * 
  from ord
  where OrderDate between @StartPeriod and @StopPeriod and DeliveryDate > @StopPeriod;


Для ваших запросов надо
create index IX1 on ord(Client, OrderDate, DeliveryDate )
16 янв 14, 12:30    [15424034]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
bacalavr
Member

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

OrderDate как раз не обязан быть в выбранном периоде, а должен быть лишь меньше его окончания. в этом то и проблема.

Для усугубления еще уберем Client = 300 из запроса, нужно найти все заказы, удовлетворяющие

where OrderDate <= @StopPeriod and DeliveryDate > @StopPeriod
16 янв 14, 12:41    [15424114]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
aleks2
Guest
bacalavr
aleks2,

Для усугубления еще уберем Client = 300 из запроса, нужно найти все заказы, удовлетворяющие



Не надо убирать. Это сильно усугубляет.

Но если убираете - убираем и в индексе
create CLUSTERED  index IX1 on ord(OrderDate, DeliveryDate )


Можете попробовать развести сервер на index intersect

create index IX1 on ord(OrderDate )
create   index IX2 on ord(DeliveryDate )
16 янв 14, 13:14    [15424359]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
bacalavr
Member

Откуда:
Сообщений: 311
aleks2
Можете попробовать развести сервер на index intersect


как-то насильно это можно сделать?

при наличии двух индексов, делается поиск лишь по одному, в зависимости от даты, по второму идет лишь просто предикат.
поэтому при поиске в "середине" данных получается не очень хорошо.
16 янв 14, 13:19    [15424396]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
aleks2
Можете попробовать развести сервер на index intersect
create index IX1 on ord(OrderDate )
create index IX2 on ord(DeliveryDate )

Причём тут он? Там смысл другой.

Вот сколько лет я наблюдаю, сколько не подымается тема с диапазонами, всё какое-то неверное представление о сути задачи.
Уже задолбался линки кидать на обсуждаемые темы.
11649923, 12610105, 14668313, 14624760
Подскажите по запросу на периоды
Контроль непрерывности и непересечения интервала дат
16 янв 14, 16:54    [15425779]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
SERG1257
Member

Откуда:
Сообщений: 2748
bacalavr
возникла потребность найти все заказы, которые в определенном временном промежутке уже были созданы, но не были доставлены.
Значит индекс должен быть по OrderDate
bacalavr
При поиске же в "начале" данных получаю скан кластерного индекса
И что прямо с начала времен до сегодня или все же ограничего чем-то
select Client, OrderID from ord 
where OrderDate between @start and @end and DeliveryDate > '20090105' and Client = 300

То бишь для решения данной задачи будет сканирование некластерного индекса на промежуток с дополнительными полями DeliveryDate, Client будя они понадобятся в запросе.
16 янв 14, 21:30    [15426984]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
SERG1257
Member

Откуда:
Сообщений: 2748
Если вас не интересуют строки с OrderDate = DeliveryDate отфильтруйте их в индексе.
16 янв 14, 21:41    [15427014]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
SERG1257
Member

Откуда:
Сообщений: 2748
Да и если данных реально много, то возможно имеет смысл секционировать по дате заказа (либо с помощью функции либо с помощью вьюх). То бишь не для данной задачи, просто для удобства администрирования и помощи другим задачам, ну а для данной задачи это может уменьшить объем сканирования.
16 янв 14, 21:53    [15427057]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mnior
bacalavr
При этом заказов, которые доставляются через несколько дней после заведения, прилично меньше, чем тех, которые доставляются сразу же.
возникла потребность найти все заказы, которые в определенном временном промежутке уже были созданы, но не были доставлены.
11649923
Если лень ходить по ссылке:
+ код
Mnior
ALTER TABLE [TABLE] ADD Duration AS (DateDiff(Day,StartDate,EndDate)) PERSISTED
	
CREATE INDEX IX_Date1 FOR [TABLE] (StartDate) WHERE (Duration >= 1)
CREATE INDEX IX_Date2 FOR [TABLE] (StartDate) WHERE (Duration <  1)

	SELECT	*
	FROM	[TABLE]
	WHERE	    Duration	>= 1
		AND StartDate	<= @From
		AND EndDate	>= @To
UNION ALL
	SELECT	*
	FROM	[TABLE]
	WHERE	    Duration	<  1
		AND StartDate	<= @From
		AND StartDate	>= DateAdd(Day,-1,@To)
		AND EndDate	>= @To
Там же но ниже 11716894
+ результаты тестов
Mnior
Индекс:ТупойПара ограниченныхПространственный
Размер:1.5G1.5G(+0)9G
Вермя L:474(7380)18(45)931(12329)
IO L:1977614323067+487641
Вермя M:199(2965)16(31)971(12321)
IO M:784314323061+488717
Вермя R:31(280)16(47)984(12201)
IO R:68624323088+488312
16 янв 14, 23:00    [15427355]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
bacalavr
Member

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

Искуственно ограничивать период с одной стороны - потенциально пропустить данные. Ограничивать заведомо огромным диапазоном - не очень эффективно.
20 янв 14, 15:41    [15441506]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
bacalavr
Member

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

Вы, как и всегда, очень серьезно подходите к своим ответам, за это вам огромный респект. Также спасибо за свою подборку.

Вариант разделения на "длинные" и "недлинные" реализован, даже при нем в аналоге запроса

SELECT	*
	FROM	[TABLE]
	WHERE	    Duration	>= 1
		AND StartDate	<= @From
		AND EndDate	>= @To

Получается поиск лишь по одному полю.
20 янв 14, 15:45    [15441556]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Сделать два индекса
create index IX1 on ord(DeliveryDate);
create index IX2 on ord(OrderDate);
И попробовать index intersect вручную
select
 ...
from
 (
  select OrderID from ord where OrderDate < '20140103'
  intersect
  select OrderID from ord where DeliveryDate > '20140105'
 ) t join
 ord o on o.OrderID = t.OrderID;
20 янв 14, 17:14    [15442363]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
bacalavr
Получается поиск лишь по одному полю.
Да, но смысл именно в том что этот индекс очень маленький по количеству строк.
Если у вас он получается большой, надо или увеличить размер базового диапазона, или сделать чуть больше разделений.
Поэтому я и упомянул про "Пространственный индекс", у него точно такой же смысл, и там кажись 4-ре уровня.

Или вы думаете что существует ещё более лучший подход, можно сказать "математечески точное решение"? :)
Или вы просто хотели это подчеркнуть?

И это, я не смотрел ваш тест, так что извиняйте, если не дал точных запросов.
Тем более на PERSISTED нельзя вешать индексы с ограничениями (во всяком случае в текущих версиях), нужно явно вставлять в таблицу (об этом написано по ссылке на тест).
20 янв 14, 17:16    [15442372]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
INTERSECT не уменьшает количество строк для проверки соответствия, он может лишь дать приемущество, что индекс уже таблицы.
И сервер будет перебирать все строки индекса ("половинки").
Более того, у них разный порядок сортировки. Merge-Jion может и не сработать, или будет предварительная сортировка всего индекса.

В моём случае количество строк существенно меньше.
Жаль что нет нормального сервера под рукой чтобы показать на том же примере.

Смотря на ваш генератор, можно разбить на 3 уровня, до дня, до 10 дней и больше 10. Как-то так.
Лучше уж посмотреть статистическое распределение диапазонов на реале.
На пространственном индексе также вручную ставится детализированность разделения для каждого случая.

Так как индексы не пересекающиеся и дополняющиеся, то указанная параметризованная вьюха сродни секционированию со всеми вытекающими возможностями.
20 янв 14, 18:04    [15442699]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в периоде по двум полям  [new]
bacalavr
Member

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

я понял вашу мысль, спасибо!
20 янв 14, 19:40    [15443210]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить