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

Откуда:
Сообщений: 169
Подскажите как максимально эффективно получить данные..

Имеется таблица: ZAYAVKI (ID VARCHAR(20), DD DATE)
В ней содержится миллион строк.


Вторая таблица. HISTORY (ID VARCHAR(20), DD DATETIME , INCOME MONEY )
В ней сотни миллионов строк.



Необходимо к таблице заявки подтянуть поле income по следующему условию:
ZAYAVKI.ID = HISTORY.ID и HISTORY.DD = максимальная дата из HISTORY которая меньше даты из ZAYAVKI

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

select a.*,(select max(b.dd) from history b where a.id = b.id and b.dd < a.dd) from zayavki

Главная загвоздка в том что по одному ID в таблице HISTORY большое кол-во записей, бывает одна а бывает больше 100
16 ноя 17, 14:16    [20960046]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
assmsk
Member

Откуда:
Сообщений: 169
После подзапроса джойню по полученной дате, чтобы получить income
16 ноя 17, 14:19    [20960065]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
WarAnt
Member

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

люто долго потому что индекса нет (точнее можно сказать только по плану) а это так пальцем в небо.
короче давайте план.
16 ноя 17, 14:22    [20960089]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Имеется таблица: [b]ZAYAVKI [/b](ID VARCHAR(20), DD DATE)

Но в то же время

Вторая таблица. [b]HISTORY[/b] (ID VARCHAR(20), DD DATETIME , INCOME MONEY ) 


откуда вы такие беретесь то?!
16 ноя 17, 14:30    [20960129]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
А также ID варчар и дата DD. Начните оптимизацию запроса с устранения каши из головы.
16 ноя 17, 14:35    [20960156]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Гигабайт Мегабайтович Килобайтов
Member [заблокирован]

Откуда:
Сообщений: 5975
Cammomile - ну чего ты прицепился - )) таки варчар(20) не может быть примари кеем? ))
а чем он может быть хуже других? ))
16 ноя 17, 15:47    [20960546]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Владислав Колосов
Member

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

железо слабое, там же сортировка. Кроме того, сравнение варчаров и дат разных типов. Кластерный индекс по дате сэкономит сортировку в запросе.
16 ноя 17, 16:41    [20960804]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
aleks222
Guest
Владислав Колосов
assmsk,

железо слабое, там же сортировка. Кроме того, сравнение варчаров и дат разных типов. Кластерный индекс по дате сэкономит сортировку в запросе.


Теоретег, видно по нему...

select a.*, b.* 
  from zayavki as a
          outer apply ( select top(1) * from history where id = a.id and dd < a.dd order by dd desc ) as b
16 ноя 17, 18:22    [20961234]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
04cf9f9576a6f15
Member [заблокирован]

Откуда:
Сообщений: 2927
Гигабайт Мегабайтович Килобайтов
Cammomile - ну чего ты прицепился - )) таки варчар(20) не может быть примари кеем? ))
Может.
Гигабайт Мегабайтович Килобайтов
а чем он может быть хуже других? ))
Скоростью операций сравнения и сортировки. Также размером. Это приводит к сильным тормозам при недостатке оперативной памяти.

#Хэш=
20 ноя 17, 09:40    [20967961]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33265
Блог
assmsk,

вас ничто не спасет, если будете писать такой г-код, где джойны идут по разным типам данных
20 ноя 17, 18:30    [20970071]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30801
assmsk
Подскажите как максимально эффективно получить данные..

Имеется таблица: ZAYAVKI (ID VARCHAR(20), DD DATE)
В ней содержится миллион строк.


Вторая таблица. HISTORY (ID VARCHAR(20), DD DATETIME , INCOME MONEY )
В ней сотни миллионов строк.
Индексы покажите для начала.
assmsk
Джойню такая же история.

select a.*,(select max(b.dd) from history b where a.id = b.id and b.dd < a.dd) from zayavki

После подзапроса джойню по полученной дате, чтобы получить income
Ну понятно, медленно, запрос нужно переписать.
Но начать нужно с индексов
20 ноя 17, 19:34    [20970183]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Andy_OLAP
Member

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

вас ничто не спасет, если будете писать такой г-код, где джойны идут по разным типам данных

Думаю, коллега постепенно придет к использованию int ключей типа yyyymmdd для даты. Всему свое время.
21 ноя 17, 08:46    [20970917]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33265
Блог
Andy_OLAP
Критик
assmsk,

вас ничто не спасет, если будете писать такой г-код, где джойны идут по разным типам данных

Думаю, коллега постепенно придет к использованию int ключей типа yyyymmdd для даты. Всему свое время.


спорная идея, int - 4 байта, date - 3,
так стоит делать только, если очень хочется запихнуть в одну таблицу, скажем, квартальные, месячные, недельные и дневные расчеты, но тот же Кимбалл выступал резко против такого подхода

мне не понравилось выделенное сравнение:

ZAYAVKI (ID VARCHAR(20), DD DATE)
HISTORY (ID VARCHAR(20), DD DATETIME , INCOME MONEY )
select a.*,(select max(b.dd) from history b where a.id = b.id and b.dd < a.dd)

такие скрипты могут работать на порядки дольше, чем скрипты с джойнами столбцов с одинаковыми типами данных

ps работаю сейчас с ХД, где изначально заложились на yyyymmdd, для таблиц с десятками миллиардов это не хорошо
21 ноя 17, 18:21    [20973178]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Критик,
автор
такие скрипты могут работать на порядки дольше, чем скрипты с джойнами столбцов с одинаковыми типами данных

sql сравнивает date и datetime без преобразования
21 ноя 17, 18:32    [20973205]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Критик
спорная идея, int - 4 байта, date - 3,
ps работаю сейчас с ХД, где изначально заложились на yyyymmdd, для таблиц с десятками миллиардов это не хорошо

Не соглашусь, и любые ключи дат в хранилище всегда буду делать yyyymmdd, а то потом возникает желание у тех, кто смотрит в готовое DWH, приджойнить одну большую таблицу к другой извращенным образом, да строки выбрать внутри даты последние по возрастанию времени внутри одной из таблиц и так далее.
В общем, экономия одного байта того не стоит. Да и Enterprise быстрее связывает, когда столбцы int/bigint, если ничего не путаю.
Другое дело, когда загрузка в много потоков и нарезка схемой секционирования не только по дате, но и по часу внутри суток, чтобы утреннюю/вечернюю порцию быстро залить в текущую таблицу, а потом медленно скинуть в архивную с другой схемой секционирования - ну это да, тогда ключи можно и Date.
21 ноя 17, 18:33    [20973208]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
как же задолбали фантазёры...
21 ноя 17, 18:35    [20973216]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Andy_OLAP
Member

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

Полностью согласен с Вами, коллега, смотрят в DWH и начинают у них глаза гореть,а руки трястись от возбуждения.
И я Вам таки больше скажу - есть такие даже на этом форуме. Только никому не говорите, пусть это будет секрет!
21 ноя 17, 18:45    [20973246]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Andy_OLAP,

автор
быстрее связывает, когда столбцы int/bigint, если ничего не путаю.

это ваша фантазия, устаревшая > 10 назад. Продолжайте нести свой бред в массы
21 ноя 17, 18:47    [20973255]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Andy_OLAP
Member

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

автор
быстрее связывает, когда столбцы int/bigint, если ничего не путаю.

это ваша фантазия, устаревшая > 10 назад. Продолжайте нести свой бред в массы

Коллега, можно попросить Вас выступить в роли эксперта и предоставить ссылку на кошерную статью MSDN с описанием, как 10 лет назад с выходом новой версии MSSQL эта фантазия вмиг устарела? Было бы очень интересно прочитать. Заранее большое спасибо.
21 ноя 17, 18:50    [20973262]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Andy_OLAP
TaPaK
Andy_OLAP,

пропущено...

это ваша фантазия, устаревшая > 10 назад. Продолжайте нести свой бред в массы

Коллега, можно попросить Вас выступить в роли эксперта и предоставить ссылку на кошерную статью MSDN с описанием, как 10 лет назад с выходом новой версии MSSQL эта фантазия вмиг устарела? Было бы очень интересно прочитать. Заранее большое спасибо.

для такого бараналабола я даже гугл открывать не стану
21 ноя 17, 18:57    [20973288]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Баранов имеет в виду вот это:
Optimizing Data Warehouse Query Performance Through Bitmap Filtering
в сведению парнокопытных: ничем не подтвержденные утверждения
зовутся по-русски "слышал звон"
21 ноя 17, 19:56    [20973409]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Yasha123
Баранов имеет в виду вот это:
Optimizing Data Warehouse Query Performance Through Bitmap Filtering
в сведению парнокопытных: ничем не подтвержденные утверждения
зовутся по-русски "слышал звон"

Спасибо, коллега! Отличная ссылка. Отличная фраза в ней - "An integer-based column is preferred". Можете как эксперт вкратце пояснить, может быть, со времен SQL 2008 в этом плане что-то поменялось? Тогда эту статью на Microsoft TechNet нужно переписать. Составим петицию, я ссылку на нее скину в Редмонд.
21 ноя 17, 20:06    [20973421]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Andy_OLAP
Составим петицию, я ссылку на нее скину в Редмонд.

петицию?
да не вопрос.
главное, успокойтесь,
а то смотрят в DWH и начинают у них глаза гореть,а руки трястись от возбуждения

Картинка с другого сайта.
21 ноя 17, 20:38    [20973468]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33265
Блог
TaPaK
Критик,
автор
такие скрипты могут работать на порядки дольше, чем скрипты с джойнами столбцов с одинаковыми типами данных

sql сравнивает date и datetime без преобразования


возможно,
я встречал случай, когда тормоза были при int и smallint
21 ноя 17, 21:59    [20973571]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
Andy_OLAP
Member

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

Видимо, Вы дошли до момента, когда в таблицу столько строк переливается, что менее определенного процента - и далее статистика перестает по столбцу кластерного индекса (не важно, datetime/int/smallint) обновляться. Ну и select c1,c2,dt from table where dt>=param1 and dt<param2, а далее left join с какой-нибудь крупной таблицей превращается в nested loops.

Приходится секционировать и апдейтить статистику по перезаливаемым секциям. Это если SQL 2014 и далее.
Сначала включаете "ALTER DATABASE [MYDB] SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON)", а затем
"UPDATE STATISTICS dbo.BigTable(cluster_index_name) WITH RESAMPLE ON PARTITIONS(number_of_reload_partition)".
21 ноя 17, 22:44    [20973642]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить