Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Мистическая проблема даты 01.11.2017  [new]
K-Nick
Member

Откуда:
Сообщений: 135
Доброго времени суток.

Столкнулся сегодня с мистической проблемой. Вдруг начала катастрофически тормозить вьюха, при выборке по сегодняшней дате (01.11.2017)
Вьюха примерно такая:

CREATE VIEW
V1
AS
SELECT 
CAST(CAST(F1.Загружено AS DATE) AS DATETIME) AS Дата_загрузки, 
CONVERT(VARCHAR(8),F1.Загружено,108) AS Время_загрузки, 
F1.*,
F2.*,
F3.*
FROM ((F1
WITH (NOLOCK)
LEFT JOIN F2_MAX ON F1.ID = F2_MAX.F2_F1) 
LEFT JOIN F3_MAX ON F1.ID = F3_MAX.F3_F1) ;

Все таблицы не очень большие - до 100 тыс записей.
Индексы построены по всем полям, которые используются в запросах.
Так вот...
Запрос
SELECT * FROM V1 WHERE [Дата_загрузки]='11.01.2017'
выполняется примерно 2 мин.
А запрос
SELECT * FROM V1 WHERE [Дата_загрузки]='10.31.2017'
выполнятся 4 секунды, т.е. в 30 раз быстрее!!!

Размер выборки примерно одинаковый - около 600 записей.

Дальнейшие эксперименты показали:

SELECT * FROM V1 WHERE [Дата_загрузки]='10.02.2017' 
- 4 сек.
SELECT * FROM V1 WHERE [Дата_загрузки]='20171101' 
- 2 мин.
SELECT * FROM V1 WHERE [Дата_загрузки]>='10.31.2017'
- 4 сек.
SELECT * FROM V1 WHERE [Дата_загрузки]>='10.31.2017' AND [Дата_загрузки]<>'10.31.2017' 
- 4 сек.

Т.е. такое впечатление, что проблема именно в использовании даты '11.01.2017', а не в объеме данных.

При этом запросы
SELECT * FROM F2_MAX
и
SELECT * FROM F3_MAX
выполняются за доли секунды.

Обе вьюхи имеют примерно такой вид:

CREATE VIEW
F2_MAX
AS
SELECT *
FROM F2 
WHERE ((F2.ID2) In (SELECT Max(T.ID2) FROM F2 AS T GROUP BY T.F2_F1));

Подскажите, в чем может быть дело?
Сервер MS SQL 2012
1 ноя 17, 23:41    [20920606]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
UPDATE STATISTICS F1 WITH FULLSCAN
UPDATE STATISTICS F2_MAX WITH FULLSCAN
UPDATE STATISTICS F3_MAX WITH FULLSCAN

Сообщение было отредактировано: 2 ноя 17, 00:41
2 ноя 17, 00:00    [20920617]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Ну и давайте я Вам расскажу (даже смотреть план запроса не стану), что у Вас происходит внутри view F2_MAX.

SELECT *
FROM F2
WHERE ((F2.ID2) In (SELECT Max(T.ID2) FROM F2 AS T GROUP BY T.F2_F1));
Для начала идет сканирование таблицы F2. Затем Hash Match по столбцу F2_F1.
После этого сортировка по ID2 - и мы заходим в цикл Nested Loops с той же таблицей F2 по ID.
Но и это еще не все.
Поскольку есть select * - то мы в цикле nested loops ищем недостающие столбцы уточняющим запросом RID Lookup.
Который в отличие от Table scan не читает последовательно по несколько экстентов (8 страниц по 8Кбайт - это экстент, а их читается сразу несколько), а RID Lookup обрушивает на дисковую подсистему лавину чтений разных страниц по 8 Кбайт.

Потому что нужно ставить вместо view таблицу, а ее предварительно заполнять таким запросом
SELECT *
FROM F2 with (index=0)
WHERE ((F2.ID2) In (SELECT Max(T.ID2) FROM F2 AS T with (index=0) GROUP BY T.F2_F1));
Тогда будет два Table scan, один hash match и один Right Semi Join.

Удачи.

Сообщение было отредактировано: 2 ноя 17, 00:38
2 ноя 17, 00:08    [20920625]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Andy_OLAP

UPDATE STATISTICS F1 WITH FULLSCAN
UPDATE STATISTICS F2_MAX WITH FULLSCAN
UPDATE STATISTICS F3_MAX WITH FULLSCAN

Конечно же, для начала - ведь F2_MAX это view, а F2 это таблица.


UPDATE STATISTICS F1 WITH FULLSCAN
UPDATE STATISTICS F2 WITH FULLSCAN
UPDATE STATISTICS F3 WITH FULLSCAN
2 ноя 17, 00:13    [20920635]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
K-Nick,

Ах, да, пока не забыл.

CREATE VIEW
V1
AS
SELECT
CAST(CAST(F1.Загружено AS DATE) AS DATETIME) AS Дата_загрузки,
F1.Загружено за Реальная_Дата_загрузки,
CONVERT(VARCHAR(8),F1.Загружено,108) AS Время_загрузки,
F1.*,
F2.*,
F3.*
FROM ((F1
WITH (NOLOCK)
LEFT JOIN F2_MAX ON F1.ID = F2_MAX.F2_F1)
LEFT JOIN F3_MAX ON F1.ID = F3_MAX.F3_F1) ;

И выборка select * from V1 where Реальная_Дата_загрузки >= convert(datetime,'2017-11-01',120) and Реальная_Дата_загрузки < convert(datetime,'2017-11-02',120) .
И никаких cast и convert по тем полям, по которым делаете выборку. Не любит это MSSQL, очень сильно не любит.
2 ноя 17, 00:21    [20920655]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Модератор: Уважаемый, Andy_OLAP.

Во-первых, предупреждение за оверквотинг. Не обязательно в двух сообщениях цитировать всю простыню изначального поста: кругом не идиоты, и догадаются, кому вы отвечаете.

Во-вторых, последнюю простыню из мешанины текста и кода без тегов src я снес. Потрудитесь оформлять сообщения правильно.
2 ноя 17, 00:41    [20920679]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
aleks222
Guest
CREATE VIEW
V1
AS
SELECT 
CAST(CAST(F1.Загружено AS DATE) AS DATETIME) AS Дата_загрузки, -- плохо совместимо с индексом
CONVERT(VARCHAR(8),F1.Загружено,108) AS Время_загрузки, 
F1.*,
F2.*,
F3.*
FROM ((F1
WITH (NOLOCK)
LEFT JOIN F2_MAX ON F1.ID = F2_MAX.F2_F1) 
LEFT JOIN F3_MAX ON F1.ID = F3_MAX.F3_F1) ;

-- плохо совместимо с индексом
SELECT * FROM V1 WHERE [Дата_загрузки]='10.02.2017' 


Надо не лениться

CREATE VIEW
V1
AS
SELECT 
CAST(CAST(F1.Загружено AS DATE) AS DATETIME) AS Дата_загрузки, 
CONVERT(VARCHAR(8),F1.Загружено,108) AS Время_загрузки, 
F1.Загружено, -- совместимо с индексом
F1.*,
F2.*,
F3.*
FROM ((F1
WITH (NOLOCK)
LEFT JOIN F2_MAX ON F1.ID = F2_MAX.F2_F1) 
LEFT JOIN F3_MAX ON F1.ID = F3_MAX.F3_F1) ;

-- совместимо с индексом
SELECT * FROM V1 WHERE '10.02.2017' <= [Загружено] and [Загружено] < '11.02.2017'


Это ж ужос. Бедный сервер перехерачивает ВСЮ таблицу ради ОДНОГО F2_F1.
CREATE VIEW
F2_MAX
AS
SELECT *
FROM F2 
WHERE ((F2.ID2) In (SELECT Max(T.ID2) FROM F2 AS T GROUP BY T.F2_F1));


Учись, студент:
CREATE VIEW
V1
AS
SELECT 
CAST(CAST(F1.Загружено AS DATE) AS DATETIME) AS Дата_загрузки, 
CONVERT(VARCHAR(8),F1.Загружено,108) AS Время_загрузки, 
F1.Загружено, -- совместимо с индексом
F1.*,
F2.*,
F3.*
FROM F1
outer apply ( select top(1) * from F2 where F2_F1 = F1.ID order by ID2 desc ) as F2
--LEFT JOIN F2_MAX ON F1.ID = F2_MAX.F2_F1 
LEFT JOIN F3_MAX ON F1.ID = F3_MAX.F3_F1 ;
2 ноя 17, 06:09    [20920754]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
aleks222
Надо не лениться

Учись, студент:
SELECT 

F1.Загружено, -- совместимо с индексом
F1.*,

FROM F1

Ну и внутри view получается два столбца с наименованием "Загружено". Нужно не лениться, это точно, а написать select f1.столбец1 as f1_столбец1, f2.столбец1 as f2_столбец1. Еще и из F2 или F2 рано или поздно прилетит столбец с таким же наименованием, как в F1.
Автор темы, без обид, но за использование select * from в приличных местах или сурово отчитают, или подпишут увольнение с черной меткой. Не нужно раскидывать грабли, на которые обязательно прыгнет Ваш коллега, когда Вы в отпуск уйдете.
P.S. Ну сейчас вроде оформление на месте, а изложение компактное :)
2 ноя 17, 09:42    [20921003]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
K-Nick
Member

Откуда:
Сообщений: 135
Andy_OLAP
Ну и внутри view получается два столбца с наименованием "Загружено". Нужно не лениться, это точно, а написать select f1.столбец1 as f1_столбец1, f2.столбец1 as f2_столбец1. Еще и из F2 или F2 рано или поздно прилетит столбец с таким же наименованием, как в F1.
Автор темы, без обид, но за использование select * from в приличных местах или сурово отчитают, или подпишут увольнение с черной меткой. Не нужно раскидывать грабли, на которые обязательно прыгнет Ваш коллега, когда Вы в отпуск уйдете.
P.S. Ну сейчас вроде оформление на месте, а изложение компактное :)


Спасибо!
У меня так и сделано, просто перечень всех более чем сорока полей не имел смысла (в данном случае) и я сократил его до "звездочки".

На хорошие советы я не обижаюсь.
2 ноя 17, 10:39    [20921223]     Ответить | Цитировать Сообщить модератору
 Re: Мистическая проблема даты 01.11.2017  [new]
K-Nick
Member

Откуда:
Сообщений: 135
Всем большое спасибо за развернутые ответы.
Особенно за оператор "outer apply".
2 ноя 17, 10:41    [20921231]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить