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

Откуда:
Сообщений: 243
Задачка взята из сертификационного теста.

Исходные данные:
Есть табличка Sales. Скрипт ее создания:
create table sales 
(
  ID int Identity(1, 1) Not null Primary key,
  orderDate char(10) NOT NULL,
  Amount decimal
);


На табличке создан индекс при помощи следующего скрипта:
create index IX_Sales_OrderDate
ON Sales(OrderDate)
include(ID, Amount);


В табличке хранится около 3 миллионов записей

Еще есть хранимая процедурка:
create procedure usp_Proc1(
  @date1 datetime, @date2 datetime)
AS 
  SELECT ID, OrderDate, Amount
  from sales
  where cast(orderDate as datetime) between @date1 and @date2
  order by ID;  


Стоит задачка: что надо сделать, чтобы ускорить выполнение этой процедуры?

В качестве вариантов решения предлагается:
- изменить тип колонки OrderDate в таблице на datetime
- изменить условие where в хранимой процедуре на WHERE OrderDate BETWEEN CAST(@date1 as char(10)) AND CAST(@date2 as char(10))
- убрать условие Order by в процедуре
- пересоздать индекс, убрав из него включаемые колонки (условие INCLUDE)

Итак, выполняю все эти манипуляции по созданию этих объектов и заполняю табличку 3 млн записей.
Смотрю план выполнения:
начальный план

Будем смотреть по порядку предложенных вариантов:
- изменение типа колонки мне кажется в данном случае не подходит, потому как на заполненной таблице с таким количеством записей такая операция с большой вероятностью не выполнится. По крайней мере уже по той причине, что типы несовместимы
- меняем условие where и смотрим план выполнения:
план с измененным условием where
- возвращаю обратно прежнее условие where, но убираю условие order by и вновь смотрю план выполнения:
план без условия order by
- возвращаю все в исходное состояние, меняю индекс, используя следующий скрипт:
DROP INDEX IX_Sales_OrderDate ON Sales;
GO
CREATE INDEX IX_Sales_OrderDate ON Sales(OrderDate);
GO
и вновь смотрю план выполнения:
план с новым индексом
Я в планах не очень разбираюсь, поэтому вопрос, какой из этих планов лучший и какой будет работать быстрее всех?

Заранее спасибо.
26 дек 13, 14:41    [15350419]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1882
harisma
- изменение типа колонки мне кажется в данном случае не подходит

тест провален
26 дек 13, 14:58    [15350579]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Дурацкий вопрос если честно.

- изменить тип колонки OrderDate в таблице на datetime
раз у нас в базовом запросе cast(orderDate as datetime) то предполагается что в поле orderDate все ж таки нечто конвертируемое в дату. так что потенциально можно сконвертить столбец и решить проблемы
-изменить условие where в хранимой процедуре на WHERE OrderDate BETWEEN CAST(@date1 as char(10)) AND CAST(@date2 as char(10))
тут бабушка на двое сказала что после конвертации получится. У меня лично Dec 26 201 .))))
Так что, что там с чем мы будем сравнивать не понятно
- убрать условие Order by в процедуре
феерично. самая классная оптимизация получится, если вообще убрать запрос из процедуры. Пускай ничего не выводит.
- пересоздать индекс, убрав из него включаемые колонки (условие INCLUDE)
вариант из серии надо же что то написать.

В общем, я к первому варианту склоняюсь, но есть ощущение, что создатели теста ко второму
26 дек 13, 14:58    [15350582]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
harisma
Member

Откуда:
Сообщений: 243
Какие еще будут варианты?
26 дек 13, 16:19    [15351202]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Антонио Перейро
Member

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

замените условие и будет вам щастье на:

where orderDate between cast(@date1 as char(10)) and cast(@date2 as char(10))
26 дек 13, 16:27    [15351235]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
wizli
Member

Откуда: Minsk
Сообщений: 270
Скорее всего это будет первый вариант. Если сделаем,так как описано во втором, никогда не будет index seek(SARG аргументы).
3 и 4 вариант вообще ни о чем, просто чтобы заполнить варианты ответа.
26 дек 13, 16:29    [15351251]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
harisma
Member

Откуда:
Сообщений: 243
Антонио Перейро,

