End Sub. PrintStats может дать некоторое представлениео том, почему одна методика работает быстрее,чемдругая

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

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

Далее в статье приведен пример использования этих функций для сравнения двух различных методик.

Оптимизация базы данных

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

Составление таблиц данных

Кроме обычных правил оптимизации,при построении таблиц необходимо помнить о следующем:

• При работесо связанными таблицами следует создавать постоянные связи.

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

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

• Не рекомендуется создавать поля данных, размер которых будет больше, чем необходимо. Access предоставляет место для данных по размеру поля этих данных. Можно впустую потратить массу сво­бодного места (и времени), используя типы данных или установки свойств, слишком большие для данных, содержащихся в этих полях.

Нормализация данных в целях повышения производительности

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

• Нормализованная база данных позволяет экономить дисковое пространство, а также препятствует появлению конфликтующих или некорректных данных, поскольку она не содержит повторяющих­ся данных.

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

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

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

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

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

Создание индексов, ускоряющих выполнение запросов

• Индексы могут ускорить процесс получения данных примерно в 10 раз.

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

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

• Можно проиндексировать все поля, для которых приложение применяет условие отбора. Создание индексов на множественных полях в таблицах упростит оптимизацию запросов, созданных позже в процессе разработки.

• Можно проиндексировать поля с обеих сторон ожидаемой связки. Поскольку поля Order Number относятся как к таблице Order Detail, так и к таблице Orders, обе таблицы должны иметь соответ­ствующий индекс. Но если необходимо выполнять много отчетов по дате заказа, возможно, данные поля также необходимо проиндексировать.

Раннее создание отношений для повышения производительности

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

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

Повышение производительности запросов

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

Чтобы понять, как оптимизировать запросы, необходимо понимать,как их обрабатываетJet. Каждый запрос проходит четыре этапа:

1. Определение — создается SQL-оператор с помощью одного из нескольких инструментальных средств.

2. Компиляция — SQL-строка разбивается на составные части.

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

4. Выполнение — используя план оптимизации, Jet передает результирующий набор пользователю.

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

Jet размещает составные части строки в иерархической внутренней структуре. Эти части весьма напо­минают ключевые слова SQL-оператора. В основе лежат базовые таблицы, используемые запросом (Из). Потом устанавливаются столбцы результата (Выбор). Далее следуют условия отбора или ограничения, заданные запросом (Где). Затем оцениваются отношения базовых таблиц (Объединение). Наконец, проис­ходит сортировка результирующего набора (Сортировка). Такая структура переходит в фазу оптимизации.

Оптимизация представляет собой самый сложный этап. Jet оценивает и рассчитывает стоимость каж­дого возможного подхода. Это делается путем рассмотрения запроса под двумя различными углами: с точки зрения доступа к базовым таблицам и с точки зрения исследований связей между ними. Понимание действий Jet может способствовать разработке более быстрых запросов в дальнейшем.

Для Jet существует три способа получения строк данных из таблиц:

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

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

• Срочная оптимизация (Rushmorc Optimizations). Этот способ доступен только втех случаях, когда установлены ограничения более чем для одного индекса в запросе. Срочная оптимизация позволя­ет Jet считать намного меньше страниц данных, иногда вообще ни одной. При использовании способа срочной оптимизации Jet читает только индексные страницы,что весьма эффективно.

Очевидно, что сканирования следует избегать при любой возможности и попытаться воспользоваться индексами. Но как проверить, будет ли для данного запроса действовать лучший способ — срочная оп­тимизация? Срочную оптимизацию нельзя просто включить или выключить, и не существует какого-то явного индикатора для определения этого. Она всегда включена, но лишь определенные виды запросов могут воспользоваться преимуществами данного способа. Для того чтобы была реализована срочная оп­тимизация, должны удовлетворяться следующие три условия:

• Запросы должны содержать множественные индексы.

• Для данных индексированных полей должны быть заданы условные ограничения.

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

• Перекрытие индексов. Условие отбора с оператором AND. Jet может применить способ срочной оптимизации на данном наборе ограничений, поскольку индексированы оба поля.

WHERE CompanyName='Ernst Handle' And City='Graz'

• Объединение индексов. Условие отбора с оператором "OR". Jet может применить способ срочной оптимизации на данном наборе ограничений, поскольку индексированы оба поля.

WHERE CompanyName='Ernst Handle' Or City='Graz'

• Счетчики индексов. Составные запросы, возвращающие только счетчик записей. При срочной оптими­зации обрабатывается каждый запрос, даже если не существует набора ограничений в условииWhere.

SELECT Count(*) FROM Customers

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

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

Таблица 2. Типы объединения: принцип работы и отличительные признаки.

Jet выбирает один из этих планов в зависимости от таких факторов, как:

• Число записей в каждой базовой таблице,

• Число страниц данных, используемых базовыми таблицами,

• Местонахождение и тип таблицы — локальная ISAM или ODBC,

• Избирательность индексов таблиц — разрешены ли нулевые значения или повторения,

• Число страниц индекса.

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

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

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

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

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

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

Как известно.Jet создает план выполнения для каждого запроса. Создав в системном реестре запись

\HKEY_LOCALMACHINS\SOFTWARE\MICROSOFT\JET\4 . 0\ENGINES\DEBUG

и установив значение строки вON, Jet создает или добавляет текстовый файл в текущей папке плана выполнения запроса. В этот план входит многое из того, что было описано выше. План изменить невоз­можно, если не изменять схему данных, структуру запроса или ограничения запроса. Чем подробнее план, тем лучше. В листинге 3 приведен план выполнения для запроса Quarterly Orders для фирмы Northwind.

Листинг 3. План выполнения с использованием преимуществ срочной оптимизации.

--- Quarterly Orders ---

- Inputs to Query –

- Table 'Customers'

Using index 'PrimaryKey'

Having Indexes: