Долгий запрос в MS SQL - как избежать выхода по тайм-ауту?

@Abejon · Регистрация: 21.03.2017

Студворк — интернет-сервис помощи студентам

Есть некий реально долгий запрос - порядка 30 минут выполняется.
Вызываю его через ADO.Net Естественно, что он отваливается по тайм-ауту.
Как этого избежать?

@MsGuns · 20.07.2021, 19:18

Время ожидания регулируется параметрами соединения либо конкретной транзакции.
Ну и, конечно, 30 мин - это реально очень долго. Есть смысл оптимизировать сам запрос. Как именно ?
Нужно знать исходную информацию:
1. К скольким таблицам обращается запрос
2. Объем этих таблиц как в сроках, так и в колонках
3. Каким образом выполняются связки между таблицами
4. Сколько записей в итоге извлекается
5. Есть ли внутри запроса обращения к SP/Udf и, если есть, насколько тормозят они. В частности, используют ли они временные таблицы, курсоры, обращаются к другим SP/Udf.
6. Есть ли вторичные индексы и, если есть, как они организованы и как используются
7. Есть ли в запросе рекурсия и оператор WITH
8. Используются ли подзапросы

В любом случае, надо смотреть план запроса

Добавлено через 3 минуты
Неплохо было бы взглянуть как на запрос, так и структуры таблиц, в нем использованных.

@Abejon · 20.07.2021, 19:37 **[ТС]**

Запрос вылизан. Ускорить его не удастся. В базе не один десяток миллионов записей. Показать его здесь я не могу, т.к. он не мой.
А как через транзакцию регулировать тайм-аут?

@MsGuns · 20.07.2021, 20:09

CommandTimeout

Добавлено через 32 секунды

Сообщение от Abejon

Ускорить его не удастся

Не верю

@HF · 20.07.2021, 21:54

Сообщение от Abejon

Запрос вылизан. Ускорить его не удастся. В базе не один десяток миллионов записей.

Не можете изменить запрос - можете ли перенести логику в код? Тогда бы и управляли выполнением.
А так бы - переносить всё в Задачи (Handfire, Quartz). И из них выгребать данные и потихоньку обрабатывать.

И если уж вы утверждаете что запрос (на 30+ минут) идеальный, то это всё равно задача не для выполнения из приложения. Я бы придумал "обёртку" в БД для запуска этого "запроса". Например запуск SQL-задачи. И чтения статуса выполнения.

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

Ну и у меня сомнения по адекватности запроса. Если нужны такие большие данные, то
- или это слишком старые данные, что можно изменить подход для работы с "архивом"
- или вы из Роскомнадзора и наши данные анализируете, то и работать должны с бигдатой и не выполнять запросы в mssql

- или всё же начать запросом управлять. Добавлять фильтрации и ограничивать количество данных

@Abejon · 21.07.2021, 05:42 **[ТС]**

Сообщение от MsGuns

CommandTimeout
... %D1%8B-sql

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

Добавлено через 32 минуты

Сообщение от MsGuns

CommandTimeout

Добавлено через 32 секунды

Не верю

Попробовал вызвать ExecuteReader в отдельном потоке

C#
1
                SqlDataReader reader = Task.Run(() =>cmd.ExecuteReader()).Result;

- не помогло. Вываливается по тайм-ауту, но возвращает пустую выборку.

Добавлено через 9 минут
Такой вызов

C#
1
SqlDataReader reader = await cmd.ExecuteReaderAsync();

тоже не работает. Всё осталось так же - возвращает пустой запрос, хотя данные должны быть.

@Usaga · 21.07.2021, 05:47

Сообщение от Abejon

Запрос вылизан. Ускорить его не удастся.

Так мы и поверили.

@Abejon · 21.07.2021, 06:03 **[ТС]**

Такой вызов

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public async Task<IEnumerable<OrganizationShortModel>> GetOrganizationsAsync(int fromId, int toId, int deepSize)
{
 ...
    SqlDataReader reader = await cmd.ExecuteReaderAsync();
...
}
public async void RunAsync()
{
...
    while (fromId < maxOrgId)
    { 
         IEnumerable<OrganizationShortModel> orgList = await _dataProvider.GetOrganizationsAsync(fromId, toId, _appSetings.DeepSize);
         if (orgList != null && orgList.Count() > 0)
         {
...
}

завершает работу программы, сразу же с кодом 0 при вызове ExecuteReaderAsync вот с таким сообщением

The thread 0x77b0 has exited with code 0 (0x0).
The thread 0x4238 has exited with code 0 (0x0).
The program '[8968] Tmh.exe' has exited with code 0 (0x0).

, хотя, как можно заметить, вызывается этот метод в цикле, и условия выхода из цикла не достигнуты.

@Usaga · 21.07.2021, 06:15

Сообщение от Abejon

public async Task<IEnumerable<OrganizationShortModel> > GetOrganizationsAsync

Сообщение от Abejon

if (orgList != null && orgList.Count() > 0)

А тут, случаем, не двойная ли материализация перечисления?

@Abejon · 21.07.2021, 06:45 **[ТС]**

Может быть. А как это понять?

@Usaga · 21.07.2021, 06:52

Abejon, как минимум в профилировщике СУБД можно увидеть два одинаковых запроса... Вы же сказали, что запрос "вылезан". Значит вы профилировщиком это проверяли. Там бы и увидели, что запрос повторяется...

@Abejon · 21.07.2021, 07:54 **[ТС]**

как минимум в профилировщике СУБД можно увидеть два одинаковых запроса... Вы же сказали, что запрос "вылезан". Значит вы профилировщиком это проверяли. Там бы и увидели, что запрос повторяется...

А что, это - двойная материализация перечисления - приведёт к завершению программы?
Кстати, при запуске в MSSMS запрос выполняется ровно то же время. Собственно, оттуда я и узнал длительность выполнения этого запроса.

@Usaga · 21.07.2021, 08:04

Abejon, оно приведёт к нескольким запросам.

Я не вижу полного вашего кода. Но при работе с IEnumerable очень легко можно нарваться на такую ситуацию.

@Abejon · 21.07.2021, 08:06 **[ТС]**

как минимум в профилировщике СУБД можно увидеть два одинаковых запроса... Вы же сказали, что запрос "вылезан". Значит вы профилировщиком это проверяли. Там бы и увидели, что запрос повторяется...

А что, это - двойная материализация перечисления - приведёт к завершению программы?
Кстати, при запуске в MSSMS запрос выполняется ровно то же время. Собственно, оттуда я и узнал длительность выполнения запроса.

@HF · 21.07.2021, 08:58

Сообщение от Abejon

Увеличил тайм-аут на команде - не помогло.

Покажите конкретную ошибку. Есть ещё таймаут выполнения действия в приложении - executionTimeout. Он тоже должен быть большой.

@Usaga · 21.07.2021, 09:10

Abejon, оно приведёт к нескольким запросам.

Я не вижу полного вашего кода. Но при работе с IEnumerable очень легко можно нарваться на такую ситуацию.

@Abejon · 21.07.2021, 09:21 **[ТС]**

Сообщение от HF

Покажите конкретную ошибку. Есть ещё таймаут выполнения действия в приложении - executionTimeout. Он тоже должен быть большой.

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

@carrotik · 21.07.2021, 09:45

Abejon,
.. подобные запросы логичнее было бы запускать в виде хранимых процедур по расписанию с сохранением результатов в плоскую таблицу (view - представление), а уже читать оттель ...

@MsGuns · 21.07.2021, 15:24

Abejon, Вам код в [8] не кажется странным ?

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

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

В нормальных базах нормальные запросы даже на сотнях миллионов записей летают.

Добавлено через 9 минут
И еще про "запрос", текст которого нельзя выложить.
Если это хранимка, к которой у Вас есть доступ, при этом доступа к самим объектам БД (таблицы, SP,Udf) доступа нет, - это одно дело. И тут Вам что-то посоветовать трудно, разве что применить кнут или пряник к ее автору.
Но если у Вас имеется сам полный код SQL со всеми потрохами, то что мешает над ним поколдовать самостоятельно :

Сообщение от HF

или всё же начать запросом управлять. Добавлять фильтрации и ограничивать количество данных

@Abejon · 24.07.2021, 14:19 **[ТС]**

Запрос удалось ускорить в 40 раз (sic!) применив операцию OPTION (RECOMPILE). Для меня это какая-то магия - впервые столкнулся с таким.

Новые блоги и статьи Все статьи Все блоги /
Отображение реквизитов в документе по условию и контроль их заполнения Maks 04.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "ПланированиеСпецтехники", разработанного в конфигурации КА2. Данный документ берёт данные из другого нетипового документа. . .	Фото всей Земли с борта корабля Orion миссии Artemis II kumehtar 04.04.2026 Это первое подобное фото сделанное человеком за 50 лет. Снимок называют новым вариантом легендарной фотографии «The Blue Marble» 1972 года, сделанной с борта корабля «Аполлон-17». Новое фото. . .	Вывод диалогового окна перед закрытием, если документ не проведён Maks 04.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: реализовать программный контроль на предмет проведения документа. . .	Программный контроль заполнения реквизита табличной части документа Maks 02.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: реализовать контроль заполнения реквизита "ПричинаСписания". . .
wmic не является внутренней или внешней командой Maks 02.04.2026 Решение: DISM / Online / Add-Capability / CapabilityName:WMIC~~~~ Отсюда: https:/ / winitpro. ru/ index. php/ 2025/ 02/ 14/ komanda-wmic-ne-naydena/	Программная установка даты и запрет ее изменения Maks 02.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: при создании документов установить период списания автоматически. . .	Вывод данных в справочнике через динамический список Maks 01.04.2026 Реализация из решения ниже выполнена на примере нетипового справочника "Спецтехника" разработанного в конфигурации КА2. Задача: вывести данные из ТЧ нетипового документа. . .	Программное заполнения текстового поля в реквизите формы документа Maks 01.04.2026 Алгоритм из решения ниже реализован на нетиповом документе "ВыдачаОборудованияНаСпецтехнику" разработанного в конфигурации КА2, в дополнении к предыдущему решению. На форме документа создается. . .

@Abejon 3 / 2 / 2 Регистрация: 21.03.2017 Сообщений: 297

	Долгий запрос в MS SQL - как избежать выхода по тайм-ауту? 20.07.2021, 18:50. Показов 9606. Ответов 20 Метки ado.net, c#, mssql (Все метки) Есть некий реально долгий запрос - порядка 30 минут выполняется. Вызываю его через ADO.Net Естественно, что он отваливается по тайм-ауту. Как этого избежать? 0

@MsGuns 1497 / 1238 / 245 Регистрация: 04.04.2011 Сообщений: 4,363
	20.07.2021, 19:18
	Время ожидания регулируется параметрами соединения либо конкретной транзакции. Ну и, конечно, 30 мин - это реально очень долго. Есть смысл оптимизировать сам запрос. Как именно ? Нужно знать исходную информацию: 1. К скольким таблицам обращается запрос 2. Объем этих таблиц как в сроках, так и в колонках 3. Каким образом выполняются связки между таблицами 4. Сколько записей в итоге извлекается 5. Есть ли внутри запроса обращения к SP/Udf и, если есть, насколько тормозят они. В частности, используют ли они временные таблицы, курсоры, обращаются к другим SP/Udf. 6. Есть ли вторичные индексы и, если есть, как они организованы и как используются 7. Есть ли в запросе рекурсия и оператор WITH 8. Используются ли подзапросы В любом случае, надо смотреть план запроса Добавлено через 3 минуты Неплохо было бы взглянуть как на запрос, так и структуры таблиц, в нем использованных. 0

@Abejon 3 / 2 / 2 Регистрация: 21.03.2017 Сообщений: 297
	20.07.2021, 19:37 [ТС]
	Запрос вылизан. Ускорить его не удастся. В базе не один десяток миллионов записей. Показать его здесь я не могу, т.к. он не мой. А как через транзакцию регулировать тайм-аут? 0

@Abejon 3 / 2 / 2 Регистрация: 21.03.2017 Сообщений: 297
	21.07.2021, 06:45 [ТС]
	Может быть. А как это понять? 0

@Usaga 14314 / 9399 / 1355 Регистрация: 21.01.2016 Сообщений: 35,435
	21.07.2021, 06:52
	Abejon, как минимум в профилировщике СУБД можно увидеть два одинаковых запроса... Вы же сказали, что запрос "вылезан". Значит вы профилировщиком это проверяли. Там бы и увидели, что запрос повторяется... 0

@Usaga 14314 / 9399 / 1355 Регистрация: 21.01.2016 Сообщений: 35,435
	21.07.2021, 08:04
	Abejon, оно приведёт к нескольким запросам. Я не вижу полного вашего кода. Но при работе с IEnumerable очень легко можно нарваться на такую ситуацию. 0

@Usaga 14314 / 9399 / 1355 Регистрация: 21.01.2016 Сообщений: 35,435
	21.07.2021, 09:10
	Abejon, оно приведёт к нескольким запросам. Я не вижу полного вашего кода. Но при работе с IEnumerable очень легко можно нарваться на такую ситуацию. 0

@carrotik 800 / 583 / 207 Регистрация: 21.02.2019 Сообщений: 2,095
	21.07.2021, 09:45
	Abejon, .. подобные запросы логичнее было бы запускать в виде хранимых процедур по расписанию с сохранением результатов в плоскую таблицу (view - представление), а уже читать оттель ... 1

@Abejon 3 / 2 / 2 Регистрация: 21.03.2017 Сообщений: 297
	24.07.2021, 14:19 [ТС]
	Запрос удалось ускорить в 40 раз (sic!) применив операцию OPTION (RECOMPILE). Для меня это какая-то магия - впервые столкнулся с таким. 0

Долгий запрос в MS SQL - как избежать выхода по тайм-ауту?

Решение