Быстродействие SqlDataReader

Класс SqlClient SqlDataReader

Класс SqlDataReader пространства имён Microsoft.Data.SqlClient предназначен для получения данных из базы. Как следует из названия, объекты данного класса только читают информацию, но не изменяют данные в базе. Компенсировать это ограничение с лихвой помогает идущая в комплекте ADO.NET возможность прямых SQL запросов вставки и обновления данных. Соответственно имени SqlDataReader предназначен для работы с базами данных MS SQL Server.

Функциональность работы с базой данных SQL Server построенную на ADO.NET: SqlConnection, SqlCommand, SqlDataReader очень быстро можно внедрить в веб приложение ASP.NET. Говоря на принятом сленге, такой способ получения и изменения данных имеет низкий порог знаний для входа.

В подготовленном исходнике веб приложения ASP.NET Core MVC можно посмотреть и провести сравнительные тесты на быстродействие считывания данных объектами класса SqlDataReader при различных компоновках. Теоретическая часть описана в данной статье, практический программный код в прикреплённом исходнике.

Роль SqlDataReader

Класс SqlDataReader предназначен только для последовательного считывания данных. Для временного хранения данных он не годится. Необходима оболочка для принятия данных. Пока не произошло считывание, объект SqlDataReader держит соединение открытым. В такой ситуации нельзя выполнять другие запросы.

SqlDataReader – неотъемлемое звено цепочки соединения: SqlConnection-> SqlCommand-> SqlDataReader-> получение данных-> соединение закрывается.

И хотя для новых запросов могут создаваться новые подключения, каждое такое подключение будет опять зафиксировано в открытом состоянии. Количество открытых соединений ограничено и при достижении максимума выбросится исключение:
System.InvalidOperationException: "Истекло время ожидания. Период тайм-аута истек до получения соединения из пула. Это могло произойти из-за того, что все соединения пула в использовании и достигнут максимальный размер пула." ("Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.")

Логично считать данные в промежуточный объект и вызвать метод SqlDataReader.Close(), тем самым освободив соединение. Простой способ считать данные через объект SqlDataReader предлагает класс DataTable (представляет одну таблицу в памяти). Метод DataTable.Load(IDataReader) считывает полные данные для одной таблицы. При запросе к нескольким таблицам требуется несколько объектов DataTable.

В чём суть временного промежуточного хранилища? В простейшем приложении можно, например, прочитать данные через SqlDataReader сразу в консоль. Но вот другой пример: в контроллере ASP.NET MVC были запрошены данные для вывода в представление, каким контейнером их передать? Не передавать же в качестве модели объект SqlDataReader и заблокировать на некоторое время соединение. Вот здесь и необходим промежуточный объект хранения извлечённых данных. И потом, в приложении, для удобного получения данных и рационального программного кода, неизбежно понадобится инкапсуляция процесса SqlConnection-> SqlCommand-> SqlDataReader-> получение данных-> соединение закрывается.

Создание команд для SQL-запросов

У компонентов ADO.NET узкая специализация: SqlConnection обеспечивает соединение с SQL Server, SqlDataReader читает данные. Команды, они же SQL-запросы, непосредственно выполняют объекты класса SqlCommand.

В случае выполнения SQL инструкции SELECT, именно класс SqlCommand создаёт экземпляр SqlDataReader для считывания запрошенных данных. Объект SqlDataReader нельзя создать конструктором, его объект создает исполняющая запрос команда в лице класса SqlCommand. Сначала команда отправляется в открытое соединение, после успешного выполнения SQL запроса возвращается объект SqlDataReader.

SqlCommand command = new("SELECT * FROM Table WHERE Id= '1'", connection);
SqlDataReader reader = command.ExecuteReader();

Теперь ридер может считывать запрошенные данные.

SqlCommand command = new("UPDATE Sciences SET NumberViews=@nv WHERE uri=@uri");
SqlDataReader reader = command.ExecuteReader();

Ридер возвращает только количество изменённых строк свойством RecordsAffected. Данных для чтения, конечно же, не будет.

int rowsUpdated = command.ExecuteNonQuery();

Многокомандные SQL запросы

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

SqlCommand command = new("SELECT * FROM  Test;");
SqlCommand command1 = new("SELECT * FROM  Test1;");
SqlCommand command2 = new("SELECT * FROM  Test2;");
SqlCommand command3 = new("SELECT * FROM  Test3;");
SqlCommand command3 = new("SELECT * FROM  TableData;");

SqlCommand compoundСommand = new("SELECT * FROM  Test;SELECT * FROM  Test1;SELECT * FROM  Test2;SELECT * FROM  Test3;SELECT * FROM  TableData;"

Быстродействие многокомандных SQL запросов

Сравнительные тесты на быстродействие чтения из базы данных. Из базы данных запрашиваются данные нескольких таблиц (5-ти штук), при этом запросы повторяются установленное количество раз (100).

В принципе это логично: на создание каждого объекта SqlCommand и отправки команды в соединение с базой данных затрачивается некоторое время.

SQL команды на обновление Binary данных

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

Если команда создаётся без применения параметров приходится сначала массив байтов сериализировать в строку шестнадцатеричных символов, и затем строку запроса явно конвертировать в формат SQL VARBINARY.

Stream streamImage = image.OpenReadStream();
byte[] bytesImage = new byte[image.Length];
streamImage.Read(bytesImage, 0, (int)image.Length);
string hexData = "0x" + Convert.ToHexString(bytesImage);

Формирование команды обновления бинарных данных. Строку необходимо явно конвертировать в формат SQL VARBINARY с помощью инструкции CONVERT().

string qs = "UPDATE TableData SET ImageName='" + image.FileName +
                        "', ImageData=CONVERT(VARBINARY(MAX), " + hexData + ") WHERE Id='1'";

SqlCommand command = new(gs);
int rowsUpdated = command.ExecuteNonQuery();

Обновление SQL Binary с помощью параметров

Или с применением входного параметра. Это рекомендуемый способ вставки и изменения бинарных, и вообще любых типов, данных. При этом способе не требуется преобразовывать байты в строку, достаточно в качестве значения SqlParameter.Value указать ссылку на массив.

SqlParameter binary = new()
{
      SqlDbType = SqlDbType.VarBinary,
      Direction = ParameterDirection.Input,
      ParameterName = "ImgData",
      Value = bytesImage
};
sqlParameters.Add(binary);

qs = "UPDATE TableData SET ImageName='" + image.FileName + "', ImageData=@ImgData WHERE Id='1'";

SqlCommand command = new(gs);
command.Parameters.Add(binary);
int rowsUpdated = command.ExecuteNonQuery();

Извлечение данных в DataTable

Наиболее удобный способ получить данные таблиц с объекта SqlDataReader - это использование класса DataTable. Класс DataTable хранит в памяти одну таблицу. Если через SqlDataReader запрошены данные из нескольких таблиц, то необходимо вызывать метод SqlDataReader.NextResult() для получения данных следующей таблицы. Вызов метода SqlDataReader.Read() позволяет считывать строки текущей таблицы.

...
using SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);

// Если имя таблицы не указывать, название определяется из метаданных
DataTable result = new(); // C# 9.0
result.Load(reader);
...

Если указан параметр CommandBehavior.KeyInfo, название таблицы в объекте DataTable извлекается из метаданных базы (т.е. название будет такое же как и в базе).

List<DataTable> listTables = new();
...
using SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);

while (reader.IsClosed == false)
{
	// Если имя таблицы не указывать, название определяется из метаданных
	DataTable result = new(); // C# 9.0
	result.Load(reader);

	listTables.Add(result);
}
...

Быстродействие заполнения DataTable

Можно данные от SqlDataReader считывать не только в объект DataTable, но и в специальный класс-модель таблицы.

Судя по тестам быстродействия с DataTable SqlDataReader работает несколько медленнее, чем с классом-моделью. Тест состоит из 100 повторов считывания данных в объект DataTable и в объект класса-модели таблицы.

Извлечение данных в альтернативный класс-модель происходит приблизительно в 1,5 раза быстрее.

// Таблица
public class Table
{
	public string Name { get; set; }
	public List<TableRow> TableRows { get; set; } = new();

	public Table(string name) => Name = name;

}

// Строка
public class TableRow
{
	public Dictionary<string, object> columns = new();
}

...
using SqlDataReader sqlDataReader = command.ExecuteReader(CommandBehavior.KeyInfo);

List<Table> tables = new();

while (sqlDataReader.HasRows == true)
{
    Table table = new(sqlDataReader.GetSchemaTable().Rows[0].Field<string>("BaseTableName"));


    // Читаем строки
    while (sqlDataReader.Read() == true)
    {
        TableRow tableRow = new();

        for (int i = 0; i < sqlDataReader.FieldCount; i++)
        {
            string colName = sqlDataReader.GetColumnSchema()[i].ColumnName;
            tableRow.columns.Add(colName, sqlDataReader.GetValue(colName));
        }

        table.TableRows.Add(tableRow);
    }

    tables.Add(table);

    // Переход на следующую таблицу
    sqlDataReader.NextResult();
}
...

<ul>
    <li>
        @foreach (Table table in Model)
        {
            <h4>Таблица: @table.Name</h4>
            <ul>
                @for (int i = 0; i < table.TableRows.Count; i++)
                {
                    <li>
                        <b>Строка @(i+1)</b>
                        @foreach (KeyValuePair<string, object> col in table.TableRows[i].columns)
                        {
                            <ul class="mb-3">
                                <li>@col.Key: @col.Value</li>
                            </ul>
                        }
                    </li>
                }
            </ul>
        }
    </li>
</ul>

Получение данных через SQL параметры

Функциональность ADO.NET даёт возможность получения и изменения данных различными способами. Можно работать исключительно SQL инструкциями, извлечением данных через SqlDataReader и др. Одним из рекомендуемых способов работы с таблицами базы данных это использование sql-параметров для извлечения, вставки и изменения данных.

При этом выполнение команд объектами SqlCommand может сводится к вызову метода Command.ExecuteNonQuery(). Так как при использовании команд объект SqlDataReader не требуется и даже при использовании command.ExecuteReader() в объект SqlDataReader данные не вносятся. Необходимо отметить, что при выполнении SQL инструкции SELECT нельзя смешивать способы получения данных через параметры и без использования таковых. В таком случае сработает исключение:
Microsoft.Data.SqlClient.SqlException: "Инструкцию SELECT, которая присваивает значение переменной, нельзя использовать вместе с операциями получения данных."

На изображении сравнительные тесты заполнения данными DataTable и использование SELECT запроса с параметрами SqlParameter. Как видно по времени, с параметрами работа происходит заметно быстрее. Скорость работы с sql параметрами сравнима со скоростью извлечения данных в класс-модель таблицы. А вот хоть DataTable удобная оболочка для табличной порции, заполнение её происходит медленнее. Вероятно это связано с внушительной функциональностью предлагаемой классом DataTable.

Как итог

ADO.NET предоставляет богатый выбор средств доступа к данным. Одним из важных компонентов является SqlDataReader для построчного считывания данных из баз SQL Server. Хотя SqlDataReader только читает данные, сопутствующий класс SqlCommand устраняет это ограничение и даёт возможность на чистом SQL языке исполнять любые команды для изменения данных в базе.

Исходник веб приложения ASP.NET MVC

Исходник содержит программные коды возможных вариантов применения классов SqlDataReader и SqlCommand из технологии SqlClient ADO.NET. В веб приложении ASP.NET MVC осуществляется доступ к данным SQL Server и вывод полученной информации на веб страницы. В комплекте с исходными кодами веб приложения идёт файл тестовой базы данных для SQL Server.

Для работы с исходником требуется среда программирования MS Visual Studio 2019, сервер баз данных - MS SQL Server, MS SQL Server Express (можно использовать локальный сервер MSSQLLocalDB, идущий в составе Visual Studio). Желательно MS SQL Server Management Studio для создания и редактирования таблиц.