Оптимизация использования сервера

Использование хранимых процедур

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

Однако следует иметь в виду, что хранимые процедуры пишутся на процедурном расширении SQL используемого сервера. Cуществуют официальные стандарты непроцедурного языка SQL ANSI/ISO SQL-86, SQL-89 и SQL-92, но на сегодняшний день не существует стандартов на процедурные расширения этого языка. Каждая серверная СУБД имеет свой набор процедурных расширений, отличающийся от соответствующих расширений других СУБД. Некоторые сервера, например Borland IB Database, поддерживают создание и использование в процедурах функций, определенных пользователем (UDF - User Defined Functions), а некоторые не поддерживают. Поэтому при смене платформы хранимые процедуры, скорее всего, потребуется переписывать. Отметим также, что чаще всего серверные хранимые процедуры создаются путем ручного кодирования, и для их создания, как правило, не существует удобных визуальных средств разработки и отладки наподобие имеющихся в C++Builder. Поэтому при принятии решения о создании тех или иных хранимых процедур не мешает оценить возможные трудозатраты - иногда может оказаться, что они не стоят ожидаемого эффекта.

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

Использование предварительной подготовки запросов

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

Что каcается передаваемых на сервер параметров запроса, их число и объем рекомендуется минимизировать точно так же, как и в случае параметров хранимых процедур.

Использование представлений (View) и параметризованных запросов.

Нередко начинающие программисты используют динамическое создание запросов на этапе выполнения, изменяя содержимое строкового массива, содержащегося в свойстве SQL компонента TQuery (например, периодически модифицируя предложение WHERE). При часто повторяющихся запросах такого типа это не самый оптимальный способ пересылки запросов на сервер, так как в этом случае обязательно осуществляется предварительная подготовка запросов, заключающаяся в пересылке всего текста на сервер, а также оптимизации и компиляции его сервером. Более предпочтительным в этом случае является использование параметризованных запросов и метода Prepare(), либо использование представлений (View) сервера, представляющих собой не что иное как хранимый на сервере заранее скомпилированный запрос. В последнем случае можно избежать не только лишних повторных компиляций запроса сервером, но и излишней перегрузки клиента генерацией запросов.

Использование свойства UpdateMode

Свойство UpdateMode компонентов TDBDataSet определяет состав оператора WHERE, генерируемого BDE при обновлении данных. Рассмотрим, каким получится оператор WHERE при редактировании поля SYMBOL содержащейся на сервере Oracle Workgroup Server копии таблицы HOLDINGS из входящей в комплект поставки C++Builder базы данных BCDEMOS при разных значениях этого свойства. Сгенерированные SQL-предложения можно пронаблюдать с помощью SQL Monitor.

По умолчанию значением свойства UpdateMode является UpWhereAll, и в этом случае BDE генерирует предложение WHERE, содержащее все поля таблицы. При этом сгенерированный оператор SQL, если только он не переопределен с помощью компонента TUpdateSQL, будет выглядеть следующим образом:

UPDATE "HOLDINGS" SET "SYMBOL"=:1 WHERE "ACCT_NBR"=:2 AND "SYMBOL"=:3 AND "SHARES"=:4 AND "PUR_PRICE"=:5 AND "PUR_DATE"=:6 AND "ROWID"=:7.

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

Одним из других возможных значений этого свойства является UpWhereChanged, при котором в предложении WHERE содержатся только поля, измененные в данном запросе, и ключевые поля. В этом случае запрос имеет следующий вид:

UPDATE "HOLDINGS" SET "SYMBOL"=:1 WHERE "ROWID"=:2 AND "SYMBOL"=:3

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

Третьим возможным значением свойства UpdateMode является UpWhereKeyOnly. В этом случае предложение WHERE содержит только ключевое поле:

UPDATE "HOLDINGS" SET "SYMBOL"=:1 WHERE "ROWID"=:2

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

Повышение эффективности SQL-запросов

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

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

SELECT * FROM <имя таблицы> WHERE (SELECT COUNT (*) FROM <имя таблицы> WHERE <условие>) >0

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

SELECT * FROM <имя таблицы> WHERE EXISTS (SELECT * FROM <имя таблицы> WHERE <условие>)

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

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

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

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