А можно "на пальцах" объяснить, почему замена этого условия приводит к ускорению. За счет чего? (файлы планов приложены в моем первом сообщении. Лучше всего, если объясните на них)
26 дек 13, 16:30    [15351254]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Антонио Перейро
Member

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

у Вас индекс по полю OrderDate. Он не будет работать если в условиях не используется это поле. Вы используете в условии не поле а функцию, при этом неизбежна операция сканирование таблицы. Если будете использовать конкретно это поле, будет поиск по индексу. Преобразование констант намного проще, с точки зрения производительности, нежели преобразование поля в предикате фильтарции (условие в where).
26 дек 13, 16:51    [15351340]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
wizli
Скорее всего это будет первый вариант. Если сделаем,так как описано во втором, никогда не будет index seek(SARG аргументы).
3 и 4 вариант вообще ни о чем, просто чтобы заполнить варианты ответа.

чего не будет то? там char с char сравнивается.
26 дек 13, 16:51    [15351342]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Exproment
Member

Откуда:
Сообщений: 416
harisma, ваши планы у меня не открываются.

Но по факту - прочитайте что такое SARG предикаты и как они соотносятся с преобразованием типов.
26 дек 13, 16:51    [15351345]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
wizli
Member

Откуда: Minsk
Сообщений: 270
Мистер Хенки
wizli
Скорее всего это будет первый вариант. Если сделаем,так как описано во втором, никогда не будет index seek(SARG аргументы).
3 и 4 вариант вообще ни о чем, просто чтобы заполнить варианты ответа.

чего не будет то? там char с char сравнивается.

Здесь мой косяк, написал какую-то чушь
26 дек 13, 16:54    [15351363]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
wizli
Мистер Хенки
пропущено...

чего не будет то? там char с char сравнивается.

Здесь мой косяк, написал какую-то чушь

Просто на мой взгляд вариант этот идиотский потому что в вопросе ни разу не говорится в каком формате дата хранится в таблице и в какой формат она будет преобразовыватся при cast(@date as char(10)) . Если отбросить эти сомнения, то выходит вариант самый лучший, если сомневаться, то первый вариант - там получится сравнение дат, а не строк.
26 дек 13, 17:00    [15351390]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Антонио Перейро
Member

Откуда:
Сообщений: 11
Антонио Перейро,

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

Я бы еще хинт в запросе добавил - option(recompile)
  SELECT ID, OrderDate, Amount
  from sales
  where orderDate between cast(@date1 as char(10)) and cast(@date2 as char(10))
  order by ID
option(recompile);
26 дек 13, 17:02    [15351395]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
o-o
Guest
harisma
Антонио Перейро,

А можно "на пальцах" объяснить, почему замена этого условия приводит к ускорению. За счет чего? (файлы планов приложены в моем первом сообщении. Лучше всего, если объясните на них)


это у Антонио шутка такая, предновогодняя.
Вам же уже Мистер Хенки намекал про конвертацию без указания "стиля",
повторить наглядно?

declare @t table (dt char(10))
insert into @t (dt)
select CONVERT(char(10), '20130301', 103)

select *
from @t
-----------------
dt
20130301  

declare @date1 datetime = '20130101',
           @date2 datetime = '20130401'
        
select *
from @t
where dt between cast(@date1 as char(10)) and cast(@date2 as char(10))
------------------
dt
-- пусто

 select cast(@date1 as char(10)),
          cast(@date2 as char(10))
-----------------
date1_char	date2_char
Jan  1 201	Apr  1 201

--что вообще может попасть в интервал от J до A? 
26 дек 13, 17:12    [15351424]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Exproment
Member

Откуда:
Сообщений: 416
Мой вариант:
harisma
- изменить тип колонки OrderDate в таблице на datetime

Увеличит производительность операций с индексом и таблицей в целом. Т.к. datetime весит 8 байт, а char(10) весит 10 байт.
harisma
- изменить условие where в хранимой процедуре на WHERE OrderDate BETWEEN CAST(@date1 as char(10)) AND CAST(@date2 as char(10))

Сделает предикат SARG-совместимым. => скорей всего увеличит производительность. Тут уже от оптимизатора зависит и многих иных факторов типа статистики и т.д.
harisma
- убрать условие Order by в процедуре

Для текущих индексов увеличит производительность.
harisma
- пересоздать индекс, убрав из него включаемые колонки (условие INCLUDE)

Однозначно надо пересоздавать. Включаемое поле ID точно надо убирать, т.к. оно уже есть на leaf-уровне индекса.
26 дек 13, 17:16    [15351445]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Антонио Перейро
Member

Откуда:
Сообщений: 11
o-o,

ну да cast сработает от настроек сервера... ну хорошо уточню - CONVERT(char(10), @param, 112)

конверт использовать нужно. Просто поставил каст из за того что эта функция изначально в запросе и использовалась ))).
26 дек 13, 17:27    [15351489]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
o-o
Guest
извиняюсь, там для чистоты эксперимента надо было в таблицу вот такое сложить,
хотя сути не меняет:
declare @t table (dt char(10))
insert into @t (dt)
select CONVERT(char(10), cast('20130301' as datetime), 103)

select *
from @t
--------------------
01/03/2013

т.е. в таблице все же лежит нечто похожее на дату.
но уж никак не то, во что искорежит дату тупой cast(some_date as char(10))

дата, хранящаяся в виде строки, всегда ЗЛО
26 дек 13, 17:33    [15351515]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
o-o
Guest
Антонио Перейро
o-o,

ну да cast сработает от настроек сервера... ну хорошо уточню - CONVERT(char(10), @param, 112)

конверт использовать нужно. Просто поставил каст из за того что эта функция изначально в запросе и использовалась ))).


вот именно: что у НИХ ТАМ использовалось, использовать вообще нельзя.
а настройки сервера...
ну давайте вместо того, чтоб 1 раз поместить дату в поле дата,
переставим сервер, перепишем процедуры через CONVERT,
а заодно еще гору функций изобретем по сложению дат в виде строк
26 дек 13, 17:42    [15351558]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
o-o
Guest
ну и еще раз о сравнении строк:

declare @t table (dt char(10))
insert into @t (dt)
values('01/03/2013'), ('31/01/2013'), ('05/02/2013') 

select *
from  @t
order by 1 ASC
--------------------------
dt
01/03/2013
05/02/2013
31/01/2013

вас устраивает такая сортировка?
вот и результаты у предлагающих выбрать строки по условию BETWEEN
будут совсем не те, что ожидались
26 дек 13, 18:06    [15351651]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
что, кто-то действительно сомневается, что правильный ответ первый?
второй не подходит, потому что cast строку с датой в приемлемом для сравнения с помощью between не даст.
третий и четвертый - ни о чем вообще. рассчитано, по-моему на тех, кто об оптимизации "что-то слышал". ну, там, про то что ордер бай замедляет выполнение, или что что-то с индексами для оптимизации делать надо.
27 дек 13, 10:57    [15353904]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
daw
второй не подходит, потому что cast строку с датой в приемлемом для сравнения с помощью between не даст.

Почему нет? Если в ISO стандарте поле с датой и региональная настройка сервера, то все ок.
27 дек 13, 11:06    [15353961]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Мистер Хенки
daw
второй не подходит, потому что cast строку с датой в приемлемом для сравнения с помощью between не даст.

Почему нет? Если в ISO стандарте поле с датой и региональная настройка сервера, то все ок.

Потому что у условиях задачи нет ничего об ISO.
Первый вариант правильный.
27 дек 13, 11:24    [15354080]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Владимир Затуливетер
Мистер Хенки
пропущено...

Почему нет? Если в ISO стандарте поле с датой и региональная настройка сервера, то все ок.

Потому что у условиях задачи нет ничего об ISO.
Первый вариант правильный.

так нет и про отсутствие ))). но есть небольшой намек на то, что табла достаточна немаленькая 3000000 строк и вот так менять тип ради оптимизации одной процедурки?
27 дек 13, 11:29    [15354108]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Exproment,

> Однозначно надо пересоздавать. Включаемое поле ID точно надо убирать, т.к. оно уже есть на leaf-уровне индекса.

можно поинтересоваться, зачем?
27 дек 13, 11:35    [15354146]     Ответить | Цитировать Сообщить модератору
 Re: Какой план выполнения лучше и быстрее и почему?  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Мистер Хенки,

> Почему нет? Если в ISO стандарте поле с датой и региональная настройка сервера, то все ок.

гм. не подскажите, какие должны быть настройки сервера, чтобы cast выдавал строку с датой в iso-формате?
27 дек 13, 11:37    [15354154]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить