Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 определить, какая выборка быстрей  [new]
нуб987
Guest
подскажите, как определить, какой из запросов будет работать быстрей.
В частности требуется сделать выборку по датам с привязкой 2-х таблиц по полю datetime.
1-я таблица - это "сборная" из нескольких т.фактов на 100млн.записей (select union all) с датами продаж (любой год, любой месяц, любое число, т.е. самые обычные даты).
2-я таблица - некий справочник, в котором даты указаны как год и месяц (т.е. там всегда 1-е число месяца).
Нужно по селекту выгрузить (insert'ом) факты в другую таблицу. Даты по фактам и из справочника "скрещиваются" по месяцам. Т.е. если в справочнике указано 1-е февраля, значит берем все факты за весь февраль и т.д.
Т.е. запрос будет примерно такой:
select.....
where ....
   and (MONTH(facts.ADate) = MONTH(sprav.AMonth))
   and (YEAR(facts.ADate) = YEAR(sprav.AMonth))

Не быстрее ли будет такой запрос (и как это узнать, кроме как запускать и ждать реальный запрос):
where ....
   and substring(cast(facts.ADate as varchar(8), 1, 6)) = substring(cast(sprav.AMonth as varchar(8), 1, 6))

или такой:
where ....
   and facts.ADate - day(facts.ADate) + 1 = sprav.AMonth
28 мар 12, 19:34    [12329807]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
все запросы ужасные...вы не оставляете шансов индексам...

а для замера времени, чтени/записи есть статистики

set statistics io
set statistics time
28 мар 12, 19:41    [12329821]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
выгрузить справочник во временную таблицу (если этого нельзя сделать заранее в базе) с преобразованием вашего "1-е число месяца" в два поля: 1-е и последнее число месяца.
а уж потом
facts.ADate between ... and ...
28 мар 12, 20:03    [12329889]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
нуб987
Т.е. запрос будет примерно такой:
...
Не быстрее ли будет такой запрос (и как это узнать, кроме как запускать и ждать реальный запрос):
...
или такой:
Ужас ...

Надеюсь, на даты в фактах есть индекс?

where ....
and facts.ADate >= sprav.AMonth
and facts.ADate < dateadd( dd, 1, sprav.AMonth)
28 мар 12, 20:08    [12329907]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
нуб987
Guest
да, индексы в обоих таблицах на этих полях есть
а в чем ужас?
28 мар 12, 22:12    [12330306]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
нуб987
да, индексы в обоих таблицах на этих полях есть
а в чем ужас?
В том, что любая функция над индексированным столбцом делает поиск по индексу невозможным. В лучьшем случае будет скан интекса, в худшем - скан таблицы.

Впрочем, если вы выгружаете все факты, то всё равно будет скан таблицы, так что не страшно :-)

Возможно, в этом случае есть такой вариант:
where ....
 and facts.ADate >= sprav.AMonth
 and dateadd( mm, -1, facts.ADate) < sprav.AMonth
может, сервер из за небольшого количества значений sprav.AMonth будет распаралеливать по месяцам...

PS. В предыдущем примере ошибся, нужно месяц прибавлять, а не день:
where ....
 and facts.ADate >= sprav.AMonth
 and facts.ADate < dateadd( mm, 1, sprav.AMonth)


Ну и планы надо смотреть на все эти варианты.
28 мар 12, 22:25    [12330347]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
alexeyvg
нуб987
да, индексы в обоих таблицах на этих полях есть
а в чем ужас?
В том, что любая функция над индексированным столбцом делает поиск по индексу невозможным. В лучьшем случае будет скан интекса, в худшем - скан таблицы.

Впрочем, если вы выгружаете все факты, то всё равно будет скан таблицы, так что не страшно :-)

Возможно, в этом случае есть такой вариант:
where ....
 and facts.ADate >= sprav.AMonth
 and dateadd( mm, -1, facts.ADate) < sprav.AMonth
может, сервер из за небольшого количества значений sprav.AMonth будет распаралеливать по месяцам...

PS. В предыдущем примере ошибся, нужно месяц прибавлять, а не день:
where ....
 and facts.ADate >= sprav.AMonth
 and facts.ADate < dateadd( mm, 1, sprav.AMonth)


Ну и планы надо смотреть на все эти варианты.

Самое смешное, что если реально нужно загрузить всю таблицу фактов то индексы скорее всего в топку, варианты SARG запросов серверу наверняка тоже не понравятся. При скане обеих таблиц самый быстрый вариант соединения HASH JOIN, но при условиях вида < > хэш использовать невозможно по определению. Так что я ставлю на самый тупой вариант, но мне кажется что он таки будет самым быстрым:

 and substring(cast(facts.ADate as varchar(8)), 1, 6) = substring(cast(sprav.AMonth as varchar(8)), 1, 6)


В  плане в идеале должно быть 2 скана и HASH MATCH
29 мар 12, 04:13    [12330935]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
нуб987
Guest
скопировать нужно не всю т.фактов, а выборочно. Но скопируется процентов 30% из 100млн фактов. А вот что выбирать, это указано в справочнике (где даты указаны по месяцам).
Помимо выборки по датам там еще куча условий. Но сейчас интересуют именно даты.

И еще вдогонку. Есть текстовое поле [StrField] со значениями вида 'bla-bla-bla AAAA', где AAAA может принимать одно из 2-10 текстовых значений. Но важно выбрать те записи, где присутствует 'bla-bla-bla'.
Как это лучше реализовать:
1.
where ....
   and (substring(StrField, 11) = 'bla-bla-bla')
   ...

2.
where ....
   and (
      (StrField = 'bla-bla-bla aaa')
      or
      (StrField = 'bla-bla-bla bbb')
      or
      (StrField = 'bla-bla-bla ccc')
      ...
      or
      (StrField = 'bla-bla-bla zzz')
      )
29 мар 12, 13:55    [12333471]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
нуб987
скопировать нужно не всю т.фактов, а выборочно. Но скопируется процентов 30% из 100млн фактов. А вот что выбирать, это указано в справочнике (где даты указаны по месяцам).
Тогда наверное лучьше мой первый вариант, тем более что:
нуб987
Помимо выборки по датам там еще куча условий

И нужно будет посмотреть планы на реальных запросах, возможно, будут оптимальны какие то составные индексы.

нуб987
И еще вдогонку. Есть текстовое поле [StrField] со значениями вида 'bla-bla-bla AAAA', где AAAA может принимать одно из 2-10 текстовых значений. Но важно выбрать те записи, где присутствует 'bla-bla-bla'.
То же самое, первый вариант плохой. Я же сказал, любая функция отключает инедкс.

Можно использовать либо второй вариант, либо
where ....
   and StrField LIKE 'bla-bla-bla%'
29 мар 12, 14:11    [12333609]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
нуб987
Guest
огромное спасибо всем ответившим :) есть над чем подумать
ПС. откуда вы все умные такие? БОЛ читаю, там таких тонкостей не описано...
Неужели только долгий опыт и грабли?
29 мар 12, 14:34    [12333858]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
нуб987
БОЛ читаю, там таких тонкостей не описано...
В BOL практически всё есть, только он большой, за раз не прочитать...

Правила использования индексов - это вообще самые основы, в принципе это понятно даже без чтения документации (просто представьте, как вы сами реализовали бы индексы, и будет понятно, почему при испльзовании функций индексы не работают).
29 мар 12, 14:47    [12333959]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
нуб987
Guest
ну и еще вопрос :)
при условии, что нужно выбрать много записей, не обработанных (не вставленных) в прошлых выборках, какой из запросов лучше выбрать:
1.
not exists(select 1 from facts_copy where facts_copy.spravId = sprav.id)

2.
sprav.id not in (select spravid from facts_copy)
29 мар 12, 14:49    [12333966]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
нуб987
Guest
ПС. facts_copy - это таблица, куда мы копировали наши факты из пред.сообщений
29 мар 12, 14:51    [12333976]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
нуб987,

а колонка facts_copy.spravId nullable?
Если нет, то без разницы, скорее всего оптимизатор одинаковый план сгенерирует. А если nullable то exists однозначно. Я вообще всегда предпочитаю пользоваться именно им, для таких целей.
29 мар 12, 15:03    [12334095]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
SomewhereSomehow
Я вообще всегда предпочитаю пользоваться именно им, для таких целей.
+1 Лучше not exists
29 мар 12, 15:38    [12334452]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
нуб987
Guest
alexeyvg
Я же сказал, любая функция отключает инедкс.
Можно использовать либо второй вариант, либо
where ....
   and StrField LIKE 'bla-bla-bla%'

т.е. такую строку:
ISNULL(Field, '') = ''

лучше заменить на:
Field is NULL or Field = ''

так?
ПС. всем еще раз спасибо. С внесенными изменениями время пересчета огроменной процедуры сократилось с 6-ти часов до полутора :)
Плюс вижу, где еще можно сократить время добавлением предварительных преобразований
23 апр 12, 12:27    [12456043]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
нуб987
т.е. такую строку:
ISNULL(Field, '') = ''


лучше заменить на:
Field is NULL or Field = ''


так?
В принципе да, но на практике or тоже не очень хорошо, и такой индекс будет нормально работать, если нужно выбрать немногочисленные пустые записи, а это обычно не так.

Так что смотрите планы на конкретных данных - может помочь, а может и ухудшить.
23 апр 12, 16:40    [12458137]     Ответить | Цитировать Сообщить модератору
 Re: определить, какая выборка быстрей  [new]
нуб987
Guest
в продолжение нубских вопросов
подскажите плз. Например, нужно проапдейтить таблицу фактов (не всю, но достаточно большую ее часть) или сделать выборку из нее каких-то значений (т.е. в селекте будут поля только из 1-й таблицы) - по-моему это похожие в плане выборки задачи. Как лучше состыковать т.фактов со справочником?
Так:
update FactTable
set Field1 = 2 * Field2
from FactTable as f, Clients as c
where c.id = f.ClientId
   and c.ClientTypeId = 123



select f.ClientId, f.ProdId
from FactTable as f, Clients as c, Prod as p
where c.id = f.ClientId
   and c.ClientTypeId = 123
   and p.id = f.ProdId
   and p.ProdType = 456


или так:
update FactTable
set Field1 = 2 * Field2
where exists(select 1 from Clients as c
      where c.ClientTypeId = 123
         and c.id = FactTable.ClientId)



select f.ClientId, f.ProdId
from FactTable as f
where exists(select 1 from Clients as c
      where c.ClientTypeId = 123
         and c.id = f.ClientId)
   and exists(select 1 from Prod as p
      where p.ProdTypeId = 456
         and p.id = f.ProdId)
13 янв 13, 22:49    [13763602]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить