Как SQLDataReader обрабатывает действительно большие запросы?

Question

На самом деле я не уверен, что название точно описывает вопрос, но я надеюсь, что он достаточно близок.

У меня есть некоторый код, который выполняет SELECT из таблицы базы данных, которая, как я знаю, приведет к выделению около 1,5 миллионов строк. Данные в каждой строке не велики - возможно, 20 байтов в строке. Но это все еще 30 МБ данных. Каждая строка содержит номер клиента, и мне нужно что-то сделать с каждым клиентом.

Мой код выглядит примерно так:

SqlConnection conn = new SqlConnection(connString);
SqlCommand command = new SqlCommand("SELECT ... my select goes here", conn);
using (conn)
{
    conn.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            ... process the customer number here
        }
    }
}

Поэтому я просто перебираю всех клиентов, возвращенных SELECT.

Мой вопрос, это приводит к многократному чтению базы данных или только одному? Я предполагаю, что сетевые буферы недостаточно велики, чтобы вместить 30 МБ данных, так что же здесь делает.NET? Является ли результат SELECT где-то спрятанным, чтобы SQLDataReader откусывал строку каждый раз, когда Read() продвигает указатель? Или это возвращается в базу данных?

Причина, по которой я спрашиваю, состоит в том, что часть кода "... обработать номер клиента здесь" может занять некоторое время, поэтому для 1,5 миллиона клиентов этот код (цикл while выше) может занять много часов. Пока это происходит, мне нужно беспокоиться о других людях, блокирующих позади меня базу данных, или я в безопасности, зная, что я сделал свой один SELECT из базы данных, и я больше не вернусь?

Answer 1

Выбор будет выполнен как "одиночная монолитная транзакция". Баланс выходных данных кэшируется в SQL Server и передается в сеть, поскольку протокол определяет наличие буфера для его получения. SQL Server не будет каждый раз возвращаться в таблицы данных. Состояние данных в точке оригинала SELECT переданный он будет возвращен в вашу заявку. Если вы указали (NOLOCK), вы больше не будете влиять на данные. Другие люди могут читать и писать это; вы не увидите их изменений. Однако вы не закончили работу с SQL Server, пока последняя строка не окажется в буферах вашего сервера приложений, спустя несколько часов. В каждом "у меня есть место для большего количества, пожалуйста, будет сетевой трафик", но не заметно больше, чем все 30 МБ за один раз.

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

Answer 2

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

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

Ссылка http://msdn.microsoft.com/en-us/library/ms187602.aspx

Когда запрос передается для выполнения, SQL Server отправляет наборы результатов клиентам следующим образом:

1. SQL Server получает сетевой пакет от клиента, содержащий оператор Transact-SQL или пакет операторов Transact-SQL, которые должны быть выполнены.
2. SQL Server компилирует и выполняет инструкцию или пакет.
3. SQL Server начинает помещать строки набора результатов или нескольких наборов результатов из пакета или хранимой процедуры в сетевые пакеты и отправлять их клиенту. SQL Server помещает как можно больше строк набора результатов в каждый пакет.
4. Пакеты, содержащие строки результирующего набора, кэшируются в сетевых буферах клиента. Когда клиентское приложение извлекает строки, драйвер ODBC или поставщик OLE DB извлекает строки из сетевых буферов и передает данные клиентскому приложению. Клиент извлекает результаты по одной строке за раз в прямом направлении.

Набор результатов по умолчанию не предоставляется приложению в одном большом блоке. Результирующий набор кэшируется в сетевых буферах на клиенте. Приложение извлекает набор результатов по одной строке за раз. При каждой выборке поставщик OLE DB или драйвер ODBC перемещает данные из следующей строки в сетевом буфере в переменные в приложении. Приложения OLE DB, ODBC и ADO используют те же функции API для извлечения строк, которые они использовали бы для извлечения строк из курсора. Управляемый поставщик SqlClient использует класс SqlDataReader для предоставления набора результатов по умолчанию. Когда для MultipleActiveResultSets задано значение true, более одного SqlDataReader разрешено открывать в данный момент времени.

Ссылка: http://technet.microsoft.com/en-us/library/ms187602%28v=sql.105%29.aspx

Answer 3

Прежде всего, я собираюсь перенаправить вас к следующему вопросу о SO, в котором описано, как обрабатываются блокировки и т. д.:

Понимание блокировок SQL Server по запросам SELECT

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

Второй вопрос: что вы собираетесь делать с данными? Возможно, вам следует иметь в виду, что при обработке 1M+ записей хранимая процедура будет быстрее, поскольку она обрабатывает все в базе данных и поддерживает низкий трафик.

Answer 4

На клиенте ничего не кэшируется DataReader; он пытается передавать данные с сервера каждый раз, когда вы звоните Read(). Как это происходит (из опыта):

1. ExecuteReader()блоки, пока первый оператор Sql не создаст данные для возврата клиенту.
2. NextResult() блокирует, пока сервер либо
   1. Указывает, что на сервере не выполняется ничего дополнительного и "команда" фактически завершена.
   2. XOR последующий оператор в команде производит данные, которые возвращаются клиенту.
3. Read()блокируется, пока сервер не сможет передать клиенту следующую запись. (Да, это означает, что неупорядоченный выбор почти всегда начнет потоковую передачу клиенту раньше, чем упорядоченный эквивалент.)
   • Например, я наблюдал, как SqlServer потребовалось 15 секунд, чтобы начать потоковую передачу результатов (NextResult() вернулся), затем, через некоторое время, заблокируйте еще 15 секунд при вызове Read(); это было в SELECT с ORDER BY. (Идентичное поведение при выполнении запроса из SSMS.)
4. Общий текст команды будет выполняться синхронно с тем, как DataReader работает.
   • Т.е. если в вашей команде есть 2 оператора SELECT, оба из которых возвращают данные, второй оператор SELECT начнет выполняться на сервере только тогда, когда NextResult()называется. Но если первый возвращает нулевые результаты, второй начнет выполнение во время ExecuteReader(). (В любом случае вам всегда нужно будет позвонить NextResult() чтобы получить данные второго SELECT.)

К вашему сведению: мой опыт работы с (MS) Sql2019 и.Net Framework, и IIRC, это поведение все еще сохранялось в 2013 году.

Итак, чтобы прямо ответить на ваш вопрос

Ваш запрос представляет собой один оператор SELECT, который будет выполняться в своей собственной неявной транзакции. Как только сервер завершит поиск всех данных / строк для возврата, он снимет все блокировки, которые он мог получить в таблице (таблицах), и в этот момент ваш код не будет иметь прямого влияния на другие запросы, попадающие в эти те же таблицы.

Однако, пока вы не закончите все свои Read()вызовы, вы по-прежнему ограничиваете ресурсы на сервере, а также управляете монопольным доступом к этому соединению из пула соединений. Итак, в вашем примере вы хотите изменить свой while(reader.Read())цикл, чтобы просто захватить все данные в локальный объект. Затем напишите следующий цикл после того, как вы закрыли соединение, для выполнения вашего длительного процесса с этими данными.