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

Откуда:
Сообщений: 82
Есть такая заморочка. Две таблицы, около 100 миллиона записей. По структуре одно и то же - кластерный индекс по primary key, некластерные индексы на InsertDateTime и UpdateDateTime. Обе таблицы используют одну и ту же функцию для секционирования (по дате). Статистика обновлена. Разработчик жалуется, что запрос по одной таблице идёт быстро, вторая очень долго.

Идея запроса внизу. В быстром варианте оптимизатор планирует использовать clustered index scan, но при выполнении переключается на Nonclustered индексы. В медленном варианте упирается в clustered index scan. Вторая таблица даже на 30% меньше. Не могу понять, что не так. Индексы не фрагментированы, статистика обновлена.

DECLARE
@InsertDateTime DATETIME = '2017-03-03 10:50:00',
@UpdateDateTime DATETIME = '2017-03-03 10:50:00'

SELECT
col1,
, col2
, col3
....
, col70
FROM Table
WHERE ([InsertDateTime] between @InsertDateTime and GETDATE()) or ([UpdateDateTime] BETWEEN @UpdateDateTime AND GETDATE() )
OPTION(RECOMPILE)
3 мар 17, 06:41    [20259215]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
sqluru
Member

Откуда:
Сообщений: 82
Версия SQL - Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
3 мар 17, 06:52    [20259222]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
sqluru
Member

Откуда:
Сообщений: 82
Похоже, понял что происходит. Из первой таблицы ничего не возвращается, а из второй около 2.5 миллиона записей. Видимо SQL не нравится такой большой лукап? Если пробую TOP 1000, то такая же история, но TOP в этом деле не помощник.
Есть ли возможность как-то вытаскивать данные пакетами?
3 мар 17, 07:40    [20259245]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
aleks2
Guest
sqluru
Похоже, понял что происходит. Из первой таблицы ничего не возвращается, а из второй около 2.5 миллиона записей. Видимо SQL не нравится такой большой лукап? Если пробую TOP 1000, то такая же история, но TOP в этом деле не помощник.
Есть ли возможность как-то вытаскивать данные пакетами?


Вы куды, дальше то, "2.5 миллиона записей" деваете?
Чего с ими делать намереваетесь?

sqluru
Разработчик жалуется

Гнать надо такого "разработчика".
3 мар 17, 08:28    [20259295]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
Massa52
Member

Откуда:
Сообщений: 395
Может разработчикe PAGE(ing) надо оптимизировать(в сети лес и тележка этого добра)?
Не лупить GRID(или его аналог) на два лимона.
3 мар 17, 08:48    [20259333]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
sqluru
Member

Откуда:
Сообщений: 82
aleks2

Вы куды, дальше то, "2.5 миллиона записей" деваете?
Чего с ими делать намереваетесь?

Местный Data Warehouse это. Загружают данные из рабочей таблицы в постоянную. Надо или переписывать код, либо переходить на 2016, но с умом. Я бы эти рабочие таблицы сделал in memory (без данных), главное чтобы всю память не съели.

aleks2

Гнать надо такого "разработчика".

Да тут многих надо гнать, этот хоть старается хоть и топором.
3 мар 17, 09:14    [20259384]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
sqluru
aleks2
Вы куды, дальше то, "2.5 миллиона записей" деваете?
Чего с ими делать намереваетесь?

Местный Data Warehouse это. Загружают данные из рабочей таблицы в постоянную. Надо или переписывать код, либо переходить на 2016, но с умом. Я бы эти рабочие таблицы сделал in memory (без данных), главное чтобы всю память не съели.

aleks2
Гнать надо такого "разработчика".

Да тут многих надо гнать, этот хоть старается хоть и топором.


Только в соседней ветке про инмемори писали. Плохо написанному приложению и корявым запросом in memory конечно поможет, но это какой то костыль выходит.
Как на моей первой работе, я еще был маленький и только только начал изучать СУБД, от разработчиков (а это между прочим ПО в котором работает тысяч 30 человек) была инструкция, все БД должы быть на SSD иначе все жутко тормозило и результат оч долго ждали. Когда начал вникать, оказалось что у них даже индексов не было на самых важных полях, корявая архитектура, нарушение в некоторых местах 3 NF и т.д., так вот, для них костылем был SSD.
3 мар 17, 09:31    [20259428]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
30 тысяч в сумме, в каждой БД от силы работало по 100 человек, отдельный сервер в каждом филиале и репликация с центральным.
Забыл добавить, тогда SSD был оч дорогой и более сложные приложения прекрасно работали и без него.
3 мар 17, 09:38    [20259440]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
sqluru
Похоже, понял что происходит. Из первой таблицы ничего не возвращается, а из второй около 2.5 миллиона записей. Видимо SQL не нравится такой большой лукап? Если пробую TOP 1000, то такая же история, но TOP в этом деле не помощник.
Есть ли возможность как-то вытаскивать данные пакетами?
Запрос не работает, потом что оптимизатор не знает сколько записей ему придется лукапить, и потому считает что возможно фулл скан обойдется дешевле. Ошибка в проектировании.

Если запрашиваемые записи компактно расположены, то такой запрос возможно поможет.


select * FROM Table1 A 
inner join   (
select [primary_key_field]
FROM Table1
WHERE ([InsertDateTime] between @InsertDateTime and GETDATE()) 
union
select [primary_key_field]
FROM Table1
WHERE  ([UpdateDateTime] BETWEEN @UpdateDateTime AND GETDATE() )
) Q on A.[primary_key_field] = Q.[primary_key_field]


Примерно так, или через промежуточную таблицу собрать [primary_key_field], если так оптимизатор обмануть не получиться.
3 мар 17, 10:35    [20259579]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
Поправил
Deff
не знает сколько записей ему придется лукапить

"не знает сколько страниц ему придется запросить при лукапе"
3 мар 17, 10:45    [20259621]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32169
sqluru
Похоже, понял что происходит. Из первой таблицы ничего не возвращается, а из второй около 2.5 миллиона записей.
Странно, и неужели при этом времена запросов отличаются??? :-)
3 мар 17, 11:00    [20259674]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
бред какой-то царит в теме....


автор
некластерные индексы на InsertDateTime и UpdateDateTime.
это 2 индекса? описание не в вольном пересказе покажите и план а то "не знает сколько страниц ему придется запросить при лукапе" заставляет проснуться
3 мар 17, 11:08    [20259709]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
aleks2
Guest
sqluru
aleks2
Вы куды, дальше то, "2.5 миллиона записей" деваете?
Чего с ими делать намереваетесь?

Местный Data Warehouse это. Загружают данные из рабочей таблицы в постоянную. Надо или переписывать код, либо переходить на 2016, но с умом. Я бы эти рабочие таблицы сделал in memory (без данных), главное чтобы всю память не съели.

aleks2
Гнать надо такого "разработчика".

Да тут многих надо гнать, этот хоть старается хоть и топором.


Робятки, скопировать "2.5 миллиона записей" - это надо время.
В любом случае.

Так што, возможно, следует гнать не разработчика, а начальника.
3 мар 17, 13:43    [20260336]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
sqluru
Member

Откуда:
Сообщений: 82
TaPaK
бред какой-то царит в теме....

автор
некластерные индексы на InsertDateTime и UpdateDateTime.
это 2 индекса?

Да, два отдельных индекса.

CREATE NONCLUSTERED INDEX [IX_TableA_InsertDateTime] ON [TableA]
(
[InsertDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

CREATE NONCLUSTERED INDEX [IX_TableA_UpdateDateTime] ON [TableA]
(
[UpdateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO

Deff
Примерно так, или через промежуточную таблицу собрать [primary_key_field], если так оптимизатор обмануть не получиться.

Оно может и сработало бы, проблема в том что primary key состоит из 8(!) полей. Боюсь, что будет ещё страшнее...
6 мар 17, 02:57    [20265565]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
sqluru
Deff
Примерно так, или через промежуточную таблицу собрать [primary_key_field], если так оптимизатор обмануть не получиться.

Оно может и сработало бы, проблема в том что primary key состоит из 8(!) полей. Боюсь, что будет ещё страшнее...
select [primary_key_fields]
FROM Table1
WHERE ([InsertDateTime] between @InsertDateTime and GETDATE()) 
union
select [primary_key_fields]
FROM Table1
WHERE  ([UpdateDateTime] BETWEEN @UpdateDateTime AND GETDATE()

Вот это в любом случае отработает быстро.
6 мар 17, 07:10    [20265623]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
o-o
Guest
Deff
sqluru
пропущено...

Оно может и сработало бы, проблема в том что primary key состоит из 8(!) полей. Боюсь, что будет ещё страшнее...
select [primary_key_fields]
FROM Table1
WHERE ([InsertDateTime] between @InsertDateTime and GETDATE()) 
union
select [primary_key_fields]
FROM Table1
WHERE  ([UpdateDateTime] BETWEEN @UpdateDateTime AND GETDATE()

Вот это в любом случае отработает быстро.

А select 1 еще быстрее.
Только у него dwh и перелив таблицы по условию. Все поля, а не те, что вам понравились
6 мар 17, 08:22    [20265680]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
o-o
Deff
пропущено...
select [primary_key_fields]
FROM Table1
WHERE ([InsertDateTime] between @InsertDateTime and GETDATE()) 
union
select [primary_key_fields]
FROM Table1
WHERE  ([UpdateDateTime] BETWEEN @UpdateDateTime AND GETDATE()


Вот это в любом случае отработает быстро.

А select 1 еще быстрее.
Только у него dwh и перелив таблицы по условию. Все поля, а не те, что вам понравились
Не обязательно в данном случае переливать одной транзакцией.
6 мар 17, 08:57    [20265725]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
sqluru
Member

Откуда:
Сообщений: 82
Спасибо всем! По-любому, если данных немного, то отрабатывает по некластерному индексу в обеих случаях. Если много, то вариант
Deff
с UNION лучше. Пробовал на count(*) - при возврате 3 миллионов записей:

set statistics io on
set statistics time on

SELECT count(*) as CountRows
FROM TableA
WHERE ([InsertDateTime] between @InsertDateTime and GETDATE())
OR ([UpdateDateTime] BETWEEN @UpdateDateTime AND GETDATE())
OPTION(RECOMPILE)

CPU time = 11451 ms, elapsed time = 9707 ms.

Если разбить на 2 с UNION, то:

SELECT count(*) as CountRows
FROM TableA
WHERE [InsertDateTime] between @InsertDateTime and GETDATE()

UNION

SELECT count(*) as CountRows
FROM TableA
WHERE [UpdateDateTime] between @UpdateDateTime and GETDATE()
OPTION(RECOMPILE)

CPU time = 345 ms, elapsed time = 139 ms.

Хотя результаты всё время разные, но тенденция видна. Для чистоты эксперимента надо буфер вычищать каждый раз, что в данном варианте не могу позволить.
7 мар 17, 06:34    [20269380]     Ответить | Цитировать Сообщить модератору
 Re: Некластерный индек работает для одной таблицы, но не для другой.  [new]
o-o
Guest
Еще один смешной человек.
Посчитать число строк можно всегда, подняв некластерный индекс, лишь бы он был нефильтрованный.
И это один план.
А поднять все поля, особенно которых НЕТ в некластерном, это другой план, тк поля все же выковыривать придется из кластерного.
Но если вас устраивает вместо заполненной таблицы всего лишь число строк, которые могли бы туда уйти, то я не понимаю, чем не устраивает select 1.
Тоже какое-то число вернет, и гораздо быстрее
7 мар 17, 07:45    [20269411]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить