Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 При использовании between тормозит. Почему? Без него быстрее.  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
Доброго дня всем!

Ситуация следующая:

select sum(sd) as СНД from _1sbkttl ttl (nolock)
where (date = '20090401') and (kind = '1')
этот запрос работает значительно быстрее, чем вот этот:
select sum(sd) as СНД from _1sbkttl ttl (nolock)
where (date between '20090401' and '20090401') and (kind = '1')

Первый запрос работает значительно быстрее, чем второй. Почему так? Из этого следует, что выполнив n-раз первый запрос для каждого из значений date и объединив это всё в результатирующую таблицу результат получишь гораздо быстрее, нежели используя between.

Подскажите, где я ошибаюсь. Спасибо.
14 окт 09, 18:34    [7787042]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
iljy
Member

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

а вы чего вообще хотели сказать таким условием??
between '20090401' and '20090401'

Ну а так, если в общем (а по другому без планов запросов не получится) - between выбирает диапазон дат, и соответственно это может быть сканирование вместо поиска.
14 окт 09, 18:39    [7787060]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
iljy
RSRuslan,

а вы чего вообще хотели сказать таким условием??
between '20090401' and '20090401'

Ну а так, если в общем (а по другому без планов запросов не получится) - between выбирает диапазон дат, и соответственно это может быть сканирование вместо поиска.


Вообще-то я специально указал '20090401' and '20090401' и = '20090401'. Запрос возвращает одно и тоже, а время выполнения - значительно отличается. Вопрос ключевой

или
.....
unian all
.....
unian all
.....
unian all
.....
unian all

или

......
.......
where between ....
14 окт 09, 18:46    [7787078]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
iljy
Member

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

планы посмотрите. если присутствуют индексы - возможно есть смысл обновить статистику. А пока мы сферический запрос в вакууме обсуждаем, да к тому же еще и некорректный -between и = существенно разные операции и работают они по разному.
14 окт 09, 18:51    [7787098]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
Вот план 1-го запроса и 2-го. Сорри, но я в них ничего не соображаю, первый раз имею с этим дело :(

К сообщению приложен файл (1.CSV - 14bytes) cкачать
14 окт 09, 19:01    [7787129]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
Вот план 2-го...

К сообщению приложен файл (2.CSV - 86bytes) cкачать
14 окт 09, 19:02    [7787132]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
сравните эти 2 запроса:

select sum(sd) as СНД from _1sbkttl ttl (nolock)
where (date >= '20090401' and date <= '20090401') and (kind = '1')

и

select sum(sd) as СНД from _1sbkttl ttl (nolock)
where (date between '20090401' and '20090401') and (kind = '1')
14 окт 09, 19:03    [7787134]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Версию сервера укажите. А то говорить о 2000-м нет смысла.

Мелкомягким надо добавить патерн в прекомпилятор планов:
<Expression1> >= <Expression2>
AND => <Expression1> = <Expression2>
<Expression1> <= <Expression2>


RSRuslan

.....
unian all
.....
unian all
.....
unian all
.....
unian all
Оператор IN нормально для констант отрабатывает. Планы смотрите.
14 окт 09, 19:04    [7787138]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
RSRuslan
Вот план 1-го запроса и 2-го...
Ты хоть видел что выставил?
Нам мусор твоего буфера обмена не нужен.
14 окт 09, 19:07    [7787147]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
iljy
Member

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

Мелкомягким надо добавить патерн в прекомпилятор планов:
<Expression1> >= <Expression2>
AND => <Expression1> = <Expression2>
<Expression1> <= <Expression2>


а это не поможет, between раскывается как Expr>= @1 and Expr <=@2
14 окт 09, 19:13    [7787174]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
RSRuslan
Подскажите, где я ошибаюсь.
Возможно индекс вывернут на изнанку. Нужно выставить правильный порядок колонок. Предположительно вместо (date,kind) нуна (kind,date).

И главное, тестировать надо не на константах - это пустая потеря времени, а на переменных, если вы будете это оборачивать в процедуры/запросы. Компилятор на основе значений констант (используя статистику) генерирует соответствующий план запроса. На переменных такой халявы ему никто не даст. :)
14 окт 09, 19:18    [7787187]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
iljy
[quot Mnior]Expr>= @1 and Expr <=@2
Тут небыло сказано, что есть переменные, хотя это и 100% подразумевается.
Как раз поможет: Такие как RSRuslan (тестирующие на константах) не будут тут вопить про не оптимальность.
14 окт 09, 19:21    [7787201]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
iljy
Ну а так, если в общем (а по другому без планов запросов не получится) - between выбирает диапазон дат, и соответственно это может быть сканирование вместо поиска.


Скорее всего проблема именно в переборе и проверки принадлежности, а в случае равенства всё гораздо проще.

Извините, за то что выложил некорректные планы. Как правильно их выкладывать пока ещё не понял, да и уже не к чему это.
14 окт 09, 19:38    [7787275]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
RSRuslan
Как правильно их выкладывать пока ещё не понял,

https://www.sql.ru/faq/faq_topic.aspx?fid=393
14 окт 09, 20:11    [7787380]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mnior
iljy
[quot Mnior]Expr>= @1 and Expr <=@2
Тут небыло сказано, что есть переменные, хотя это и 100% подразумевается.
Как раз поможет: Такие как RSRuslan (тестирующие на константах) не будут тут вопить про не оптимальность.

вы проверьте сначала, потом спорьте. Именно константы (причем одинаковые) так и заменяются.
14 окт 09, 20:35    [7787451]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
iljy
Member

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

возможно дело действительно в индексе (date, kind), который используется при проверки на равеноство очень хорошо, а на условие по date - не очень. Но это опять же гадания, хотите точно - смотрите план.
14 окт 09, 20:39    [7787463]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
iljy
Mnior
iljy
Expr>= @1 and Expr <=@2
Тут небыло сказано, что есть переменные, хотя это и 100% подразумевается.
Как раз поможет: Такие как RSRuslan (тестирующие на константах) не будут тут вопить про не оптимальность.
вы проверьте сначала, потом спорьте.
Что вы заводитесь, какой спор? Проверять я ничего не обязан, я заранее оговорил про версию сервера, и нету оснований не доверять присутствующим.
iljy
Именно константы (причем одинаковые) так и заменяются.
Молодец, у вас сиквел под рукой.
Поздравляю, у вас на вашем сервере указанная проблема не воспроизводится.

Предположительно: у RSRuslan 2000-ый и на 2005м выше указанный паттерн "частично" работает, что в принципе предсказуемо.
Личное спасибо RSRuslan, за то что сразу указал версию сервера. :P
14 окт 09, 21:08    [7787557]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mnior,
да я не завожусь, что вы :) просто у меня под рукой как раз 2005, и такой запрос
select * from data where date between '20090401' and '20090401'

имеет вот такой план:

|--Clustered Index Seek(OBJECT:([DataBase].[dbo].[Data].[IX_Data_Date_Id]),
SEEK:([DataBase].[dbo].[Data].[Date] >= CONVERT_IMPLICIT(datetime,[@1],0) AND
[DataBase].[dbo].[Data].[Date] <= CONVERT_IMPLICIT(datetime,[@2],0))
ORDERED FORWARD)
14 окт 09, 21:19    [7787600]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
iljy, какже вы любите людей путать:
-- SET SHOWPLAN_TEXT ON

DECLARE	@Table	TABLE (
	 ID	Int	IDENTITY PRIMARY KEY
	,Kind	VarChar(50)	NOT NULL
	,Data	DateTime	NOT NULL
	,UNIQUE (Kind
		,Date
	)
)
SELECT	*
FROM	@Table
WHERE	    Kind = '1'
--1	AND Date <= GetDate()	AND Date >= GetDate()
--2	AND Date <= @Date	AND Date >= @Date
--3	AND Date <= '20010101'	AND Date >= '20010101'

1  |--Index Seek(OBJECT:(@Table), SEEK:([Kind]='1' AND [Date] >= getdate() AND [Date] <= getdate()) ORDERED FORWARD)
2  |--Index Seek(OBJECT:(@Table), SEEK:([Kind]='1' AND [Date] >= [@Date] AND [Date] <= [@Date]) ORDERED FORWARD)
3  |--Index Seek(OBJECT:(@Table), SEEK:([Kind]='1' AND [Date]='2001-01-01 00:00:00.000') ORDERED FORWARD)
Пришлось перегрузиться в винды. Старайтесь не спешить, не лениться, и быть поточнее.

Короче патерн работает только для констант. Мелкомягкие халявничают.
15 окт 09, 01:39    [7788186]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Шо за день:
Mnior

-- SET SHOWPLAN_TEXT ON
DECLARE	@Table	TABLE (
	 ID	Int	IDENTITY PRIMARY KEY
	,Kind	VarChar(50)	NOT NULL
	,Date	DateTime	NOT NULL
	,UNIQUE (Kind
		,Date
	)
)
DECLARE	@Date	DateTime
SELECT	@Date	= GetDate()
SELECT	*
FROM	@Table
WHERE	    Kind = '1'
--1	AND Date <= GetDate()	AND Date >= GetDate()
--2	AND Date <= @Date	AND Date >= @Date
--3	AND Date <= '20010101'	AND Date >= '20010101'

1  |--Index Seek(OBJECT:(@Table), SEEK:([Kind]='1' AND [Date] >= getdate() AND [Date] <= getdate()) ORDERED FORWARD)
2  |--Index Seek(OBJECT:(@Table), SEEK:([Kind]='1' AND [Date] >= [@Date] AND [Date] <= [@Date]) ORDERED FORWARD)
3  |--Index Seek(OBJECT:(@Table), SEEK:([Kind]='1' AND [Date]='2001-01-01 00:00:00.000') ORDERED FORWARD)
15 окт 09, 01:43    [7788192]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
Извените, что не сказал ранее - сервер 2000. На текущий момент проверка на равенство и объединение union all - работает в два раза быстрее. Естественно запрос гораздо сложнее, здесь я написал самый простой пример, чтобы было понятнее, сам не люблю когда здесь выкладывают километры своего кода.
15 окт 09, 09:55    [7788718]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
iljy
Member

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

извините, что значит путать?? я построил план конкретного запроса и выложил сюда. Версию сервера хотите? Легко
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Workgroup Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 

и замена date between на date >= и date<= план не меняет. Но если выполнить такой запрос:
select * from data where id = 7 and date <= '20090401' and date >= '20090401'
план получается такой:

|--Nested Loops(Inner Join, OUTER REFERENCES:([DataBase].[dbo].[Data].[Id],
[DataBase].[dbo].[Data].[Date]))
|--Index Seek(OBJECT:([DataBase].[dbo].[Data].[IX_Data_Id_Date]),
SEEK:([DataBase].[dbo].[Data].[Id]=(7) AND
[DataBase].[dbo].[Data].[Date] >= '2009-04-01 00:00:00.000' AND
[DataBase].[dbo].[Data].[Date] <= '2009-04-01 00:00:00.000') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([DataBase].[dbo].[Data].[IX_Data_Date_Id]),
SEEK:([DataBase].[dbo].[Data].[Date]=[DataBase].[dbo].[Data].[Date] AND
[DataBase].[dbo].[Data].[Id]=[DataBase].[dbo].[Data].[Id]) LOOKUP ORDERED FORWARD)

так что все не так уж безоблачно. Хотя параметры во втором случае оптимизатор не подставляет - замена шаблона тоже не происходит. А уж тут-то она была бы очень полезна - можно было бы сразу по кластеру искать
15 окт 09, 11:56    [7789847]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
iljy
что значит путать?? я построил план конкретного запроса и выложил сюда.
Неужели носом тыкать?:

iljy
select * from data where date between '20090401' and '20090401'
  |--Clustered Index Seek(OBJECT:([DataBase].[dbo].[Data].[IX_Data_Date_Id]),
      SEEK:([DataBase].[dbo].[Data].[Date] >= CONVERT_IMPLICIT(datetime,[@1],0) AND
               [DataBase].[dbo].[Data].[Date] <= CONVERT_IMPLICIT(datetime,[@2],0))
      ORDERED FORWARD)
  |--Nested Loops(Inner Join, OUTER REFERENCES:([DataBase].[dbo].[Data].[Id],
                        [DataBase].[dbo].[Data].[Date]))
       |--Index Seek(OBJECT:([DataBase].[dbo].[Data].[IX_Data_Id_Date]),
             SEEK:([DataBase].[dbo].[Data].[Id]=(7) AND
                     [DataBase].[dbo].[Data].[Date] >= '2009-04-01 00:00:00.000' AND
                     [DataBase].[dbo].[Data].[Date] <= '2009-04-01 00:00:00.000') ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([DataBase].[dbo].[Data].[IX_Data_Date_Id]),
           SEEK:([DataBase].[dbo].[Data].[Date]=[DataBase].[dbo].[Data].[Date] AND
                    [DataBase].[dbo].[Data].[Id]=[DataBase].[dbo].[Data].[Id]) LOOKUP ORDERED FORWARD)
15 окт 09, 15:28    [7791796]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
iljy
Member

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

вы во что собственно пытаетесь ткнуть меня носом? В то, что оптимизатор ведет себя по разному? Так это я вас в этот факт тычу носом с самого начала. И в некоторых случаях оптимизатор даже использует подстановку = вместо >= and <=. Но далеко не во всех, и даже закономерность я проследить затрудняюсь. Поэтому явно не стоит взваливать на оптимизатор обязанность думать за вас.
15 окт 09, 15:43    [7791918]     Ответить | Цитировать Сообщить модератору
 Re: При использовании between тормозит. Почему? Без него быстрее.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
iljy
вы во что собственно пытаетесь ткнуть меня носом?
В то что для конкретного запроса с константой '20090401' оптимизатор не может сгенерировать план с CONVERT_IMPLICIT(datetime,[@1],0), где явно видно что используется переменная @1. Что-то не припоминаю такого синтаксиса для констант.
iljy
оптимизатор ведет себя по разному
Так это я вас в этот факт тычу носом с самого начала.
Где, с какого места вы мне лично тычете? Где я утверждал или подразумевал, что оптимизатор ведёт себя одинаково?
iljy
Поэтому явно не стоит взваливать на оптимизатор обязанность думать за вас.
1. Узко мыслите - оба выражения <= и >= могут быть написаны в разных местах/объектах. Например, одно определено в функции, другое в процедуре, которая использует эту функцию или и того глубже по иерархии. Человеку это, может быть, нереально проследить, всё зависит от сложности проекта.
2. Оптимизатор, в особенности мелко-мягкий, как раз и так продумывает за меня (и всех нас) невероятную уйму вещей. Ибо, как ни странно, цель всего IT (и вообще T) это автоматизация и замена рутины. И если технологии позволяют, значит должны - по определению/априори (конечно же в рамках эффективности).
3. Вы хоть знаете что взваливать? Если вы сами пишите:
iljy
даже закономерность я проследить затрудняюсь.
Сначала сами выясните, что, где и почему конкретно оптимизатор не может сделать, а потом "в это тычьте носом" ... "с самого начала" ... от начала времён ...
Ладно, со словом тычьте я зря тут начал - ща Glory всех нас заблокирует. ;)

Хотя и так понятно, но всё-таки: вы можете представить структуру таблицы data для вашего запроса?:
select * from data where id = 7 and date <= '20090401' and date >= '20090401'
А то ядрёный у вас план. Хотя и так ясно, что сиквел халявничает, но так - потыкаться носом в дебрях оптимизатора. ;)
15 окт 09, 21:23    [7793847]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить