Перенос приложений Btrieve на
Microsoft SQL Server

Мэт Дото


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

Этот документ предназначен для использования только в информационных целях. MICROSOFT НЕ ДАЕТ ЗДЕСЬ НИКАКИХ ГАНРАНТИЙ, ЯВНЫХ ИЛИ ПОДРАЗУМЕВАЕМЫХ.

©1997 Microsoft Corporation. Все права соблюдены.

Microsoft, BackOffice, логотип BackOffice, Visual C++, Win32, Windows и Windows NT являются зарегистрированными товарными знаками или товарными знаками Microsoft Corporation в США или других странах.

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

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


Содержание

Процесс разработки и используемые платформы                                                                                                                                  

Примеры из базы данных об изданиях                                                                                                                                                      

Образец приложения и листинги программ                                                                                                                                             

Отправной пункт: приложение Btrieve                                                                                                                                                      

Этап 1: Представление Microsoft SQL Server под маской Btrieve                                                                                                      

Этап 2: Новое приложение, использующее ODBC и SQL для доступа к Microsoft SQL Server                                                 

Создание динамической библиотеки промежуточного интерфейса                                                                                                

Выявление функций, которые должны быть преобразованы                                                                                                             

Создание файла с определением функций библиотеки промежуточного интерфейса                                                                               

Эмуляция функций Btrieve в библиотеке промежуточного интерфейса                                                                                          

Доступ к библиотеке промежуточного интерфейса из основного приложения                                                                            

Трансляция вызовов Btrieve в вызовы ODBC и SQL с помощью библиотеки промежуточного интерфейса                        

Назначение дескриптора posBlock в Btrieve                                                                                                                                            

Установка связи с ODBC и инициализация доступа к данным                                                                                                          

Использование ODBC и SQL в библиотеке промежуточного интерфейса.                                                                                     

Обработка ошибок                                                                                                                                                                                          

Нормализованные базы данных                                                                                                                                                                 

Сравнение навигационной модели Btrieve и языка структурированных запросов SQL Server                                                

Навигационная модель Btrieve                                                                                                                                                                    

Transact-SQL                                                                                                                                                                                                      

Стандартный результирующий набор записей и курсоры                                                                                                                 

Стандартные результирующие наборы записей                                                                                                                                    

Курсоры SQL Server                                                                                                                                                                                        

Интерфейс доступа к данным                                                                                                                                                                       

Прямой доступ к таблицам с помощью Transact-SQL                                                                                                                           

Эффективная организация поиска данных                                                                                                                                                               

Эффективное использование оптимизатора запросов                                                                                                                          

Использование средств объединения на стороне сервера                                                                                                                   

Управление параллельным доступом и блокировкой                                                                                                                          

Блокировка и параллельный доступ в модели ISAM/Btrieve                                                                                                             

Блокировка и параллельный доступ в SQL Server                                                                                                                                 

Реализация баз данных и запросов с эффективной структурой                                                                                                         

Выбор специальных столбцов таблицы                                                                                                                                                   

Использование предложений WHERE для уменьшения размера результирующего набора                                                    

Использование одиночного SELECT                                                                                                                                                         

Использование методов эффективного индексирования                                                                                                                      

Выполнение хранимых процедур                                                                                                                                                                               

Сохранение транзакций короткими и эффективными                                                                                                                           

Обновление и удаление данных как операции, ориентированные на обработку наборов записей                                       

Использование триггеров, DRI и проекции                                                                                                                                              

Триггеры, DRI, бизнес-правила и целостность ссылок                                                                                                                         

Проекции                                                                                                                                                                                                            

Как сделать администрирование базы данных автоматическим                                                                                                      

Планирование событий                                                                                                                                                                                  

Выдача SQL Server предупреждений о событии с возможными неприятными последствиями                                                

Отсечение протокола в контрольных точках                                                                                                                                          

Мастер планирования сопровождения БД                                                                                                                                                               

Управление размером БД и журнала транзакций                                                                                                                                  

Дополнительная справочная литература                                                                                                                                                

Средства                                                                                                                                                                                                             

 


Введение

Многие производители программных приложений замечают, что жизненный цикл их продуктов приблизился к критической точке. Выпущенные ими ранее версии СУБД, подобных Btrieve, основывались на индексно-последовательном методе доступа (ISAM) и имели в свое время заслуженный успех. Однако сейчас, зная о растущих потребностях пользователей в СУБД с широкими возможностями, многие разработчики переносят свои приложения на другие платформы баз данных. Большинство из них переходят на использование реляционных систем управления базами данных (РСУБД). Приложения, основанные на РСУБД, обладают рядом преимуществ по сравнению с решениями, использующими метод ISAM: они демонстрируют более высокую производительность и гибкость, поскольку лучше масштабируются, с ними могут работать больше пользователей и они проектировались для управления БД, размер которых варьируется от пары мегабайт до нескольких сотен гигабайт.

Microsoft® SQL Serverä является одной из самых мощных и гибких РСУБД, доступных в настоящее время. Благодаря простоте использования и богатым возможностям, он является привлекательной платформой для разработчика приложений БД. В этом документе описывается стратегия перевода приложений Btrieve на Microsoft SQL Server, а также демонстрируются средства Microsoft SQL Server, которые делают его высокопроизводительной альтернативой любой ISAM-платформе. Несмотря на то, что в данном документе обсуждается в основном платформа Btrieve, многие из изложенных принципов применимы к любому ISAM-приложению.

Процесс разработки и используемые платформы

Для простоты предполагается, что средой разработки и платформой приложения являются соответственно Microsoft Visual C++® и Microsoft Windows NT® или Microsoft Windows® 95. Все упоминаемые в документе функции Btrieve и ссылки на DLL соответствуют этому допущению. Тем не менее, описываемая технология может быть применена и к другим компиляторам для создания приложений Microsoft Windows.

Примеры из базы данных об изданиях

Несколько таблиц из БД pubs Microsoft SQL Server используются как в образцах приложений, так и в листингах программ данного документа. Эти таблицы были выбраны, потому что они доступны любому пользователю SQL Server, а их структура и связь сущностей представлена в документации по SQL Server.


Образец приложения и листинги программ

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

·         Btrvapp.exe

Образец приложения Btrieve, которое обращается к файлам данных Btrieve: Sales.btr и Titlepub.btr — это отправной пункт преобразования. Оно представляет собой простую программу для ввода данных и подготовки отчетов по БД, содержащей информацию об издательских компаниях, изданиях и продажах каждого издания.

·         Mybtrv32.dll

Образец библиотеки промежуточного интерфейса – обертки (wrapper), которая транслирует программные вызовы Btrieve из Btrvapp.exe в вызовы ODBC и Microsoft SQL Server Transact-SQL. Обертка обеспечивает минимальное преобразование из структуры Btrieve ISAM в структуру Microsoft SQL Server клиент/сервер. Используя эту библиотеку, Btrvapp.exe получает доступ к ненормализованным таблицам из БД Microsoft SQL Server pubs вместо файлов данных Btrieve.

·         Odbcapp.exe

Образец приложения, которое выполняет те же функции, что и Btrvapp.exe в сочетании с Mybtrv32.dll, используя только ODBC и SQL, а не вызовы функций Btrieve.

·         Morepubs.sql

Скрипт-файл, создающий ненормализованные таблицы в БД pubs, на которые есть ссылки из интерфейсной DLL, а также хранимые процедуры, используемые Odbcapp.exe в иллюстративных целях.

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

Эта стратегия предлагает общие принципы, которые можно применить при преобразовании приложений, использующих средства Btrieve, в полноценные приложения, взаимодействующие с Microsoft SQL Server посредством ODBC и структурного языка запросов (SQL). Поэтапное преобразование приложения позволяет контролировать состояние этого процесса на каждой из его стадий. Преобразование происходит по следующим направлениям:

·         Создание динамической библиотеки промежуточного интерфейса

·         Преобразование приложения в ODBC и SQL

·         Эффективное использование ресурсов сервера

На иллюстрации представлено поэтапное изменение архитектуры приложения в процессе преобразования. Изображенные на иллюстрации компоненты будут подробно рассмотрены в последующих двух разделах данного документа.

Отправной пункт: приложение Btrieve

Отправной пункт преобразования—Btrvapp.exe—простая программа для ввода данных и подготовки отчетов, которая обрабатывает информацию об изданиях, издателях и продажах. Приложение осуществляет доступ к двум файлам данных Btrieve: Sales.btr и Titlepub.btr с помощью микроядра Btrieve. Файл Sales.btr содержит информацию о продажах каждой книги, а файл Titlepub.btr – о каждом издании и его издателе. Каждый из этих двух файлов имеет два ключа, соответствующих издателю и идентификатору издания Title ID. Приложение использует эти ключи для позиционирования в файлах при выполнении операций поиска. Программа использует технику построчного поиска ISAM и обрабатывает его результаты. В Btrvapp.exe применяется механизм параллельных транзакций Btrieve, управляющий блокировкой данных во время модификации, добавления или удаления информации. Демонстрационное приложение может выполнять следующие операции:

·         Поиск отдельного издания по его идентификатору TitleID. Результат поиска содержит полную информацию о названии, издателе, текущих продажах и объеме продаж с начала года.

·         Добавление нового издания и информации о его издателе.

·         Добавление сведений о продажах издания.

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

Этап 1: Представление Microsoft SQL Server под маской Btrieve

Цель этого этапа преобразования – создать промежуточный уровень абстракции между приложением и Microsoft SQL Server. Использование концепции DLL-обертки позволяет приложению Btrvapp.exe обращаться к данным SQL Server без внесения изменений в его код. Фактически, благодаря промежуточному интерфейсу, SQL Server выглядит для Btrvapp.exe как Btrieve. Интерфейсная библиотека, обращаясь к ненормализованным таблицам bsales и titlepublisher, организует обработку информации и возвращает результат в формате Btrieve. Эти таблицы имеют структуру, в точности соответствующую содержанию файлов Sales.btr и Titlepub.btr, с которыми работает Btrvapp.exe. Хотя техника использования ODBC и SQL в обертке DLL не оптимальна, она эмулирует исходный метод доступа, аналогичный Btrieve.

Этап 2: Новое приложение, использующее ODBC и SQL для доступа к Microsoft SQL Server

Odbcapp.exe – полноценное приложение, использующее ODBC и SQL, которое обращается непосредственно к данным SQL Server. Это более эффективный способ организации взаимодействия с SQL Server, чем реализованный с помощью обертки. Приложение манипулирует с данными трех нормализованных таблиц: titles, publishers и sales, используя все преимущества реляционной модели, предоставляемые SQL Server. Odbcapp.exe также использует некоторые средства повышения производительности SQL Server, такие как индексы, стандартные результирующие наборы записей SQL и хранимые процедуры обработки результирующих наборов.

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

Например, в случае рассматриваемой стратегии преобразования вы можете перехватить вызовы Btrieve, которые производятся из приложения, и подменить их вызовом средств ODBC для доступа к Microsoft SQL Server вместо использования самой Btrieve. Такая техника позволяет оставить основное приложение в неизменном виде и сохранить инвестиции, сделанные ранее в его разработку или приобретение, одновременно расширив его возможности за счет доступа к данным SQL Server.

Другой возможный путь – создание динамической библиотеки, которая получает данные от SQL Server и размещает их в буфере на клиентском или любом другом компьютере. При такой стратегии приложение самостоятельно извлекает данные из буфера, применяя индексно-последовательный доступ. Хотя этот подход позволяет основному приложению работать с SQL Server, он сложен и его реализация может быть весьма затруднительна. Он лучше всего подходит в тех ситуациях, когда вы не хотите использовать операции с наборами или при разработке полноценных ODBC- и SQL-приложений. Эта методика не рассматривается в контексте стратегии преобразования, представленной в данном документе.

Создание динамической библиотеки промежуточного интерфейса

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

Выявление функций, которые должны быть преобразованы

Библиотека-обертка должна реализовывать те функции из библиотеки Btrieve Wbtrv32.dll, которые используются в основном приложении. Вы можете определить, какие именно функции импортированы с помощью утилиты выдачи дампа двоичного файла. Она генерирует список функций, импортированных из различных внешних библиотек, на которые есть ссылки в приложении. В состав Microsoft Visual C++ входит соответствующая программа под названием Dumpbin.exe.

В качестве примера ниже приведены фрагменты листингов утилиты DUMPBIN, запущенной с параметром /IMPORTS для выявления функций импортированных из Wbtrv32.dll, а также с параметром /EXPORTS для выявления функций экспортируемых Wbtrv32.dll.


DUMPBIN /IMPORTS BTRVAPP.EXE
Microsoft ® COFF Binary File Dumper Version 4.20.6164
Copyright © Microsoft Corp 1992-1997. All rights reserved.

Dump of file BTRVAPP.EXE
File Type: EXECUTABLE IMAGE
         Section contains the following Imports:
            wbtrv32.dll
               Ordinal     3
               Ordinal     2
               Ordinal     1

DUMPBIN /EXPORTS WBTRV32.DLL
Microsoft ® COFF Binary File Dumper Version 4.20.6164
Copyright © Microsoft Corp 1992-1997. All rights reserved.

Dump of file wbtrv32.dll
File Type: DLL
         Section contains the following Exports for wbtrv32.dll
                   0 characteristics
            31D30571 time date stamp Thu Jun 27 15:04:33 1996
                0.00 version
                   1 ordinal base
                  10 number of functions
                  10 number of names

            ordinal hint   name

                  1    0   BTRCALL  (000014EC)
                  8    1   BTRCALLBACK  (00003799)fs
                  2    2   BTRCALLID  (00001561)
                  9    3   DBUGetInfo  (00008600)
                 10    4   DBUSetInfo  (000089E8)
                  3    5   WBRQSHELLINIT  (00002090)
                  4    6   WBSHELLINIT  (00002A6A)
                  7    7   WBTRVIDSTOP  (00001812)
                  5    8   WBTRVINIT  (00002A4F)
                  6    9   WBTRVSTOP  (000017D2)

 

Информация, представленная в этих фрагментах, используется при создании файла определений (definition file) для библиотеки-обертки. Это позволит вам ограничится реализацией в обертке только тех функций из Wbtrv32.dll, которые действительно используются основным приложением. Таким образом, вы избавитесь от необходимости программирования никогда не используемых основным приложением функций.

Создание файла с определением функций библиотеки промежуточного интерфейса

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

1.       Выполнить команду DUMPBIN /IMPORTS application_file_name для получения списка символов, импортированных из Wbtrv32.dll. На примере приведенного выше фрагмента листинга функции, импортированные приложением Btrvapp.exe из Wbtrv32.dll, имеют порядковые номера 3, 2, и 1.

2.       Выполнить команду DUMPBIN /EXPORTS DLL_file_name для получения списка имен, экспортируемых рассматриваемой динамической библиотекой. Они перечислены в таблице с заголовками “ordinal,” “hint” и “name” в колонке “name”. В нашем примере это имена BTRCALL, BTRCALLID и WBRQSHELLINIT.

3.       Создать DEF-файл, содержащий секцию EXPORTS с именами функций из колонки “name” листинга утилиты DUMPBIN.

Конкретное сочетание импортированных/экспортируемых имен зависит от того, какие функции Btrieve используются основным приложением.

Эмуляция функций Btrieve в библиотеке промежуточного интерфейса

Следующим шагом является разработка общей структуры обертки таким образом, чтобы все операции Btrieve были реализованы корректно. Большинство операций Btrieve будет выполняться с помощью вызовов BTRCALL и BTRCALLID. Соответствующие им функции в библиотеке промежуточного интерфейса должны выполнять операции, используемые основным приложением. Каждая из этих функций будет получать все необходимые для выполнения операции данные посредством передаваемых ей входных параметров.

Наиболее важными параметрами являются posBlock, operation, dataBuffer, keyBuffer и ckeynum. Параметр posBlock обсуждается далее в разделе “Назначение дескриптора posBlock в Btrieve”. Параметр operation указывает, какая именно операция должна быть выполнена. Смысл остальных параметров зависит от выполняемой операции. Вы должны использовать эти параметры тем же образом, как это происходит при их обработке Btrieve. Следующий фрагмент демонстрирует, как операция B_GET_EQUAL обрабатывается функцией BTRCALL из библиотеки Mybtrv32.dll:

DllExport int __stdcall BTRCALL (BTI_WORD operation, BTI_VOID_PTR posBlock,
        BTI_VOID_PTR dataBuffer, BTI_ULONG_PTR dataLen32,
        BTI_VOID_PTR keyBuffer, BTI_BYTE keyLength, BTI_CHAR ckeynum)
{
SQLRETURN rc;    // Код возврата операции Btrieve

/*Выполнение операции на основании значения operation, переданного вызывающим приложением */
switch(operation){ 

case B_GET_EQUAL:   
// Получить первую запись Title-Publisher, удовлетворяющую условиям поиска
if (!strcmp(posBlock, “titlepub.btr”)){//Осуществляется доступ к Title-Publisher ?
rc = GetTitlePublisher(henv1, hdbc1, hstmt, B_GET_EQUAL, ckeynum,keyBuffer);
if (rc != B_NO_ERROR)
              return rc;

//Копировать данные об издании и издателе в структуру tpRec
memcpy(dataBuffer, &tpRec, sizeof(tpRec));
}
else { // Получить информацию о продажах
rc=GetSales(henv1, hdbc2, hstmt2, B_GET_EQUAL, keyBuffer);
       if (rc != B_NO_ERROR)
return rc;

//Копировать данные о продажах в структуру salesRec
memcpy(dataBuffer, &salesRec, sizeof(salesRec));
}     
break;

В вышеприведенном фрагменте определяется целевая таблица SQL Server. Она устанавливается в результате проверки значения параметра posBlock в соответствии со стратегией, описанной в общих чертах в разделе “Трансляция вызовов Btrieve в вызовы ODBC и SQL, осуществляемая библиотекой промежуточного интерфейса”. После того, как таблица определена, вызывается функция для поиска и извлечения первой записи, удовлетворяющей значению содержимого параметров keyBuffer и ckeynum, на которую установлен соответствующий курсор SQL Server. Этот метод используется в библиотеке промежуточного интерфейса повсюду. Следующая иллюстрация демонстрирует концепцию ее работы.

На приведенной иллюстрации основное приложение—Btrvapp.exe запрашивает информацию об издании и издателе для идентификатора TitleID BU1032. В процессе обработки запроса приложение вызывает функцию Btrieve BTRCALL, чтобы получить следующую запись из файла Titlepub.btr. Библиотека промежуточного интерфейса предназначена для эмуляции выполнения этой функции, в то время как на самом деле работа происходит с данными SQL Server. После проверки параметра opcode выполняются необходимые для удовлетворения запроса операции, использующие ODBC и SQL. Из библиотеки промежуточного интерфейса вызываются функции ODBC и SQL для поиска и извлечения записи с TitleID BU1032 из таблицы titlepub в БД. После того, как данные получены, они возвращаются основному приложению через структуру, размещенную в буфере, который был передан при исходном вызове BTRCALL.

Доступ к библиотеке промежуточного интерфейса из основного приложения

После того, как библиотека промежуточного интерфейса создана, необходимо сделать так, чтобы основное приложение обращалось к функциям этой библиотеки, а не Btrieve. Это очень просто. Скомпонуйте (link) приложение с библиотекой промежуточного интерфейса и библиотеками (LIB) ODBC, заместив тем самым библиотеку Btrieve. Перекомпиляция основной программы при этом не требуется. После завершения процесса компоновки основное приложение станет взаимодействовать с SQL Server, а не с микроядром Btrieve.


Трансляция вызовов Btrieve в вызовы ODBC и SQL с помощью библиотеки промежуточного интерфейса

Основное приложение Btrvapp.exe может теперь использовать промежуточный интерфейс для доступа к данным Microsoft SQL Server. Благодаря этому промежуточному интерфейсу, SQL Server выглядит для Btrvapp.exe как Btrieve. Следующая тема, которую мы рассмотрим – как используется ODBC и SQL для доступа к данным SQL Server в рамках библиотеки обертки. Промежуточный интерфейс позволяет употреблять для доступа к SQL Server приемы работы с данными, свойственные ISAM и привычные для разработчиков Btrieve-приложений. Предлагаемая реализация дает возможность работать с данными SQL Server без внесения изменений в основное приложение. Однако, как будет показано далее, такой путь не является оптимальным.

Назначение дескриптора posBlock в Btrieve

Второй параметр, передаваемый приложением при вызовах BTRCALL и BTRCALLID—posBlock. В среде Btrieve он представляет собой уникальную область памяти, которая создается для каждого открытого файла и содержит информацию о логическом позиционировании для доступа к записям. Библиотечные функции Btrieve инициализируют эту область памяти и используют ее при выполнении операций доступа к данным. Прикладной программе ничего не известно о содержимом posBlock. Она просто передает указатель posBlock, соответствующий файлу, к которому производится обращение при каждом вызове Btrieve.

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

В рассматриваемом примере Btrvapp.exe обращается к двум файлам Btrieve: Sales.btr и Titlepub.btr, причем Sales.btr содержит информацию о продажах каждого издания, а Titlepub.btr – об их названиях и издателях. Этим файлам соответствуют таблицы bsales и titlepublishers, которые создаются в базе данных pubs с помощью скрипт-файла Morepubs.sql, который прилагается вместе с другими примерами к данному документу. В приложении Btrvapp.exe для открытия требуемых файлов и создания соответствующих posBlock вызывается функция B_OPEN.

В библиотеке промежуточного интерфейса этот posBlock отныне будет идентифицировать отдельную таблицу по ее имени. Промежуточный интерфейс может сохранять уникальный идентификатор, однозначно определяющий данные SQL Server, к которым осуществляется доступ в любой из возможных форм. Имена таблиц используются в данном примере для наглядности. При вызове функции B_OPEN параметр keybuffer содержит имя файла, который должен быть открыт. Функция B_OPEN, реализованная в библиотеке промежуточного интерфейса, записывает это имя файла или таблицы в posBlock. Следующий фрагмент исходного текста функции B_OPEN, реализованной в библиотеке промежуточного интерфейса (см. исходный текст в файле Mybtrv32.c для подробного ознакомления), демонстрирует сказанное:

/*Step1:*/
if (strlen((BTI_CHAR *) keyBuffer) <= MAX_POSBLOCK_LEN)
memcpy((BTI_CHAR *) posBlock, (BTI_CHAR *) keyBuffer,  keyLength);
else  
memcpy((BTI_CHAR ) posBlock, (BTI_CHAR ) keyBuffer,
MAX_POSBLOCK_LEN -1);

В нашем примере posBlock для файла Sales.btr содержит значение “Sales.btr”, а для файла Titlepub.btr—“Titlepub.btr”. Btrvapp.exe всегда будет идентифицировать таблицу Microsoft SQL Server по имени файла, содержащемуся в posBlock.

И в основном приложении, и в библиотеке промежуточного интерфейса будет использоваться структура записей данных одинакового формата. Это позволит промежуточному интерфейсу передавать записи между SQL Server и приложением Btrvapp.exe в том же самом виде, как если бы они принимались из Btrieve. Структуры записей данных, используемые в Btrvapp.exe и Mybtrv32.dll, представлены в следующем примере. Для более подробного ознакомления см. исходные тексты в файлах Btrvapp.c и Mybtrv32.c.

/************************************************************
       Data Record Structure Type Definitions
************************************************************/
//
структура записи titlepub record structure
//titlepub record structure
struct{
       char   TitleID[7];   //string строка
       char   Title[81];    //string строка
       char   Type[13];     //string строка
       char   PubID[5];     //string строка
       float  Price;        //money деньги
       float  Advance;      //money деньги
       int    Royalty;      //integer целое число
       int    YTD_Sales;    //integer целое число
       char   PubName[41];  //string строка
       char   City[21];     //string строка
       char   State[3];     //string строка
       char   Country[31];  //string строка
}tpRec;

//структура записи sales
//sales record structure
struct
{
       char   StorID[5];    //string строка
       char   TitleID[7];   //string строка
       char   OrdNum[21];   //string строка
       int    Qty;          //integer целое число
       char   PayTerms[13]; //string строка
}salesRec;

Установка связи с ODBC и инициализация доступа к данным

В реализованной в качестве примера библиотеке промежуточного интерфейса функция B_OPEN устанавливает соединение с SQL Server для каждой таблицы, с которой работает основное приложение Btrvapp.exe. Функция также создает и открывает курсоры, используемые для доступа к таблицам данных на SQL Server. Курсоры открываются для всей таблицы безо всяких ограничений, накладываемых предложением WHERE для сокращения количества возвращаемых строк. Эти соединения и курсоры используются всеми остальными функциями Mybtrv32.dll при выполнении операций с таблицами SQL Server. Чтобы сократить время обработки и накладные расходы, связанные с установлением и разрывом соединений с сервером, соединения не завершаются вплоть до окончания работы приложения.

Реализация с использованием соединений и курсоров, описанная в предыдущем абзаце, выбрана по двум причинам.. Во-первых, потому что она имитирует способ, которым пользуются обычные приложения Btrieve для доступа к файлам, когда каждому открытому приложением файлу соответствует один дескриптор posBlock. Вторая причина заключается в намерении продемонстрировать неэффективность управления соединениями при доступе к SQL Server. В контексте данной реализации промежуточного интерфейса достаточно одного соединения, т.к. в его рамках может быть открыто и одновременно доступно множество курсоров сервера. Таким образом, второе соединение лишь увеличивает накладные расходы при выполнении приложения. Методика управления соединениями была бы более эффективной при использовании единственного соединения и нескольких курсоров, открытых в его рамках.

Использование ODBC и SQL в библиотеке промежуточного интерфейса.

Существует множество различных способов использования ODBC и SQL для доступа к данным SQL Server. Библиотека промежуточного интерфейса Mybtrv32.dll использует курсоры на стороне сервера. Такая реализация выбрана по нескольким причинам:

·         Доступ к данным осуществляется аналогично тому, как это происходит при доступе к файлам Btrieve (например, при помощи операторов Fetch/Get First/Next).

·         Для демонстрации применения построчной обработки и позиционного обновления, свойственных ISAM, применительно к данным SQL Server.

·         Для демонстрации различия между построчной обработкой и использованием стандартного результирующего набора записей в образце приложения Odbcapp.exe.

Каждая Btrieve-операция, выполняемая основным приложением, преобразуется промежуточным интерфейсом в эквивалентную операцию с использованием ODBC и SQL. Некоторые из этих операций, например, B_SET_DIR неприменимы в среде SQL Server и поэтому не приводят к выполнению каких-либо действий со стороны промежуточного интерфейса. Оптимальная стратегия использования ODBC и SQL как в промежуточном интерфейсе, так и в окончательном варианте преобразованного приложения обсуждается в разделе “Преобразование приложения для использования ODBC и SQL”.

Обработка ошибок

Библиотека промежуточного интерфейса при выходе из каждой функции должна формировать коды возврата Btrieve. Каждая из функций должна возвращать B_NO_ERROR или код ошибки Btrieve, соответствующий произошедшей ошибке. Коды возврата должны в точности соответствовать употребляемым в Btrieve, чтобы основное приложение не “заметило”, что библиотечные функции используют доступ к SQL Server вместо Btrieve. Чтобы библиотека промежуточного интерфейса функционировала надлежащим образом, вы должны формировать коды возврата Btrieve, которых ожидает основное приложение.

Однако, не существует прямого однозначного соответствия между кодами ошибок Microsoft SQL Server и Btrieve. Вы должны “переводить” все ошибки SQL Server, которые могут возникнуть при выполнении функций из библиотеки промежуточного интерфейса, на язык кодов возврата Btrieve. Следующий пример, заимствованный из исходного текста функции MakeConn в файле Mybtrv32.c, демонстрирует эту концепцию.

// Резервирование дескриптора соединения, установка 5-секундной паузы ожидания начала сессии и установка соединения с SQL Server
       rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, hdbc);

// Установка паузы ожидания начала сессии
       if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
              rc=SQLSetConnectAttr(*hdbc, SQL_LOGIN_TIMEOUT,(SQLPOINTER)5, 0);
       else{
       // Возникла ошибка: информировать пользователя и закончить выполнение функции
              ErrorDump(“SQLAllocHandle HDBC”, SQL_NULL_HENV, *hdbc,
SQL_NULL_HSTMT);
return B_UNRECOVERABLE_ERROR;
}

Вышеприведенный фрагмент программы резервирует дескриптор соединения для дальнейшего использования внутри библиотеки промежуточного интерфейса. При возникновении ошибки она должна быть “переведена” на “язык” кодов ошибок Btrieve. Поскольку в Btrieve не существует понятия дескриптора соединения, не существует и соответствующей ошибки. Поэтому следует выбрать код ошибки Btrieve, наиболее точно отражающий серьезность или приблизительный смысл произошедшего. Поскольку дескриптор является критическим ресурсом, в примере используется код возврата Btrieve B_UNRECOVERABLE_ERROR (непоправимая ошибка). Вы можете использовать любой из кодов возврата Btrieve, обработка которых предусмотрена в основном приложении.

Для разработки высокопроизводительного приложения Microsoft SQL Server важно понимать некоторые основные отличия между реляционной моделью, используемой в SQL Server, и методом доступа ISAM, используемым в Btrieve. Библиотека промежуточного интерфейса, обсуждаемая в первой части документа, успешно представляет SQL Server “под маской” Btrieve. Однако, подобная реализация неэффективна с точки зрения организации доступа к данным SQL Server. В действительности реализация с помощью библиотеки промежуточного интерфейса скорее всего будет иметь гораздо худшую производительность, чем оригинальное приложение Btrieve.

Библиотека промежуточного интерфейса осуществляет доступ к двум ненормализованным таблицам вместо того, чтобы использовать преимущества реляционной модели и возможности, предоставляемые SQL Server. В ней также не используется никаких расширенных средств повышения производительности, таких как языковые средства SQL, хранимые процедуры, индексы и триггеры для обработки результирующего набора записей. Следующим шагом является смещение акцента с техники обработки данных, используемой основным приложением, на получение всех преимуществ от применения реляционной модели SQL Server. Этого можно достичь, создав новое приложение Odbcapp.exe, которое использует для доступа к данным SQL Server только средства ODBC и SQL.

Наибольшего выигрыша в производительности SQL Server можно достичь при оптимальном проектировании базы данных, индексов и структуры запросов. Недостаточная проработанность этих аспектов становится причиной удручающе низкой производительности. Поскольку производительность имеет первостепенное значение, разработчики должны сконцентрировать свои усилия в первую очередь на решении этих вопросов. Существенное увеличение производительности может быть получено при сравнительно небольших затратах времени. В этой части документа мы сосредоточимся на выработке эффективных решений при проектировании базы данных, индексов и структуры запросов с использованием средств ODBC и SQL. Мы будем постоянно проводить сравнения между способами обработки данных в Btrieve и SQL Server, чтобы продемонстрировать преимущества последнего.

Нормализованные базы данных

Стройная логика структуры базы данных является залогом высокой производительности SQL Server. Нормализованная база данных характеризуется наличием множества таблиц с малым числом столбцов. Использование нормализованной базы данных помогает повысить производительность за счет сокращения избыточности данных и количества индексов. Например, формат записей, используемый приложением Btrvapp.exe и библиотекой Mybtrv32.dll для данных в файле Titlepub.btr и таблице titlepublisher, приводит к избыточности. В частности, каждому изданию сопутствует полная информация об издателе. Таким образом, если издатель выпускает более одного издания, информация о нем будет повторяться в каждой записи о его изданиях. Такие системы, как Btrieve (основанные на использовании ISAM), не поддерживают возможностей слияния или группировки данных из нескольких файлов. Это приводит к необходимости добавлять избыточную информацию об издателе в таблицу Titles, чтобы не производить в приложении слияния информации из двух файлов.

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


Сравнение навигационной модели Btrieve и языка структурированных запросов SQL Server

Вам следует выбрать реализуемую методику поиска, модификации, добавления и удаления данных на основании того, как они используются в приложении. Microsoft SQL Server—это мощная и гибкая реляционная СУБД. Хотя многие из приемов обработки данных, присущих Btrieve, применимы и в среде SQL Server, вам следует избегать обработки результирующего набора записей на стороне клиента, что типично для Btrieve ISAM, и воспользоваться всеми преимуществами, предоставляемыми возможностью их обработки на SQL Server. Далее будет проведено сравнение моделей обработки результирующих наборов записей, используемых в Btrieve и SQL Server. Целью этого сравнения является выявление различий, а также демонстрация того, насколько эффективно вы можете реализовать эти функции в среде SQL Server.

Навигационная модель Btrieve

Btrieve обрабатывает результирующий набор записей, используя навигационную модель. При этом одновременно осуществляется доступ к данным только одного файла. Все операции, в которые вовлечены данные из разных файлов, должны производиться прикладной программой самостоятельно. При поиске в файле приложению обычно возвращаются все данные, содержащиеся в записи, даже если для удовлетворения запроса требуется значение лишь одного или двух полей. Навигационная модель основана на операциях, использующих позиционирование. Приложение и Btrieve “отслеживают” текущую позицию в файле, на которой основывается выполнение всех операций с данными этого файла. Все операции по изменению данных в Btrieve—позиционные и требуют от приложения предварительного поиска и блокировки записи, подвергающейся изменению. Поскольку эти операции используют информацию о текущей позиции, приложение не имеет возможности запрашивать изменение или удаление записей в соответствии с определенными условиями.

В большинстве случаев при поиске вы будете использовать индексы. Вы должны владеть информацией обо всех полях, использующихся в качестве индексов в каждом из файлов, с которыми работает приложение (неиндексные поля можно отфильтровать с помощью вызова расширенной функции get). Моделью Btrieve предусматривается возможность простого поиска с использованием операторов сравнения =, <>, >, <, >= и <= для отбора записей, извлекаемых из файла. При этом, как правило, сравниваются значения одиночного индексированного поля и константы. В Btrieve также имеется расширенная форма вызова функции fetch, которая позволяет установить фильтр, сочетающий несколько условий поиска, объединенных операторами логического И/ИЛИ, однако его возможности ограничены.

Transact-SQL

Мощь Microsoft SQL Server заключается в его языке структурированных запросов Transact-SQL. Реализация Transact-SQL стройна и богата возможностями, что при грамотном использовании позволяет облегчить разработку приложений и сделать их более эффективными. Transact-SQL может работать как с нормализованными, так и с ненормализованными таблицами.. Он также позволяет передавать приложению не все столбцы, а только необходимые для удовлетворения его запроса. При использовании Transact-SQL возможности поиска не ограничиваются индексированными полями. Вы можете осуществлять поиск по значению любого поля в любой таблице, указанной в предложении FROM. На самом деле вам даже не требуется знать, какие индексы существуют в данной таблице. Сервер самостоятельно решает, какой из индексов следует использовать, исходя из заданных в инструкции Transact-SQL условий поиска. Например, приложение может сделать запрос “Выбрать значения TitleID, Title и Publisher для всех изданий, которых было продано больше 25000 экземпляров” (SELECT T.TitleId, T.Title, P.PubName from Titles T, Publishers P WHERE T.PubID = P.PubID and T.YTD-Sales > 25000). Приложение не обязано знать, является ли поле YTD_Sales индексированным, а клиентская сторона не должна проверять строки на соответствие условию.

Кроме простейших операторов сравнения, используемых в Btrieve, Transact-SQL предоставляет расширенные возможности поиска. Вы можете использовать сложные логические связки, групповые функции, такие как SUM, MAX и MIN, а также группировку и упорядочение данных. Одним из преимуществ Microsoft SQL Server является быстрый доступ к данным и их обработка на сервере, что сокращает нагрузку клиентской части и уменьшает количество операций обмена данными между клиентом и сервером.

Использование Transact-SQL дает возможность комбинировать данные из нескольких таблиц на сервере за одно обращение к нему, благодаря средствам объединения. Это продемонстрировано в следующем примере, заимствованном из исходного текста функции GetTitlePubRec приложения Odbcapp.exe. Функция осуществляет вызов хранимой процедуры, чтобы получить всю информацию об издании с заданным TitleID и его издателе.

/********************************************************************
Возвращает информацию об издании и издателе для заданного @titleID.
Запрос в этой хранимой процедуре осуществляет объединение данных из таблиц
Titles и Publishers на основе значения Publisher ID

********************************************************************/
CREATE PROCEDURE GetTPByTitleId @titleid char(6) AS
SELECT T.TITLE_ID, T.TITLE, T.TYPE, T.PUB_ID, T.PRICE, T.ADVANCE,
       T.ROYALTY, T.YTD_SALES, P.PUB_NAME, P.CITY, P.STATE,
       P.COUNTRY
FROM TITLES T, PUBLISHERS P
WHERE T.TITLE_ID = @titleid AND T.PUB_ID = P.PUB_ID

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

Возможности использования ресурсов сервера привлекают разработчиков приложений, которые все больше предпочитают программировать на Transact-SQL вместо того, чтобы использовать программный интерфейс, напоминающий ISAM, для автоматической генерации инструкций Transact-SQL. Программирование непосредственно на Transact-SQL предотвращает снижение производительности приложения и излишние накладные расходы, возникающие из-за незнания того, каким образом данные организованы в таблицах. Благодаря непосредственному доступу к таблицам, вы сохраняете полных контроль над тем, как данные обрабатываются в приложении. Вы можете оптимизировать каждый запрос для повышения производительности, увеличения пропускной способности и расширения возможностей одновременной работы с данными.

Стандартный результирующий набор записей и курсоры

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

Стандартные результирующие наборы записей

Если приложение использует SQL только для получения необходимых строк от сервера, наиболее эффективный способ достичь этого – использование стандартного результирующего набора записей. Приложение запрашивает его, не изменяя используемых по умолчанию параметров ODBC-инструкции SQLExecute или SQLExecDirect. [Прим. перев. Именно в силу данного обстоятельства в тексте для краткости используется понятие стандартный результирующий набор записей, которому в строгой терминологии ODBC соответствует статический набор записей с последовательным доступом.] Обрабатывая такой запрос, сервер возвращает записи так быстро, как это только возможно, не обеспечивая поддержки позиционирования внутри результирующего набора записей. Клиентская часть обязана немедленно извлечь полученные строки и разместить их в памяти приложения. Большинство операций по обработке данных в приложении может и должно выполняться с использованием стандартных результирующих наборов записей, что обеспечивает несколько явных преимуществ:

·         Требуется только одно обращение к серверу для получения всего набора записей.

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

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

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

Курсоры SQL Server

Как было отмечено выше, большинство операций по извлечению и модификации данных может быть произведено с помощью стандартных результирующих наборов записей. Однако, в некоторых случаях может потребоваться построчная обработка. Например, когда пользователь должен проверять записи одну за другой, чтобы принять решение или использовать данные из записи для выполнения другой задачи. SQL Server позволяет организовать такую обработку с помощью курсоров сервера. Аналогично Btrieve, курсоры сервера используются сервером для позиционирования. Они действуют подобно интерфейсу ISAM, за исключением того, что вы не можете осуществлять операции поиска внутри результирующих наборов. Использование курсоров подразумевает выполнение такого поиска в клиентской части приложения.

Курсоры сервера могут быть открыты как для всей таблицы, так и для любого подмножества ее строк, также как и стандартные результирующие наборы. [Прим. перев. В терминологии ODBC различным типам курсоров сервера соответствуют понятия динамического набора записей, статического набора записей и т.д.] Однако, в отличие от стандартного результирующего набора записей, они обладают следующими особенностями:

·         В зависимости от выбранного типа курсора (статический, ключевого набора, с последовательным доступом, динамический и т.д.) приложение может осуществлять различные операции извлечения записей (извлечь первую, следующую, предыдущую, по абсолютному или относительному положению и т.д.) в рамках результирующего набора на сервере. При использовании стандартного результирующего набора приложение может “продвигаться” только в прямом направлении.

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

·         Использование курсоров сервера позволяет осуществлять позиционное обновление данных. Стандартный результирующий набор не допускает применения подобной техники из-за своей ориентации на работу с множествами. Если приложению необходимо изменить или удалить строку, полученную с помощью стандартного результирующего набора записей, ему необходимо сформировать инструкцию SQL, содержащую предложения UPDATE или DELETE, а также предложение WHERE с исходным ключом (или вызвать хранимую процедуру, передав ей в качестве параметров первоначальный ключ и новые значения).

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

Очевидно, что курсоры сервера являются мощным и гибким средством SQL Server. Они могут применяться для схожей с ISAM построчной обработки, а также для позиционного обновления. Однако, это требует дополнительных расходов и накладывает некоторые ограничения:

·         Открытие курсоров требует больших затрат времени на стадиях синтаксического анализа, компиляции и обработки, а также ресурсов tempdb. Объем накладных расходов зависит от характера запроса, количества участвующих в нем таблиц (одна или несколько объединенных), а также выбранного типа курсора (статический, ключевого набора, с последовательным доступом, динамический и т.д.).

·         Курсоры сервера не могут быть открыты в SQL-пакете или хранимой процедуре, которые возвращают несколько результирующих наборов записей или используют условное выполнение операций.

·         Получение данных с помощью курсоров сервера требует по одному обращению клиента к серверу для каждой из операций:

·         Открытия курсора.

·         Указания количества извлекаемых приложением строк набора (в терминах ODBC – одно обращение для каждого вызова SQLFetchScroll или SQLExtendedFetch в ODBC 2.0).

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

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

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

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

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

Интерфейс доступа к данным

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

Прямой доступ к таблицам с помощью Transact-SQL

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

Зачастую разработанные сторонними фирмами элементы управления (controls) и объекты предоставляют свойства и методы для доступа к данным и их поиска. Основное назначение таких объектов и элементов управления – создание простого интерфейса, который позволил бы организовать доступ к данным, сведя к минимуму необходимость использования Transact-SQL. В то время, как эти средства облегчают и ускоряют процесс разработки, они вносят также дополнительный промежуточный уровень абстракции между данными и приложением. Это может поставить вас в затруднительное положение, поскольку вы не сможете в достаточной мере контролировать взаимодействие с сервером при осуществлении доступа к данным. При отсутствии полного контроля над тем, как осуществляется доступ к данным, возможность оптимальной настройки сильно ограничена.

Вы также лишаетесь возможности использовать одно из самых мощных средств SQL Server—язык Transact-SQL. Это очень мощный язык и вам следует использовать его в полном объеме. Хранимые процедуры могут применяться для инкапсуляции доступа, их использование способствует повышению вашего мастерства во владении SQL. Кроме того, хранимые процедуры дают возможность получить существенный выигрыш в производительности, поскольку сокращают затраты на синтаксический анализ, компиляцию и выполнение запросов. По возможности инструкции Transact-SQL желательно реализовывать в виде хранимых процедур, вызываемых из приложения.

Эффективная организация поиска данных

Не следует реализовывать операции поиска в клиентской части приложения. Вы можете соблазниться возможностью использования курсоров для реализации в вашем приложении метода обработки данных, подобного ISAM и применяющегося в среде Btrieve. Однако его применение для реализации операций поиска приводит к обесцениванию преимуществ использования мощных реляционных механизмов SQL Server. Имейте в виду, что использование ресурсов сервера значительно эффективнее при работе, как со стандартными результирующими наборами записей, так и с курсорами. Для сокращения непроизводительных накладных расходов в тех случаях, когда нет необходимости использовать курсоры сервера, должны использоваться стандартные результирующие наборы.


Эффективное использование оптимизатора запросов

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

Вы должны избегать выдачи предписаний оптимизатору для переопределения выбранных им индексов. Использование жестко закодированных индексов категорически не рекомендуется, поскольку снижает гибкость при проектировании базы данных и приложений. Если условия, при которых происходил выбор индексов, изменятся, приложение будет не в состоянии к ним приспособиться, что может неблагоприятно повлиять на его производительность. Хотя SQL Server имеет множество средств для принудительного воздействия на оптимизатор, они бывают нужны очень редко. В большинстве случаев оптимизатор наверняка выберет самое эффективное индексирование. Стратегия планирования индексов должна обеспечить хорошую выборку индексов для оптимизатора, чтобы он мог принять правильное решение. Это сокращает время анализа и обеспечивает хорошую производительность в большинстве случаев.

Использование средств объединения на стороне сервера

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

Управление параллельным доступом и блокировкой

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

Блокировка и параллельный доступ в модели ISAM/Btrieve

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

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

Независимо от того, какую схему вы выбрали, чтение, запись и связанные с ними блокировки в Btrieve не могут быть выполнены как единая операция. Прежде чем изменить или удалить запись, вы должны прочесть ее и заблокировать. Поэтому каждое изменение или удаление требует как минимум двух обращений к серверу. В следующем примере осуществляется поиск записи в файле Titlepub.btr по ее TitleID, при этом устанавливается блокировка одиночной строки в режиме ожидания записи (задается в Btrieve указанием смещения 100), а затем запись модифицируется:

/* Получить запись Издание/Издатель, используя операцию op и смещение 100 для установки блокировки*/
memset( &tpRec, 0, sizeof(tpRec) );
dataLen = sizeof(tpRec);
tpStat = BTRV( op+100, tpPB, &tpRec, &dataLen, keyBuf, keyNum );
if (tpStat != B_NO_ERROR)
       return tpStat;
.
.
.
// Обновить запись, указав значение ключа –1, поскольку он не будет изменен
tpStat = BTRV(B_UPDATE, tpPB, &tpRec, &dataLen, TitleID, -1 );
if (tpStat != B_NO_ERROR){
       printf( “\nBtrieve TitlePublishers UPDATE status = %d\n”,     tpStat );
       return B_UNRECOVERABLE_ERROR;
}

Поскольку для обработки запросов такого рода требуется многократное обращение к серверу, связанные с ним блокировки действуют достаточно долго. Это может ухудшить возможности параллельной работы и масштабируемость приложения. При использовании SQL Server количество обращений к серверу сокращается, поскольку все эти действия выполняются как одна элементарная операция. Установка и снятие блокировок производятся за один шаг с изменением или удалением данных, что снижает накладные расходы на управление блокировкой. SQL Server может также автоматически осуществлять оптимистическую блокировку параллельного доступа, используя для этой цели курсоры.

Блокировка и параллельный доступ в SQL Server

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

Microsoft SQL Server автоматически регулирует область действия блокировки в зависимости от характера поступившего запроса. При использовании SQL Server не требуется чтения и блокировки записи перед тем, как приложение сможет изменить или удалить ее. SQL Server осуществляет чтение и блокировку за один прием при выполнении инструкций UPDATE или DELETE. Условия отбора, указанные в предложении WHERE, позволяют SQL Server точно определить, какие данные будут затронуты и что, в конечном счете, следует заблокировать. В следующем примере с помощью хранимой процедуры и функций ODBC осуществляется точно такая же модификация записи с заданным TitleID, как и в предыдущем примере для Btrieve:

/****** ОбъектХранимая процедура dbo.UpdtTitlesByPubID   ******/
CREATE PROCEDURE UpdtTitlesByPubID @PubID char(4) AS
       UPDATE TITLES SET YTD_SALES = YTD_SALES +
       (SELECT SUM(QTY) FROM SALES WHERE TITLES.TITLE_ID =
       SALES.TITLE_ID)
       WHERE PUB_ID = @PubID
GO

// Связывание входного параметра хранимой процедуры PubID
rc = SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 4, 0,
choice, 5, &cbInval);
if  (rc!=SQL_SUCCESS && rc!=SQL_SUCCESS_WITH_INFO) {
       ErrorDump(“SQLBIND SELECT TITLEPUB 1”, SQL_NULL_HENV,
       SQL_NULL_HDBC, hstmtU);
       SQLFreeStmt(hstmtU, SQL_RESET_PARAMS);
       SQLFreeStmt(hstmtU, SQL_CLOSE);
       return FALSE;
}

// Выполнение UPDATE
rc=SQLExecDirect(hstmtU, “{call UpdtTitlesByPubID(?)}”, SQL_NTS);
if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)){
       ErrorDump(“SQLEXECUTE UPDATE TITLEPUB”, SQL_NULL_HENV,
       SQL_NULL_HDBC, hstmtU);
       return FALSE;
}

Поскольку блокировка осуществляется сервером, количество операций и общая сложность приложения снижаются. В отличие от большинства систем, базирующихся на ISAM, SQL Server имеет эффективные механизмы обнаружения тупиковых блокировок, которые помогают упростить логику приложений.

Если вы используете в приложении для SQL Server нереляционную модель ISAM, возможно, вам захочется использовать соответствующую технику управления блокировкой. Однако, это приведет к потере производительности, обеспечиваемой SQL Server. В приведенном далее списке описаны некоторые “подводные камни”, возникающие при реализации присущей ISAM стратегии блокировки. Краткое пояснение о том, как избежать возникновения каждой из проблем, следует после ее описания.

·         Попытка реализовать пессимистическую стратегию с помощью инструкции SELECT FROM T (UPDLOCK) или блокировки курсоров (используя опцию SQL_CONCURRENCY) для имитации модели ISAM.

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

·         Многократное повторное чтение и блокировка записи для проверки ее существования перед изменением или удалением.

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

В SQL Server предусмотрен механизм для предотвращения подобных издержек. Значение системной переменной @@ROWCOUNT показывает, какое количество строк было затронуто при выполнении последней операции. Таким образом, используя эту переменную после выдачи инструкции UPDATE или DELETE в приложении, вы можете проверить, сколько записей было модифицировано или удалено. Если в таблице не существует записей, удовлетворяющих условиям отбора, указанным в предложении WHERE – ничего не будет проделано и значение переменной @@ROWCOUNT будет установлено равным нулю. В следующем примере продемонстрировано, как следует использовать переменную @@ROWCOUNT в этих целях:

UPDATE PUBLISHERS SET PUB_NAME = ‘Microsoft Press’, City = ‘Redmond’, State= ‘WA’, Country = ‘USA’ WHERE = TITLE_ID = ‘BU1032’

/* Проверка, была ли запись действительно модифицирована */

IF @@ROWCOUNT <1
       /* Запись не существует, поэтому ее нужно создать, задав правильные значения полей */
       INSERT PUBLISHERS VALUES (‘BU1032’, ‘Microsoft Press’,
       ‘Redmond’, ‘WA’, ‘USA’) WHERE TITLE_ID = ‘BU1032’

В приведенном примере после выполнения инструкции UPDATE значение переменной @@ROWCOUNT соответствует количеству записей, которые были обновлены. Если ни одной записи не было модифицировано – вставляется новая запись.

Реализация баз данных и запросов с эффективной структурой

Структура приложения SQL Server особенно зависит от структуры БД и запросов — это фундамент, на котором строится все приложение. Успешное планирование и проектирование на этой стадии окажет заметное положительное влияние на работу вашего положения.

Выбор специальных столбцов таблицы

Создание приложений для SQL Server требует разработки иной стратегии, чем вы использовали бы в среде Btrieve, где большинство операций возвращают все столбцы данных или атрибуты для каждой обрабатываемой записи. Этот подход может быть не эффективен, особенно когда фактически используется лишь небольшое количество полей. Например, функция GetSales программы Btrvapp.exe вынуждена извлекать все атрибуты записи о продажах из Sales.btr, хотя для выполнения задачи нужны только значения TitleID и Qty. Ниже приведен фрагмент исходного текста функции GetSales из файла Mybtrv32.c, иллюстрирующий эту ситуацию:

/* Get TITLE/PUBLISHER with OPERATION*/
// Copy the desired TitleID to the keyBuffer for use by Btrieve and
// initialize parameters
strcpy(TitleID, keyBuf);  
memset( &salesRec, 0, sizeof(salesRec) );
dataLen = sizeof(salesRec);'

// Retrieve the sales record         
salesStat = BTRV( op, salesPB, &salesRec, &dataLen, keyBuf, 1 );
          if (salesStat != B_NO_ERROR)
                          return salesStat;

Такие же неэффективные принципы могут быть реализованы и в среде SQL Server при выборе всех столбцов из таблиц, проекций или курсоров, вовлеченных в операцию. Например, приведенный ниже фрагмент кода функции GetSales извлекает из BSALESCURSOR все атрибуты записи о продажах, хотя для выполнения задачи нужны только атрибуты TitleID и Qty:

// Связывание столбцов результирующего набора в буферах

SQLBindCol(hstmt, 1, SQL_C_CHAR, salesRec.StorID, 5, &cbStorID);
SQLBindCol(hstmt, 2, SQL_C_CHAR, salesRec.TitleID, 7, &cbTitleID);
SQLBindCol(hstmt, 3, SQL_C_CHAR, salesRec.OrdNum, 21,  &cbOrdNum);
SQLBindCol(hstmt, 4, SQL_C_SLONG, &salesRec.Qty, 0, &QtyInd);
SQLBindCol(hstmt, 5, SQL_C_CHAR, salesRec.PayTerms, 13, &cbPayTerms);

// Выборка записей с сервера по одной, пока не будет найдена заданная
while(!found)
{
memset(&salesRec, 0, sizeof(salesRec)); // Инициализация буфера для размещения записи

// Выборка записи из курсора сервера
rc = SQLFetchScroll(hstmt, FetchOrientation, FetchOffset);
if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))
              .
              .
              .

Такой неэффективной реализации можно избежать, обращаясь только к тем атрибутам записи о продажах, которые необходимы для решения конкретной задачи. Odbcapp.exe демонстрирует более эффективную концепцию в своей функции GetSales, которая вызывает из БД pubs SQL Server хранимую процедуру GetSales, чтобы получить только столбцы TitleID и Qty на запрос о продажах определенного издания. Приведенный ниже фрагмент исходного текста представляет хранимую процедуру GetSales и демонстрирует, как она выполняется и как результаты обрабатываются функцией GetSales приложения Odbcapp.exe.

/*Хранимая процедура Get Sales */
CREATE PROCEDURE GetSales @titleid char(6) AS
SELECT TITLE_ID, QTY FROM SALES WHERE TITLE_ID = @titleid
GO

// Выполнение хранимой процедуры и связывание буферов для каждого из столбцов результата
rc = SQLExecDirect(hstmtS, “{callGetSales(?)}”, SQL_NTS);
       .
       .
       .
       SQLBindCol(hstmtS,1, SQL_C_CHAR, TitleID, 7, &cbTitleID);
       SQLBindCol(hstmtS, 2, SQL_C_SLONG, & Qty, 0, &QtyInd);

// Извлечение результирующего набора с сервера до возникновения SQL_NO_DATA_FOUND
       while( rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
       {
              rc = SQLFetch(hstmtS);
.
.
.

Использование предложений WHERE для уменьшения размера результирующего набора

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

Представленный ниже пример заимствован из исходного текста функции CreateCursor в файле Mybtrv32.c:

// Создание BSALESCURSOR
if (!strcmp(curname, “BSALESCURSOR”))
       rc=SQLExecDirect(*hstmt2,
“SELECT STOR_ID, TITLE_ID, ORDNUM, QTY, PAYTERMS FROM
BSALES”,      SQL_NTS);

 

BSALESCURSOR создан без использования предложения WHERE. В результате сервер создает курсор, охватывающий все таблицу bsales. Поскольку он требует больше ресурсов и дополнительной обработки в клиентском приложении, выборка информации о продажах по определенному TitleID становится неэффективной. Было бы лучше использовать предложение WHERE, которое определяет точный TitleID или даже диапазон идентификаторов TitleID. Это уменьшит объем данных, пересылаемых для проверки на стороне клиента, и число обращений к серверу. Ниже приводится пример того же курсора с использованием предложения WHERE. Кроме того, этот курсор извлекает из таблицы bsales только столбцы TitleID и Qty, поскольку в них есть все данные, необходимые приложению.

if (!strcmp(curname, “BSALESCURSOR”)){
       SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
              SQL_CHAR, 6, 0, inval, 7, &cbInval);
       rc=SQLExecDirect(*hstmt2,
       “SELECT  TITLE_ID, QTY FROM BSALES WHERE TITLE_ID LIKE ?”,
       SQL_NTS);

 

Использование одиночного SELECT

Одиночный SELECT возвращает единственную строку, выбранную на основании указанного в предложении WHERE критерия. Одиночные SELECT часто используются в приложениях и заслуживают особого внимания. Поскольку возвращается только одна строка, вам следует для ее получения всегда использовать не курсор сервера, а стандартный результирующий набор оператора SELECT. Такая операция занимает меньше времени и нуждается в меньших ресурсах как сервера, так и клиента. Приведенный ниже фрагмент исходного текста — пример одиночного SELECT, возвращающего Pub_ID и Title для единственного Title_ID:

SELECT PUB_ID, TITLE FROM TITLES WHERE TITLE_ID = ‘PC8888’

Поскольку одиночные SELECT в большинстве случаев выполняются часто, следует использовать для их выполнения хранимые процедуры. Применение хранимой процедуры сокращает затраты на синтаксический анализ, компиляцию и выполнение, а также время, необходимое для обработки запроса по сравнению с обработкой оператора SELECT напрямую. В приведенном в качестве примера исходном тексте функции GetTitlePubRec программы Odbcapp.exe одиночный SELECT выполняется с помощью хранимой процедуры GetTPByTitleId. Обратите внимание на небольшой объем работы, который приходится проделать Odbcapp.exe для выполнения этой хранимой процедуры.

switch(key)
       {
       case 1:       //
Поиск по Title_ID
              strcpy(StoredProc, “{call GetTPByTitleID(?)}”);
              // определение хранимой процедуры, которая должна быть исполнена

              // связывание входных параметров
              SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT,
              SQL_C_CHAR,
              SQL_CHAR, 6, 0, inval, 7, &cbInval);
              break;
       .
       .
       .

// Выполнение хранимой процедуры и связывание полей с переменными
       memset( &tpRec, 0, sizeof(tpRec) );  // Инициализация буфера для размещения
                                         // структуры записи

       rc=SQLExecDirect(hstmtU, StoredProc, SQL_NTS );
       .
       .
       .

       SQLBindCol(hstmtU, 1, SQL_C_CHAR, tpRec.TitleID, 7,
       &cbTitleID);
       SQLBindCol(hstmtU, 2, SQL_C_CHAR, tpRec.Title, 81, &cbTitle);
       .
       .
       .
       SQLBindCol(hstmtU, 12, SQL_C_CHAR, tpRec.Country, 31,
       &cbCountry);

// Обработка результатов вплоть до возникновения SQL_NO_DATA_FOUND
       while (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO)
       {
              rc=SQLFetch(hstmtU);
              if (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO) {
       .
       .
       .

Использование методов эффективного индексирования

Эффективное индексирование — существенный фактор достижения хорошей производительности SQL Server, поэтому вам следует поэкспериментировать с различными индексами. В большинстве случаев оптимизатор выберет самое эффективное индексирование. Стратегия планирования индексов должна обеспечить хорошую выборку индексов для оптимизатора, чтобы он мог принять правильное решение. Это сокращает время анализа и обеспечивает хорошую производительность в большинстве ситуаций. Ниже приводятся основные рекомендации по планированию индексов:

·         Во всех SQL-запросах должно быть проанализировано предложение WHERE, поскольку именно его в первую очередь обрабатывает оптимизатор.

CREATE PROCEDURE GetTPByPubID @PubID char(4) ASSELECT T.TITLE_ID, T.TITLE,
T.TYPE, T.PUB_ID, T.PRICE, T.ADVANCE, T.ROYALTY, T.YTD_SALES, P.PUB_NAME, 
P.CITY, P.STATE,  P.COUNTRY FROM TITLES T, PUBLISHERS P
WHERE T.PUB_ID = P.PUB_ID AND T.PUB_ID = @PubID

GO

Каждый столбец или комбинация столбцов, перечисленные в предложении WHERE, являются возможным кандидатом на индекс. Эффективность индекса может быть проанализирована с помощью операторов SET SHOWPLAN ON и SET STATISTICS I/O ON. Вам следует создать индексов больше, чем только для первичного ключа в каждой таблице. Например, столбцы которые используются для управления поиском или для соединения с другой таблицей, часто бывают хорошими кандидатами на индексы. Быстродействие приведенной ниже хранимой процедуры GetTPByPubID, использующейся программой Odbcapp.exe, будет улучшено, если в качестве индекса выступит столбец PUB_ID:

·         Используйте “узкие” индексы.

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

·         Всегда используйте кластерные индексы, но с осторожностью.

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

·         Проверьте уникальность столбцов.

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

Более подробную информацию об индексировании и производительности см. в статье Q110352 “Оптимизация производительности Microsoft SQL Server” базы знаний Microsoft (Optimizing Microsoft SQL Server Performance), а о блокировке отдельной записи — во “Что нового в SQL Server 6.5” (What’s New in SQL Server 6.5) или в SQL Server Books Online.

 


Выполнение хранимых процедур

Хранимые процедуры увеличивают мощность, эффективность и гибкость Transact-SQL и могут иногда значительно увеличить производительность при обработке операторов и пакетов Transact-SQL. Хранимые процедуры отличаются от операторов Transact-SQL тем, что они прекомпилированы. При первом запуске процедуры механизм обработки запросов SQL Server создает план выполнения, а затем сохраняет процедуру в кэше процедур для будущего использования. Последующие вызовы хранимой процедуры будут выполняться практически мгновенно, поскольку большая часть обработки уже проделана. Приложение Odbcapp.exe демонстрирует, как вызываются хранимые процедуры с использованием ODBC внутри приложения.

Более подробную информацию о хранимых процедурах см. в Справочнике по Microsoft SQL Server Transact-SQL (Microsoft SQL Server Transact-SQL Reference) и в различных статьях базы знаний Microsoft (Microsoft Knowledge Base).

Сохранение транзакций короткими и эффективными

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

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

Обновление и удаление данных как операции, ориентированные на обработку наборов записей

Следует употреблять UPDATE и DELETE как операции над множествами, используя предложение WHERE для ограничения данных, участвующих в операции, в соответствии с определенным критерием. Выполнение указанных операций таким способом сокращает общий объем данных, задействованных при их выполнении, а также число блокировок, обеспечивающих монопольный доступ для обработки. Эти операции должны производиться только с помощью курсоров сервера, если условия выполнения UPDATE или DELETE не могут сформулированы в самом операторе SQL.

Следующие два примера показывают разницу между обновлением, ориентированным на обработку набора записей, и позиционным обновлением с использованием курсора. В обоих примерах происходит обновление YTD_Sales для каждого издания с определенным PubID. В первом примере хранимая процедура используется программой Odbcapp.exe. Здесь демонстрируется обновление стандартного результирующего набора с помощью предложения WHERE.

 

 

 

 /****** ОбъектХранимая процедура dbo.UpdtTitlesByPubID ******/
CREATE PROCEDURE UpdtTitlesByPubID @PubID char(4) AS
       UPDATE TITLES SET YTD_SALES = YTD_SALES + (SELECT SUM(QTY) FROM
       SALES WHERE TITLES.TITLE_ID = SALES.TITLE_ID)
WHERE PUB_ID = @PubID
GO

Предыдущий пример является эффективным, в нем для выполнения обработки используется сервер. Следующий ниже пример, взятый из Mybtrv32.exe — неэффективный. Он демонстрирует использование позиционного обновления с помощью курсора. В этом примере происходит извлечение с помощью курсора с обновлением каждой записи, PubID которой соответствует заданному. Обратите внимание на количество обращений к серверу для извлечения данных, возникающее при обработке этого запроса.

// Следующий фрагмент заимствован из исходного текста функции GetTitlePublisher в файле Mybtrv32.c
// Построчное извлечение из курсора, пока не будет обнаружена запись, которую нужно обновить
while (!found)
{
              memset( &tpRec, 0, sizeof(tpRec) );      // Инициализация буфера для размещения
                                                                                                                // строки

// Получение записи
       rc=SQLFetchScroll(hstmt8, FetchOrientation, FetchOffset);
              if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))
       {
                     if (rc!=SQL_NO_DATA_FOUND){
// Возникла ошибка до исчерпания курсора, известить пользователя и завершить работу
              ErrorDump(“SQLFetchScroll TitlePub”, SQL_NULL_HENV,
SQL_NULL_HDBC, hstmt8);
                           return B_UNRECOVERABLE_ERROR;
                     }
else {
                           return B_END_OF_FILE;} // Конец курсора достигнут
                                                                                                                // Запись не существует
}     

// Проверим, является ли эта запись той, которую нужно изменить
              if (!strcmp(keyBuffer, tpRec.PubID))
                     found=1;
}

// Запись, подлежащая обновлению, найдена. Следующий шаг – обновить ее.

// Следующий фрагмент заимствован из исходного текста функции CursorUPD в файле Mybtrv32.c
// Инициализация буфера для размещения записи

memset( &tpRec, 0, sizeof(tpRec) );
memcpy(&tpRec, dataBuffer, sizeof(tpRec));
// Инициализация структуры tpRec
memset( &tpRec, 0, sizeof(tpRec) );
memcpy(&tpRec, dataBuffer, sizeof(tpRec));

/*  Обновить текущую запись курсора. Мы производим повторное связывание столбцов, чтобы
/*  изменить ширину столбцов, которые содержат строки, завершающиеся NULL
/*  В качестве значения параметра numRows указывается 0, чтобы изменению были подвергнуты все строки набора
/*  Когда размер набора равен 1, будет обновлена только текущая строка
/*  Значение ключа текущей записи не будет модифицировано
/*  таким образом будет осуществлено позиционное обновление средствами SQLSet
/*  Pos(SQL_UPDATE, SQL_LOCK_NO_CHANGE)*/

       SQLBindCol(hstmtS, 1, SQL_C_CHAR, tpRec.TitleID, 7, &cbTitleID);
.
.
.
       rc=SQLSetPos(hstmtS, numRows, SQL_UPDATE,  SQL_LOCK_NO_CHANGE);
       if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))
              {
                     ErrorDump(“SQLSetPos SQL_UPDATE for TITLEPUBLISHER FAILED”, SQL_NULL_HENV, SQL_NULL_HDBC, hstmtS);
                     return B_UNRECOVERABLE_ERROR;
              }

       return B_NO_ERROR;


 

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

Использование триггеров, DRI и проекции

Три самые универсальных и мощных ресурса со стороны сервера — это триггеры, декларативная целостность ссылок (DRI) и проекции, которые часто применяются для уменьшения сложности клиентской части и прикладной программы. При грамотном использовании эти средства могут улучшить производительность вашего приложения.

Триггеры, DRI, бизнес-правила и целостность ссылок

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

Ссылочная целостность связана с ограничениями родительско-дочерних отношений между таблицами, где записи в порожденной таблице не могут существовать без соответствующей записи в порождающей таблице. Например, при использовании order item и order-line item сценария запись в таблице order-line не должна быть без соответствующей записи в таблице orders. SQL Server автоматически “навязывает” целостность ссылок на сервере, используя ограничения FOREIGN KEY, определенные в операторах DRI CREATE TABLE или ALTER TABLE. Поскольку в Btrieve такой возможности не предусмотрено, приложения Btrieve должны самостоятельно выполнять все действия по соблюдению целостности. Использование DRI избавляет клиентское приложение от такой обработки и обеспечивает некоторое повышение производительности. Триггеры и DRI также избавляют от необходимости модифицировать приложение в нескольких местах, если изменяется структура или связи между таблицами. Эти изменения производятся сервером в процессе обновления соответствующих триггеров и параметров DRI.

Проекции

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

Более подробную информацию о проекциях и триггерах см. в "В помощь администратору Microsoft SQL Server" (Microsoft SQL Server Administrator’s Companion) и других статьях из базы знаний Microsoft (Microsoft Knowledge Base).

Как сделать администрирование базы данных автоматическим

Среда Btrieve может показаться более легкой в использовании и администрировании, чем Microsoft SQL Server. Однако, за эту простоту приходится платить. У Btrieve есть несколько недостатков архитектуры, усложняющих работу приложений, которые ее используют.

·         Btrieve не ведет принудительного протоколирования всех изменений данных. Если обработка Btrieve операций INSERT, UPDATE или DELETE была прервана, есть вероятность, что она не будет успешно завершена. Это означает, что результаты транзакции будут утеряны, поэтому в приложении должна быть предусмотрена функция возврата.
В SQL Server не существует такой проблемы, потому что каждая БД имеет журнал упреждающей регистрации транзакции, который гарантирует, что все завершенные транзакции будут внесены в базу данных. В случае сбоя все завершенные в журнале транзакции будут записаны в БД, а незавершенные — нет.

·         В Btrieve нет резервирования открытых или использующихся файлов данных. При выполнении резервного копирования они должны быть закрыты. SQL Server обеспечивает возможность резервного копирования как БД, так и журнала транзакций в интерактивном режиме без необходимости прерывать работу сервера.

·         Btrieve не поддерживает механизмы автоматического управления выполнением таких задач, как резервное копирование файлов данных. Все задачи администрирования должны выполняться вручную или как часть приложения. С помощью SQL Enterprise Manager, входящего в состав Microsoft SQL Server многие задачи администрирования могут быть автоматизированы.

·         В Btrieve нет средств мониторинга производительности, которые могут быть очень полезны для анализа пропускной способности системы. Работа SQL Server может быть легко проконтролирована с помощью Windows NT Performance Monitor.

Хотя SQL Server — более сложная СУБД, чем Btrieve, зато она и гораздо более мощная, обладает многими возможностями и средствами повышения производительности, такими как ошибкоустойчивая обработка запросов, усовершенствованная блокировка и поддержка транзакций. SQL Server обеспечивает следующие свойства и средства, которые могут использоваться для упрощения администрирования системы:

·         Планирование событий

·         Выдача предупреждений о наступлении или приближении событии с возможными неприятными последствиями

·         Отсечение протокола в контрольных точках

·         Мастер планирования сопровождения БД

·         Настройка и изменение размеров БД и журнала транзакций

Планирование событий

Многие задачи администрирования могут быть автоматизированы с помощью средств планирования SQL Enterprise Manager и планировщика Windows NT. Наиболее подходящими для этого являются процессы, не нуждающиеся во вмешательстве человека (или для выполнения которых необходимо лишь незначительное вмешательство). Например, в большинстве сред могут выполняться автоматически следующие задачи: резервное копирование и восстановление журналов транзакций и баз данных, выполнение команд DBCC (средство проверки целостности БД), пакетные процедуры и т.д. Автоматизировав эти процессы, вы сократите время, необходимое для сопровождения системы, и облегчите работу с приложением.

 


Выдача SQL Server предупреждений о событии с возможными неприятными последствиями

Одним из наиболее мощных средств SQL Server является его возможность генерировать сигналы тревоги Windows NT Performance Monitor для уведомления администратора о наступлении критического или необычного события в системе. Система посылает администратору сообщения по электронной почте или на пейджер, либо запускает программу или пакетный файл, что позволяет администратору немедленно отреагировать на событие и предпринять необходимые действия. Сигналы тревоги также могут быть использованы для автоматизации сопровождения БД. Например, их можно настроить на создание копии журнала транзакций при его 80-процентном наполнении.

Более подробную информацию о Performance Monitor и сигналах тревоги см. в "Руководстве по Microsoft Windows NT 3.5 Server" (Microsoft Windows NT 3.5 Server System Guide), а о счетчиках Performance Monitor, связанных с SQL Server — “В помощь администратору Microsoft SQL Server” (Microsoft SQL Server Administrator’s Companion).

Отсечение протокола в контрольных точках

SQL Server ведет журнал упреждающей регистрации транзакций для каждой БД, обеспечивая таким образом возможность восстановления транзакций и других изменений, внесенных в данные, в случае возникновения проблемы. По умолчанию журнал пополняется до того момента, когда его копируют на ленту или другой диск, что позволяет производить пошаговое резервное копирование и восстановление в любой момент. Однако, для этого необходимо, чтобы копирование проводилось регулярно (автоматически или администратором) до того, как журнал транзакций переполнится. В соответствии с тем, хочет ли разработчик приложения использовать эту возможность SQL Server или нет, он может установить параметр trunc. log on chkpt. Тогда журнал транзакций будет периодически очищаться и необходимость в его резервном копировании, а также проблемы с управлением пространством для протоколирования исчезнут.

За более подробной информацией о журналах транзакций и параметре trunc. log on chkpt. обращайтесь к руководству "В помощь администратору Microsoft SQL Server" (Microsoft SQL Server Administrator’s Companion).

Мастер планирования сопровождения БД

В состав SQL Server входит программа-мастер для планирования мероприятий по сопровождению (Maintenance Plan Wizard) БД, который может быть использован для формирования расписания ежедневных или еженедельных рутинных операций по сопровождению базы данных, включая резервное копирование базы данных и журнала транзакций, проверку целостности базы данных, перестройку индексов и обновление статистической информации. Мастер (Maintenance Wizard) может быть запущен из SQL Server Enterprise Manager или утилитой Sqlmaint.exe, поставляемой вместе с Microsoft SQL Server.

Более подробную информацию о Maintenance Plan Wizard см. в "В помощь администратору Microsoft SQL Server" (Microsoft SQL Server Administrator’s Companion).


Управление размером БД и журнала транзакций

По мере увеличения вашего приложения база данных, с которой оно связано, возможно, тоже будет расти, пополняясь новыми данными. Журнал транзакций будет увеличиваться, поскольку из-за роста числа пользователей системы количество транзакций увеличивается. В базах данных и журналах регистрации транзакций SQL Server не предусмотрена возможность автоматического расширения границ выделенного для них пространства — это должен делать администратор БД или разработчик приложения. Ниже указаны проблемы, связанные с увеличением размера, и способы их решения:

·         Проблема: Журналу регистрации транзакций не хватает места

1.       Используйте оператор SQL Server DUMP TRANSACTION с параметром with no_log для очистки журнала транзакций.

2.       Выполняйте разгрузку журнала чаще.

3.       Используйте опцию trunc. log on chkpt. .

·         Проблема: Базе данных не хватает места

·         Используйте оператор SQL Server ALTER DATABASE для выделения БД дополнительного пространства.

Вы можете выполнять эти действия, используя сигналы тревоги или добавив соответствующие функции в само приложение. Например, следующая хранимая процедура вызывается из приложения и может быть использована для расширения БД и соответствующего логического устройства БД при передаче ей имени базы данных dbname. [Прим. перев. Логическое устройство БД – специальный системный файл, созданный SQL Server для размещения одной или нескольких БД] Исходя из того, что БД расположена на одном логическом устройстве, хранимая процедура вычисляет новый размер, необходимый для размещения увеличившейся БД и выделяет дополнительное пространство. После того, как это было проделано один раз, хранимая процедура будет расширять БД самостоятельно.

create proc dbexpand (@dbname sysname, @dbdevice sysname, @increment int)
as
set nocount on             // Отключение сообщений о количестве записей, подвергнутых
                           // обработке
declare @current_size int  // Переменная для хранения текущего размера БД
declare @new_size varchar(12) // Переменная для хранения нового размера БД
declare @cmd varchar(100)  // Переменная для формирования строки
                                     // с инструкцией ALTER DATABASE

/* Получение текущего размера логического устройства БД из
 системной таблицы sysdevices в главной БД и добавление инкремента @increment в страницах.
 SQL Server размещает данные в страницах, поэтому нужно определить число добавляемых страниц. */

/* Получение текущего размера выделенного для указанной БД пространства из
 системной таблицы sysdevices в главной БД */

select @current_size=high-low+1 from master..sysdevices where name = @dbdevice

/* Определение количества страниц, соответствующего новому размеру БД. Преобразование этой величины в МБ
путем умножения на 512*/

select @new_size = convert(varchar, (@current_size + (@increment * 512)))

/* Формирование строки с инструкцией для увеличения размера логического устройства  и ее выполнение.

select @cmd=’DISK RESIZE name = ‘ + @dbdevice + ‘, size = ‘+@new_size

--select @cmd

EXEC (@cmd)

/* Формирование строки с инструкцией для увеличения размера БД и ее выполнение.

select @cmd=’ALTER DATABASE ‘+ @dbname + ‘ ON ‘+ @dbdevice +’ = ‘ +
convert(varchar, @increment)

EXEC (@cmd)

За более подробной информацией по устройствам БД и о том, как они используются для размещения БД, обращайтесь к "В помощь администратору Microsoft SQL Server" (Microsoft SQL Server Administrator’s Companion).

В данном документе была представлена стратегия преобразования приложения на базе Btrieve в приложение, использующее Microsoft SQL Server, ODBC и SQL. В нем описаны методика, позволяющая за два шага перейти от приложения API Btrieve, использующего метод доступа ISAM, к реляционному приложению с архитектурой клиент/сервер на базе Microsoft SQL Server. Были проанализированы различные способы доступа к данным и обработки результатов, а также их влияние на производительность обеих платформ. Применяя методику, описанную в данном документе, вы можете создать высокопроизводительное приложение, которое использует всю мощь и гибкость Microsoft SQL Server.

Дополнительная справочная литература

Чтобы больше узнать о разработке приложений для SQL Server, обращайтесь к следующим информационным ресурсам:

·         Microsoft SQL Server 6.5 Books Online.

·         Microsoft Windows NT Resource Kit.

·         Различные статьи из базы знаний Microsoft (Microsoft Knowledge Base), доступные на диске CD-ROM TechNet.

·         В изучении SQL Server вам могут помочь Службы обучения Microsoft (Microsoft Education Services) или Авторизованные [Сертифицированные] центры обучения Microsoft (Authorized Training Center). Для получения более подробной информации свяжитесь с отделом обслуживания клиентов Microsoft (Microsoft Customer Service) по телефону (800) 426-9400.

·         Основы систем БД (Fundamentals of Database Systems) Рамеза Элмасари (Ramez Elmasari) и Шеймканта Навата (Shamkant Navathe), Издательство Benjamin Cummings Publishing Company, Redwood, California.

Средства

Следующие программные средства входят в состав Microsoft Windows NT Resource Kit или их можно переписать с веб-сервера Microsoft:

·         Sqlhdtst.exe

Утилита Win32®, имитирующая выполнение операций ввода/вывода SQL Server при обмене данными с дисковой подсистемой. Может быть использована как средство диагностики для выявления проблем дисковой подсистемы.

·         Kill.exe

Используется для уничтожения процессов Windows NT. Похожа на PVIEWER или PVIEW, с той только разницей, что запускается из командной строки и использует идентификатор процесса в качестве параметра.

·         Pmon.exe

Утилита Win32 для мониторинга процессов Windows NT, использования памяти и т.д.

·         Pview.exe

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

·         Pviewer.exe

Windows-программа для получения списка выполняющихся в системе процессов, их завершения и повышения приоритета. Похожа на Pview.exe, но может работать с удаленными компьютерами.

·         Tlist.exe

Программа используется для получения списка имен и идентификаторов активных процессов. Используется в сочетании с Kill.exe для прерывания процесса на удаленном компьютере (с помощью Remote.exe).