Организация работы с базой данных посредством сервисов WCF

 

Часть 1. Создание тестовой базы данных.

   Целью данной статьи является создание сервиса при помощи технологии Windows Communication Foundation для демонстрации слоя доступа к базе данных в контексте сервис-ориентированной архитектуры.

  Первым шагом является разработка тестовой базы данных, к которой будут осуществляться запросы.

  Для этого выполним следующую последовательность действий:

  1) Открыть приложение Microsoft SQL Server Management Studio и подключится к серверу баз данных на локальном компьютере или на удаленном хосте;

  2) Создадим базу данных выполнив следующий скрипт:

CREATE DATABASE [ProductsDB] ON PRIMARY

( NAME = N'ProductsDB',

FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\ProductsDB.mdf' ,

SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON ( NAME = N'ProductsDB_log',

FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\ProductsDB_log.ldf' ,

SIZE = 1024KB , FILEGROWTH = 10%) GO

   При необходимости можно изменить параметр FILENAME для файла базы данных и лог-файла.

   3) Создадим таблицу Products в ранее созданной базе данных ProductsDB, выполнив следующий скрипт:

USE [ProductsDB] GO

CREATE TABLE [dbo].[tb_Products]

( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [Description] [nvarchar](500) NOT NULL,

[Price] [int] NOT NULL, CONSTRAINT [PK_tb_Products] PRIMARY KEY CLUSTERED

( [Id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

   4) Для тестирования различных способов взаимодействия с базой данных создадим тестовую хранимую процедуру, которая будет возвращать записи из таблицы tb_Products, у которых поле Name будет совпадать с параметром @Name, переданным в хранимую процедуру:

CREATE PROCEDURE sp_GetProductsByName

@Name nvarchar(50)

AS BEGIN

SELECT Id, Name, Description, Price FROM tb_Products WHERE Name LIKE '%' + @Name + '%';

END GO

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

 

Часть 2. Разработка WCF сервиса.

Следующий этап – создание кода ля выполнения запросов к базе данных средствами языка программирования C#.

Откроем среду разработки Microsoft Visual Studio 2010 и создадим новый проект, выбрав тип проекта – ASP.NET Empty Web Application. Проекты на основе языка программирования ASP.NET идеально подходят для быстрого создания и развертывания сервисов WCF. Назовем проект «WCF_DataAccess».

Теперь приступим к созданию WCF сервиса. Перейдем в Solution Explorer и поставив курсор на узел проекта нажмем правую кнопку мыши. Далее в контекстном меню необходимо выбрать пункт «Add – New Item». Далее на вкладке Web нужно выбрать тип файла – «WCF Service». Назовем файл «DbService.svc».

Среда выполнения добавит в наш проект два новых файла «DbService.svc» и «IDbService.cs»:

Также необходимо создать вспомогательный класс ProductBO, который будет выполнять сразу несколько функций – это будет оболочка для записи из таблицы tb_Products, а также контракт данных для использования его в WCF. Сигнатура класса приведена ниже:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;
namespace WCF_DataAccess

{
   [DataContract]
   public class ProductBO
   {
       [DataMember]
       public int Id { get; set; }
       [DataMember]
       public string Name { get; set; }
       [DataMember]
       public string Description { get; set; }
       [DataMember]
       public int Price { get; set; }
}
}

Аттрибут [DataContract] означет что этот класс может использоваться для передачи данных между сервисом WCF и его клиентами. Атрибут [DataMember] указывает какие поля данных должны быть включены в сообщение при передаче по каналам связи WCF.

Теперь мы можем перейти к написанию сервиса. Сервис в WCF сосотит из двух частей:

1) Контракт сервиса – это файл «IDbService.cs». В этом файле находится контракт сервиса – интерфейс на языке C#, который помечен атрибутом [ServiceContract]. Интерфейс содержит набор определений методов, которые помечены атрибутом [OperationContract]. Данный интерфейс называется контрактом сервиса, птомучто он декларирует точное описание методов, которые будет предоставлять своим клиентам WCF сервис. Код интерфейса приведен ниже:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
namespace WCF_DataAccess
{
     [ServiceContract]
      public interface IDbService
      {
          [OperationContract]
          List<ProductBO> GetAllProducts();
          [OperationContract]
          void InsertProduct(ProductBO product);
          [OperationContract]
          void DeleteProductById(int productId);
         [OperationContract]
         List<ProductBO> GetProductsByName(string name);
     }
}

2) Реализация сервиса – файл «DbService.svc». Здесь находится класс DbService, который наследует и реализует контракт сервиса – интерфейс IDbService. Поскольку мы будем работать с базой данных через наш сервис, класс DbService будет также реализовывать интерфейс IDisposable.

public class DbService : IDbService, IDisposable

Класс будет содержать одно поле и конструктор:

private SqlConnection _connection;
public DbService()
{
    string connectionString = ConfigurationManager.ConnectionStrings["ProductsDBConnectionString"].ConnectionString;
    _connection = new SqlConnection(connectionString);
    _connection.Open();
}

В конструкторе создается соединение с базой данных. Строка соединения берется из файла конфигурации Web.config, где она имеет следующий вид:

<configuration>
     <connectionStrings>
            <add name="ProductsDBConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=ProductsDB;Integrated Security=True" />
      </connectionStrings>

Приступим к реализации методов сервиса:

Выборка всех продуктов из таблицы:

public List<ProductBO> GetAllProducts()
{
       string query = "SELECT Id, Name, Description, Price FROM tb_Products";
       SqlCommand command = new SqlCommand(query, _connection);
       SqlDataReader dataReader = command.ExecuteReader();
       List<ProductBO> products = new List<ProductBO>();
       while (dataReader.Read())
      {
            products.Add(new ProductBO()
           {
               Id = dataReader.GetInt32(0),
               Name = dataReader.GetString(1),
               Description = dataReader.GetString(2),
               Price = dataReader.GetInt32(3)
          });
     }
     return products;
}

Добавление записи в таблицу продуктов:

public void InsertProduct(ProductBO product)
{
       string query = "INSERT INTO tb_Products VALUES (@name, @desc, @price);";
       SqlCommand command = new SqlCommand(query, _connection);
       command.Parameters.Add(new SqlParameter()
       {
            DbType = DbType.String,
            Value = product.Name,
            ParameterName = "name"
      });
      command.Parameters.Add(new SqlParameter()
      {
           DbType = DbType.String,
           Value = product.Description,
           ParameterName = "desc"
     });
     command.Parameters.Add(new SqlParameter()
     {
          DbType = DbType.Int32,
          Value = product.Price,
          ParameterName = "price"
     });
     command.ExecuteNonQuery();
}

Удаление продукта из таблицы по его Id:

public void DeleteProductById(int productId)
{
        string query = "DELETE FROM tb_Products WHERE Id = @id";
        SqlCommand command = new SqlCommand(query, _connection);
        command.Parameters.Add(new SqlParameter()
       {
            DbType = DbType.Int32,
            Value = productId,
            ParameterName = "id"
       });
       command.ExecuteNonQuery();
}

Выборка продуктов с фильтрацией продуктов по полю Name, при помощи хранимой процедуры:

public List<ProductBO> GetProductsByName(string name)
{
         SqlCommand command = new SqlCommand();
         command.Connection = _connection;
         command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "sp_GetProductsByName";
        command.Parameters.Add(new SqlParameter()
        {
             DbType = DbType.String,
             Value = name,
             ParameterName = "Name"
        });
        SqlDataReader dataReader = command.ExecuteReader();
        List<ProductBO> products = new List<ProductBO>();
        while (dataReader.Read())
        {
              products.Add(new ProductBO()
              {
                    Id = dataReader.GetInt32(0),
                    Name = dataReader.GetString(1),
                    Description = dataReader.GetString(2),
                    Price = dataReader.GetInt32(3)
              });
       }
       return products;
}

И последний метод – член интерфейса IDisposable – Dispose:

public void Dispose()
{
       _connection.Close();
}

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

На этом написание кода сервиса завершено. Попробуем его запустить, для этого в Solution Explorer необходимо установить курсор на файл DbService.svc и в контекстном меню выбрать пункт “View In Browser”. После нажатия откроется окно браузера. Адрес страницы, которая откроется в браузере, и будет адресом нашего сервиса(он нам понадобится позже, для тестирования сервиса). В окне браузера должно быть приблизительно следующее содержимое:

Это значит, что наш сервис правильно настроен и работает. На этом разработка сервиса завершена. Давайте проверим его в действии.

 

Часть 3. Разработка клиента для тестирования сервиса.

Для тестирования сервиса создадим простое консольное приложение:

Для работы с сервисом нам понадобится ссылка на этот сервис. Чтобы ее добавить необходимо в Solution Explorer установить курсор на узел проекта и выбрать пункт «Add Service Reference». На экране появится следущее окно:

В поле Address необхимо ввести адрес из браузера и нажать кнопку Go. Затем в поле Namespace указать значение «Service Refference» - это пространство имен в котором будут расположены классы для работы с сервисом. В конце жмем кнопку «Ok».

Модифицируем файл Program.cs внеся туда код для использования нашего сервиса:

class Program
{
    static void Main(string[] args)
    {
          var service = new DbServiceClient();
          Console.WriteLine("Add product: ");
          ProductBO newProduct = new ProductBO() { Name = "Book", Description = "Classic", Price = 5 };
          service.InsertProduct(newProduct);
          Console.WriteLine("Success!\n");
          Console.WriteLine("Add product: ");
          newProduct = new ProductBO() { Name = "Pencil", Description = "Red", Price = 1 };
          service.InsertProduct(newProduct);
          Console.WriteLine("Success!\n");
          Console.WriteLine("Products list:");
          List<ProductBO> products = service.GetAllProducts().ToList();
          string productFormat = "Id - {0}, Name - {1}, Desc - {2}, Price - {3}";
          products.ForEach(product => Console.WriteLine(String.Format(productFormat, product.Id, product.Name,      product.Description, product.Price)));
          Console.WriteLine("Search by Name 'Book':");
          products = service.GetAllProducts().ToList();
          products.ForEach(product => Console.WriteLine(String.Format(productFormat, product.Id, product.Name,  product.Description, product.Price)));
          Console.WriteLine("Delete product with Id = 1: ");
          service.DeleteProductById(1);
          Console.WriteLine("Success!");
   }
}

Теперь нажав комбинацию клавиш Ctrl + F5 можно запустить тестовую программу.

На этом написание всех модулей системы завершено. Спасибо за внимание.

Оптимизация баз Microsoft SQL Server: Часть 2. Индексы на практике?

 

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

   Напомню вам определение схемы таблицы Staffs, с которой мы будем работать:

 

Таблица Staffs
Id	Int
Name	Nvarchar(50)
Surname	Nvarchar(50)
Salary	Int
Address	Nvarchar(50)
Telephone	Nvarchar(50)
Job	Nvarchar(50)
Photo	image

  Допустим нам необходимо для таблицы Staffs создать некластеризованный индекс, который будет оптимизировать следующий запрос:

SELECT Id, Name, Job FROM Stuffs WHERE SALARY > 1000 AND Photo IS NOT NULL

   Ключом индекса будут столбцы SALARY и Photo, так как по этим полям производится фильтрация выборки. А столбы Id, Name и Job будут включенными в индекс столбцами.

   Общий синтаксис команды следующий:

USE <database_name> GO

CREATE NONCLUSTERED INDEX <index_name> ON <table_name> ( <column_name> ASC -- столбцы ключа индекса )

INCLUDE ( <column_name> -- включенные столбцы ) GO

   В нашем случае запрос будет выглядеть следующим образом:

CREATE NONCLUSTERED INDEX IDX_StaffsSearch ON Stuffs

( Salary, Photo ) INCLUDE ( Id, Name, Job ) GO

   Мы создали некластеризованный индекс. А точнее некластеризованный покрывающий индекс. Это значит, что в индексе есть все необходимые для выполнения запроса поля и SQL Server при выполнении запроса не будет обращаться к базовой таблице.

   Если бы наш код был следующим:

CREATE NONCLUSTERED INDEX IDX_StaffsSearch ON Stuffs

( Salary, Photo ) INCLUDE ( Id ) GO

   То в данном случае индекс перестает быть покрывающим, поскольку не включает в себя все столбцы, используемые в запросе. Оптимизатор все равно будет использовать данный индекс при выполнении запроса, однако его эффективность на порядок снизиться, поскольку потребуются обращения к базовой таблице.

   Кластеризованный индекс создается при помощи следующей команды:

CREATE CLUSTERED INDEX IDX_Stsffsid ON Stuffs (Id)

   Здесь был создан уникальный кластерный индекс, построенный на основе первичного ключа таблицы (столбца Id).

 

Реальный пример

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

   Создадим новую базу данных:

CREATE DATABASE TestDB;

   И единственную таблицу Customers, которая будет состоять из четырех столбцов:

USE [TestDB] GO

CREATE TABLE [dbo].[Customers](

[Id] [int] NOT NULL, [Num1] [int] NULL, [Num2] [int] NULL, [Num3] [int] NULL) GO

   Теперь заполним нашу таблицу случайными данными. Столбец Id будет наращиваться в цикле, а остальные три колонки таблицы будут заполняться случайными числами при помощи своеобразного варианта функции random:

USE TestDB GO

DECLARE @i int = 0;

WHILE (@i < 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(checksum(newid())), abs(checksum(newid())), abs(checksum(newid())) ) SET @i = @i + 1; END

   Данный скрипт вносит в таблицу полмиллиона записей, поэтому будьте терпеливы, скрипт будет работать не менее 3 минут.

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

  Чтобы включить режим сбора статистики необходимо выполнить следующую команду:

SET STATISTICS IO ON

   Теперь после выполнения каждого запроса на вкладке Messages нам будет доступна статистика выполнения данного запроса, как показано ниже:

   Нас интересует только значение параметра logical reads.

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

  1) SELECT Id, Num1, Num2 FROM Customers WHERE Id = 2000

  2) SELECT Id, Num1, Num2 FROM Customers WHERE Id >= 0 AND Id < 1000

  3) SELECT Id, Num1, Num2 FROM Customers WHERE Id >= 0 AND Id < 5000

  Данные запросы вернут соответственно 1 строку, 1000 строк и 5000 строк. Без индексов показатель производительности(количество логических считываний) для всех запросов одинаковый и равен 1621. Внесем данные в таблицу результатов:

	Запрос 1	Запрос 2	Запрос 3
Без индексов	1621	1621	1621

  Теперь создадим для таблицы Customers непокрывающий индекс:

USE TestDB GO

CREATE NONCLUSTERED INDEX TestIndex1 ON dbo.Customers(Id);

  Теперь выполним приведенные выше запросы еще раз и посмотрим на счетчик производительности:

	Запрос 1	Запрос 2	Запрос 3
Без индексов	1621	1621	1621
Непокрывающий индекс	4	1632	1632

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

   Теперь создадим покрывающий индекс, тем самым достигнув максимальной производительности.

   Для начала удалим предыдущий индекс:

USE TestDB GO DROP INDEX Customers.TestIndex1

   И создадим новый индекс:

USE TestDB GO

CREATE NONCLUSTERED INDEX TestIndex2 ON dbo.Customers(Id) INCLUDE (Num1, Num2);

   Теперь выполним наши запросы в третий раз и запишем результаты в таблицу:

	Запрос 1	Запрос 2	Запрос 3
Без индексов	1621	1621	1621
Непокрывающий индекс	4	1632	1632
Покрывающий индекс	3	7	20

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

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

Оптимизация баз Microsoft SQL Server: Часть 1. Зачем нужны индексы?

 

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

   В качестве наиболее эффективных решений для повышения производительности базы данных отмечают следующие:

   1) Создание репликаций базы данных

   2) Шардинг базы данных

   3) Индексирование таблиц базы данных с целью оптимизации запросов

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

   Итак, разберемся, что такое индекс, и какие типы индексов мы можем создавать при разработке баз данных.

   Индексы создаются для таблицы и, по сути, являются копией этой таблицы. Различие состоит в том, что индекс может включать не все столбцы исходной таблицы, а лишь те которые указал разработчик. Рассмотрим пример. У нас есть таблица Stuffs, которая содержит 10 колонок и упорядоченный индекс IDX_StuffsSalary с ключом Salary, который содержит только два столбца из таблицы Stuffs:

Таблица Staffs
Id	Int
Name	Nvarchar(50)
Surname	Nvarchar(50)
Salary	Int
Address	Nvarchar(50)
Telephone	Nvarchar(50)
Job	Nvarchar(50)
Photo	image

Индекс IDX_StaffsSalary
Salary	Int
Name	Nvarchar(50)

   Как это работает и в чем тут оптимизация? Допустим, есть следующий запрос:

SELECT Name FROM Stuffs WHERE Salary > 400 AND Salary < 1000

   Если бы не было индекса, данный запрос работал бы только с основной таблицей, и происходило бы следующее: для проверки значения колонки Salary из базы будет извлекаться вся строковая запись, то есть значения всех колонок таблицы. В случае наличия индекса, механизм SQL Server вообще не будет обращаться к таблице Staffs, работая полностью с данными в индексе, так как индекс содержит все, необходимые для выполнения запроса, данные. Таким образом, вместо извлечения на каждой итерации десяти колонок – извлекается только две. Поскольку индекс уже отсортирован по его ключу – полю Salary, запрос выполнится еще быстрее, за счет прохождения меньшего количества строк. Когда в таблице миллионы записей, прирост производительности колоссальный.

  Стоит отметить, что на синтаксис запросов индексы никак не влияют. Разработчики точно также пишут запросы к таблицам базы, а оптимизатор SQL Server сам ищет нужные индексы, среди созданных, и пытается с помощью них оптимизировать запрос.

  Однако есть у индексов и свои подводные камни. Они позволяют оптимизировать и существенно ускорить операции выборки данных, но при этом отрицательно сказываются на производительности запросов по вставке и удалению данных. Так как любые изменения в данных главной таблицы должны автоматически применяться для всех ее индексов. Если для таблицы создано четыре индекса, то простая операция вставки новой строки будет выполняться пять раз. Один раз – вставка в главную таблицу и по одному разу для каждого индекса. Таким образом, время выполнения запроса INSERT увеличится в четыре раза. Аналогично обстоит дело с операцией DELETE.

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

  Столбы в индексе могут быть двух типов:

  1) Столбцы в ключе индекса

  2) Столбы, включенные в индекс

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

  В зависимости от общего числа столбцов в индексе, индексы делятся на кластеризованные и некластеризованные индексы.

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

  Некластеризованный индекс – это индекс, в который включена только часть столбцов базовой таблицы. Пример такого индекса приведен выше.

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