ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Какой план выполнения лучше и быстрее и почему?
25 сообщений из 36, страница 1 из 2
  1  все
Какой план выполнения лучше и быстрее и почему?
    #38513182
harisma
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
harisma
Гость
Задачка взята из сертификационного теста.

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



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



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

Еще есть хранимая процедурка:
Код: sql
1.
2.
3.
4.
5.
6.
7.
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
- возвращаю все в исходное состояние, меняю индекс, используя следующий скрипт:
Код: sql
1.
2.
3.
4.
DROP INDEX IX_Sales_OrderDate ON Sales;
GO
CREATE INDEX IX_Sales_OrderDate ON Sales(OrderDate);
GO

и вновь смотрю план выполнения:
план с новым индексом
Я в планах не очень разбираюсь, поэтому вопрос, какой из этих планов лучший и какой будет работать быстрее всех?

Заранее спасибо.
...
Рейтинг: 0 / 0
26.12.2013, 14:41
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513217
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
harisma- изменение типа колонки мне кажется в данном случае не подходит

тест провален
...
Рейтинг: 0 / 0
26.12.2013, 14:58
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513219
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
Дурацкий вопрос если честно.

- изменить тип колонки 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)
вариант из серии надо же что то написать.

В общем, я к первому варианту склоняюсь, но есть ощущение, что создатели теста ко второму
...
Рейтинг: 0 / 0
26.12.2013, 14:58
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513343
harisma
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
harisma
Гость
Какие еще будут варианты?
...
Рейтинг: 0 / 0
26.12.2013, 16:19
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513353
Антонио Перейро
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Антонио Перейро
Гость
harisma,

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

Код: sql
1.
where orderDate between cast(@date1 as char(10)) and cast(@date2 as char(10))
...
Рейтинг: 0 / 0
26.12.2013, 16:27
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513357
Фотография wizli
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wizli
Участник
Скорее всего это будет первый вариант. Если сделаем,так как описано во втором, никогда не будет index seek(SARG аргументы).
3 и 4 вариант вообще ни о чем, просто чтобы заполнить варианты ответа.
...
Рейтинг: 0 / 0
26.12.2013, 16:29
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513358
harisma
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
harisma
Гость
Антонио Перейро,

А можно "на пальцах" объяснить, почему замена этого условия приводит к ускорению. За счет чего? (файлы планов приложены в моем первом сообщении. Лучше всего, если объясните на них)
...
Рейтинг: 0 / 0
26.12.2013, 16:30
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513394
Антонио Перейро
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Антонио Перейро
Гость
harisma,

у Вас индекс по полю OrderDate. Он не будет работать если в условиях не используется это поле. Вы используете в условии не поле а функцию, при этом неизбежна операция сканирование таблицы. Если будете использовать конкретно это поле, будет поиск по индексу. Преобразование констант намного проще, с точки зрения производительности, нежели преобразование поля в предикате фильтарции (условие в where).
...
Рейтинг: 0 / 0
26.12.2013, 16:51
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513395
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
wizliСкорее всего это будет первый вариант. Если сделаем,так как описано во втором, никогда не будет index seek(SARG аргументы).
3 и 4 вариант вообще ни о чем, просто чтобы заполнить варианты ответа.
чего не будет то? там char с char сравнивается.
...
Рейтинг: 0 / 0
26.12.2013, 16:51
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513397
Фотография Exproment
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Exproment
Участник
harisma, ваши планы у меня не открываются.

Но по факту - прочитайте что такое SARG предикаты и как они соотносятся с преобразованием типов.
...
Рейтинг: 0 / 0
26.12.2013, 16:51
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513406
Фотография wizli
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wizli
Участник
Мистер ХенкиwizliСкорее всего это будет первый вариант. Если сделаем,так как описано во втором, никогда не будет index seek(SARG аргументы).
3 и 4 вариант вообще ни о чем, просто чтобы заполнить варианты ответа.
чего не будет то? там char с char сравнивается.
Здесь мой косяк, написал какую-то чушь
...
Рейтинг: 0 / 0
26.12.2013, 16:54
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513413
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
wizliМистер Хенкипропущено...

чего не будет то? там char с char сравнивается.
Здесь мой косяк, написал какую-то чушь
Просто на мой взгляд вариант этот идиотский потому что в вопросе ни разу не говорится в каком формате дата хранится в таблице и в какой формат она будет преобразовыватся при cast(@date as char(10)) . Если отбросить эти сомнения, то выходит вариант самый лучший, если сомневаться, то первый вариант - там получится сравнение дат, а не строк.
...
Рейтинг: 0 / 0
26.12.2013, 17:00
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513415
Антонио Перейро
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Антонио Перейро
Гость
Антонио Перейро,

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

Я бы еще хинт в запросе добавил - option(recompile)
Код: sql
1.
2.
3.
4.
5.
  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);
...
Рейтинг: 0 / 0
26.12.2013, 17:02
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513423
o-o
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
o-o
Гость
harismaАнтонио Перейро,

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
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?
...
Рейтинг: 0 / 0
26.12.2013, 17:12
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513434
Фотография Exproment
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Exproment
Участник
Мой вариант:
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-уровне индекса.
...
Рейтинг: 0 / 0
26.12.2013, 17:16
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513447
Антонио Перейро
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Антонио Перейро
Гость
o-o,

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

конверт использовать нужно. Просто поставил каст из за того что эта функция изначально в запросе и использовалась ))).
...
Рейтинг: 0 / 0
26.12.2013, 17:27
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513449
o-o
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
o-o
Гость
извиняюсь, там для чистоты эксперимента надо было в таблицу вот такое сложить,
хотя сути не меняет:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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))

дата, хранящаяся в виде строки, всегда ЗЛО
...
Рейтинг: 0 / 0
26.12.2013, 17:33
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513457
o-o
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
o-o
Гость
Антонио Перейроo-o,

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

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

вот именно: что у НИХ ТАМ использовалось, использовать вообще нельзя.
а настройки сервера...
ну давайте вместо того, чтоб 1 раз поместить дату в поле дата,
переставим сервер, перепишем процедуры через CONVERT,
а заодно еще гору функций изобретем по сложению дат в виде строк
...
Рейтинг: 0 / 0
26.12.2013, 17:42
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513484
o-o
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
o-o
Гость
ну и еще раз о сравнении строк:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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
будут совсем не те, что ожидались
...
Рейтинг: 0 / 0
26.12.2013, 18:06
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513981
Фотография daw
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daw
Участник
что, кто-то действительно сомневается, что правильный ответ первый?
второй не подходит, потому что cast строку с датой в приемлемом для сравнения с помощью between не даст.
третий и четвертый - ни о чем вообще. рассчитано, по-моему на тех, кто об оптимизации "что-то слышал". ну, там, про то что ордер бай замедляет выполнение, или что что-то с индексами для оптимизации делать надо.
...
Рейтинг: 0 / 0
27.12.2013, 10:57
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38513986
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
dawвторой не подходит, потому что cast строку с датой в приемлемом для сравнения с помощью between не даст.

Почему нет? Если в ISO стандарте поле с датой и региональная настройка сервера, то все ок.
...
Рейтинг: 0 / 0
27.12.2013, 11:06
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38514008
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
Мистер Хенкиdawвторой не подходит, потому что cast строку с датой в приемлемом для сравнения с помощью between не даст.

Почему нет? Если в ISO стандарте поле с датой и региональная настройка сервера, то все ок.
Потому что у условиях задачи нет ничего об ISO.
Первый вариант правильный.
...
Рейтинг: 0 / 0
27.12.2013, 11:24
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38514013
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
Владимир ЗатуливетерМистер Хенкипропущено...

Почему нет? Если в ISO стандарте поле с датой и региональная настройка сервера, то все ок.
Потому что у условиях задачи нет ничего об ISO.
Первый вариант правильный.
так нет и про отсутствие ))). но есть небольшой намек на то, что табла достаточна немаленькая 3000000 строк и вот так менять тип ради оптимизации одной процедурки?
...
Рейтинг: 0 / 0
27.12.2013, 11:29
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38514018
Фотография daw
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daw
Участник
Exproment,

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

можно поинтересоваться, зачем?
...
Рейтинг: 0 / 0
27.12.2013, 11:35
| Ответить | Цитировать | Написать  
Какой план выполнения лучше и быстрее и почему?
    #38514019
Фотография daw
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daw
Участник
Мистер Хенки,

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

гм. не подскажите, какие должны быть настройки сервера, чтобы cast выдавал строку с датой в iso-формате?
...
Рейтинг: 0 / 0
27.12.2013, 11:37
| Ответить | Цитировать | Написать  
25 сообщений из 36, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Какой план выполнения лучше и быстрее и почему?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=38513457&tid=1703799]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 47ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 61ms
get tp. blocked users:
 1ms
others: 14ms
total:  166ms
созд. / загр.: 166ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]