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

Откуда: Пермь
Сообщений: 18323
declare @t table (F int UNIQUE CLUSTERED )
Insert into @t
select null
union
select 1
union
select 2
union
select 3
union
select 4
union
select 5

Надо получить все значения меньше 3 и Null, если есть.
select * from @T where F<3
--Clustered Index Seek(OBJECT:(@T), SEEK:([F] < (3)) ORDERED FORWARD)
Не возвращает null, но обладает отличным планом.

select * from @T where IsNull(F,0)<3
--Clustered Index Scan(OBJECT:(@T), WHERE:(isnull([F],(0))<(3)))
select * from @T where F<3 or F is null
--Clustered Index Scan(OBJECT:(@T), WHERE:([F] IS NULL OR [F]<(3)))
Возвращает null, но план уже не так хорош.

Какие могут быть решения?
У меня мысли
1. избавится все таки от null'ов, что не хотелось бы.
2.
select * from @T where F<3
union 
select * from @T where F is null
  --Concatenation
       --Clustered Index Seek(OBJECT:(@T), SEEK:([F] < (3)) ORDERED FORWARD) 
       --Clustered Index Seek(OBJECT:(@T), SEEK:([F]=NULL) ORDERED FORWARD)
Но будет ли это быстрее?

Опишу задачу полностью. Таблица с периодическими реквизитами. Получение значения реквизита на дату будет
... DateBegin<=@DTime and Dateend>@DTime
Но я ввел, что если значение дейстовало всегда, то DateBegin = null. Соответственно Dateend = null когда реквизит действует бесконечно. DateBegin последнее поле в составе клястерного индекса(Dateend нет смысла туда включать). Делать datebegin вместо null'а какую то минимальную дату считаю не очень красиво, хотя это выход. Может все же есть еще варианты?
24 сен 09, 11:08    [7701717]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Deff


Опишу задачу полностью. Таблица с периодическими реквизитами. Получение значения реквизита на дату будет
... DateBegin<=@DTime and Dateend>@DTime
Но я ввел, что если значение дейстовало всегда, то DateBegin = null. Соответственно Dateend = null когда реквизит действует бесконечно. DateBegin последнее поле в составе клястерного индекса(Dateend нет смысла туда включать). Делать datebegin вместо null'а какую то минимальную дату считаю не очень красиво, хотя это выход. Может все же есть еще варианты?

И что мешает в качестве "значение дейстовало всегда" использовать максимально большое значение даты - 9999-12-31 ?
24 сен 09, 11:11    [7701756]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
Может все же есть еще варианты?


Угу. Уйти от NULLов и использовать

что если значение дейстовало всегда, то DateBegin = '17530101'. Соответственно Dateend = '99991231' когда реквизит действует бесконечно.
24 сен 09, 11:13    [7701767]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
последнее поле в составе клястерного индекса(Dateend нет смысла туда включать).


В этой таблице?

автор
declare @t table (F int UNIQUE CLUSTERED )


Нет?! Тогда приведите структуру реальной таблицы, индексы и типичные запросы.
24 сен 09, 11:15    [7701786]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
И еще в догонку. Дабы не заниматься вот этим:

автор
DateBegin<=@DTime and Dateend>@DTime


периодичность реквизитов делайте по принципу "действует с", тогда у Вас:

1. будет одно поле с датой.
2. Будет одно условие отбора в запросе с TOP 1.
24 сен 09, 11:17    [7701800]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
pkarklin
И еще в догонку. Дабы не заниматься вот этим:

автор
DateBegin<=@DTime and Dateend>@DTime


периодичность реквизитов делайте по принципу "действует с", тогда у Вас:

1. будет одно поле с датой.
2. Будет одно условие отбора в запросе с TOP 1.

только если между периодами нет "дырки"
24 сен 09, 12:11    [7702242]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Спасибо, значит избавлюсь от null'ов.
В догонку еще вопрос.
Есть таблица.
CREATE TABLE [dbo].[PeriodicLines](
	[PropertyID] [int] NOT NULL,
	[KeysID] [int] NOT NULL,
	[DateBegin] [datetime] NOT NULL,
	[DateEnd] [datetime] NOT NULL,
	PropertyValue int
 CONSTRAINT [IX_PeriodicLines] Primary key CLUSTERED 
(
	[PropertyID] ASC,
	[KeysID] ASC,
	[DateBegin] ASC
)
Для одного PropertyID,KeysID диапозоны не пересекаются.
Запрос ниже выдасть не более одной записи.
select PropertyValue
	from PeriodicLines 
	where  PropertyID = @PropertyID and 
			KeysID = @KeysID and 
			DateBegin<=@DTime and
			Dateend>@DTime
PropertyID, KeysID, DateBegin пойдут в плане как SEEK. Dateend, даже если и добавить в ключ, все равно пойдет как WHERE.

Вообще скорость довольно сносная, но с увеличением объема будет получаться что
для seek
PropertyID = @PropertyID and KeysID = @KeysID and DateBegin<=@DTime  
будет удовлетворять все больше записей и работать будет соответсвенно все медлнее.
Можно в ключе вместо DateBegin добавить Dateend. Получится тоже самое, только в seek попадет Dateend. Так как с текущими данными работаем чаще, чем с древними это будет не плохая оптимизация. Но все же не идеально.
Можно вот еще такой запрос:
select top 1 *
	from PeriodicLines 
	where PropertyID = @PropertyID and 
			KeysID = @KeysID and 
			DateBegin<=@DTime and
order by DateBegin desc
  |--Top(TOP EXPRESSION:((1)))
       |--Clustered Index Seek(OBJECT:([m000].[dbo].[PeriodicLines].[IX_PeriodicLines]),
--SEEK:([m000].[dbo].[PeriodicLines].[PropertyID]=(1) 
--AND [m000].[dbo].[PeriodicLines].[KeysID]=(1) 
--AND [m000].[dbo].[PeriodicLines].[DateBegin] <= '1900-01-02 00:00:00.000') ORDERED BACKWARD)
может так лучше? Тут только seek, но order by и top 1 меня смущают.
К тому же такой подход не решает вопрос, если хотим получить сразу список свойств или ключей. Например для запросов типа:
select *
	from PeriodicLines p
		INNER JOIN dbo.SomePropertyList L ON L.PropertyID = P.PropertyID
	where P.KeysID = @KeysID AND 
		 P.DateBegin<=@DTime  and
		P.DateEnd>@DTime 
24 сен 09, 12:19    [7702305]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
pkarklin
И еще в догонку. Дабы не заниматься вот этим:

автор
DateBegin<=@DTime and Dateend>@DTime


периодичность реквизитов делайте по принципу "действует с", тогда у Вас:

1. будет одно поле с датой.
2. Будет одно условие отбора в запросе с TOP 1.

С top 1 система как раз у меня сейчас и работает. Для получения одного элемента не плохо, но для получения большого списка - медленно. Поэтому и переделываю систему на "с полем dateend".
24 сен 09, 12:28    [7702372]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
Тут только seek, но order by и top 1 меня смущают.


Интересно чем?!

автор
К тому же такой подход не решает вопрос, если хотим получить сразу список свойств или ключей.


Гм...

select 
  *
from 
  dbo.SomePropertyList L
  CROSS APPLY
  (SELECT TOP 1
     *
   FROM
      PeriodicLines
   WHERE
     PropertyID = L.PropertyID and 
     KeysID = @KeysID and 
    DateFrom<=@DTime
   ORDER BY DateFrom DESC     
   ) Q ON
  L.PropertyID = Q.PropertyID
24 сен 09, 12:49    [7702516]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
pkarklin, думаю так будет медленнее. Причем на много. Но сейчас потестирую на данных.
24 сен 09, 12:53    [7702541]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Потестил
select p.KeysID, p.value, p.DateBegin, p.DateEnd into #t
	from PeriodicLines p 
	inner join Keys K on K.ID = P.KeysID and KeysOwnerID = 13 and K.ID%3=0
where PropertyID = 239 and DateBegin<='01.09.2009' and DateEnd>'01.09.2009'
и
select 
  p.KeysID, p.value, p.DateBegin, p.DateEnd into #t
from 
  dbo.keys K
  CROSS APPLY
  (SELECT TOP 1
     *
   FROM
      PeriodicLines
   WHERE
     PropertyID = 239 and 
     KeysID = k.ID and 
    DateBegin<='01.09.2009'
   ORDER BY DateBegin DESC     
   ) P
where KeysOwnerID = 13 and K.ID%3=0  

Вот этим условием K.ID%3 регулирую количество строк. На не большом количестве строк вариант с top 1 выгоднее. А на большом выгоднее вариант с dateend'ом.

Система с dateend'ом мне кажется переспективней. Мне кажется можно доработать.
24 сен 09, 13:43    [7702945]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Deff
Вот этим условием K.ID%3 регулирую количество строк. На не большом количестве строк вариант с top 1 выгоднее. А на большом выгоднее вариант с dateend'ом.


Приведите, пожалуйста, планы обоих запросов для обоих вариантов в текстовом виде. А так же статистику IO и Time.
24 сен 09, 14:23    [7703276]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Для понимания вот:
select count(*) from PeriodicLines where PropertyID = 239
вернул 4225548

Оба запроса
(1565353 row(s) affected)

первый запрос (c dateend'ом)
 |--Table Insert(OBJECT:([#t]), SET:([#t].[KeysID] = [m000].[dbo].[PeriodicLines].[KeysID] as [p].[KeysID],[#t].[vBoolean] = [m000].[dbo].[PeriodicLines].[vBoolean] as [p].[vBoolean],[#t].[DateBegin] = [m000].[dbo].[PeriodicLines].[DateBegin] as [p].[DateBegin],[#t].[DateEnd] = [m000].[dbo].[PeriodicLines].[DateEnd] as [p].[DateEnd]))
|--Top(ROWCOUNT est 0)
|--Merge Join(Inner Join, MERGE:([K].[ID])=([p].[KeysID]), RESIDUAL:([m000].[dbo].[Keys].[ID] as [K].[ID]=[m000].[dbo].[PeriodicLines].[KeysID] as [p].[KeysID]))
|--Index Seek(OBJECT:([m000].[dbo].[Keys].[IX_Keys] AS [K]), SEEK:([K].[KeysOwnerID]=(13)), WHERE:([m000].[dbo].[Keys].[ID] as [K].[ID]%(3)=(0)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([m000].[dbo].[PeriodicLines].[IX_PeriodicLines] AS [p]), SEEK:([p].[PropertyID]=(239)), WHERE:([m000].[dbo].[PeriodicLines].[DateBegin] as [p].[DateBegin]<='2009-09-01 00:00:00.000' AND isnull([m000].[dbo].[PeriodicLines].[DateEnd] as [p].[DateEnd],'2100-01-01 00:00:00.000')>'2009-09-01 00:00:00.000') ORDERED FORWARD)
--Total execution time 2171

второй(top 1)
  |--Table Insert(OBJECT:([#t]), SET:([#t].[KeysID] = [m000].[dbo].[PeriodicLines].[KeysID],[#t].[vBoolean] = [m000].[dbo].[PeriodicLines].[vBoolean],[#t].[DateBegin] = [m000].[dbo].[PeriodicLines].[DateBegin],[#t].[DateEnd] = [m000].[dbo].[PeriodicLines].[DateEnd]))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER REFERENCES:([K].[ID], [Expr1009]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([m000].[dbo].[Keys].[IX_Keys] AS [K]), SEEK:([K].[KeysOwnerID]=(13)), WHERE:([m000].[dbo].[Keys].[ID] as [K].[ID]%(3)=(0)) ORDERED FORWARD)
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Seek(OBJECT:([m000].[dbo].[PeriodicLines].[IX_PeriodicLines]), SEEK:([m000].[dbo].[PeriodicLines].[PropertyID]=(239) AND [m000].[dbo].[PeriodicLines].[KeysID]=[m000].[dbo].[Keys].[ID] as [K].[ID] AND [m000].[dbo].[PeriodicLines].[DateBegin] <= '2009-09-01 00:00:00.000') ORDERED BACKWARD)
--Total execution time 6968


Это при K.ID%1 = 0. Т.е. все данные. Вообще первый план тяжело назвать красивым.
24 сен 09, 14:58    [7703589]     Ответить | Цитировать Сообщить модератору
 Re: Условие берущее null и сохрание плана с SEEK.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
На не большом количестве строк вариант с top 1 выгоднее. А на большом выгоднее вариант с dateend'ом.

Система с dateend'ом мне кажется переспективней.


Все будет зависеть от того большое или небольшое число строк будет в реальных выборках, ибо в примере у Вас треть таблицы обрабатывается.
24 сен 09, 16:56    [7704593]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить