Microsoft Access: Запросы
Автор Allen Browne: июнь 2008 г. Последнее обновление: март 2010 г.
Оригинал http://allenbrowne.com/QueryPerfIssue.html
Перевел с английского Александр Артамонов, ноябрь 2011 г.
Содержание:
Типичные тормоза запросов
Предмет этой статьи - часто совершаемые ошибки, приводящие к ухудшению производительности запросов.
Мы предполагаем, что ваши таблицы содержат первичные ключи, внешние ключи и индексы на полях, по которым совершается поиск и сортировка.
Используйте SQL, а не VBA
JET/ACE (движок запросов в Аксессе) использует Structured Query Language (SQL), как и многие базы данных. JET также способен вызывать код Visual Basic for Applications (VBA). Это радикально расширяет возможности JET-а, но вызов VBA теряет смысл, если работу может выполнить SQL.
Is Null, не IsNull()
 | WHERE IsNull(Table1.Field1) |
 | WHERE (Table1.Field1 Is Null) |
Is Null является родным выражением SQL.
IsNull() - вызов VBA функции.
Не бывает веских причин вызывать IsNull() в запросе, так как SQL может самостоятельно оценить смысл выражения.
IIf(), не Nz()
| | SELECT Nz(Table1.Field1,0) AS Amount |
| | SELECT IIf(Table1.Field1 Is Null, 0, Table1.Field1) AS Amount |
Функция Nz() заменяет Null другим значением (для чисел обычно нулем, для текста - пустой строкой.) Новое значение является типом данных Variant, а VBA помечает его в свою очередь подтипом: String, Long, Double, Date и т.д.
В VBA это просто замечательно: функция может возвращать разные подтипы в разных ситуациях. Но в запросе столбец может быть только ОДНОГО типа данных. JET, следовательно, воспринимает значения типа Variant как текстовые, так как что угодно (числа, даты, символы, ...) являются валидными в текстовом столбце.
Визуальным признаком того, что JET воспринимает столбец как текст, является выравнивание по левому краю. Числа и даты отображаются выровненными по правому краю.
Если вы ожидали числовую или колонку с датами, у вас серьезные проблемы. Текстовые поля оцениваются посимвольно. Т.е. 2 больше 19, потому что первый символ (2) больше, чем первый символ в другом тексте (1 in 19.) Подобным образом, 4/1/2009 идет после 1/1/2010 в текстовом столбца, так как 4 идет после 1.
Звоночек раздается, когда вы видите столбец выровненным по левому краю как текст, в то время как вы ожидали, что он будет трактоваться как число. Записи будут выбраны неверно, а сортировка будет выглядеть бессмысленной.
Можно было бы типизировать выражение еще вызовом еще одной функции VBA, но лучшим решением было бы позволить JET выполнить работы, не вызывая VBA вообще.
Вместо:
Nz(MyField,0)
используйте:
IIf(MyField Is Null, 0, MyField)
Да, придется чуть больше напечатать, но есть свои плюсы:
- Вы избегаете вызова функции Nz().
- Вы сохраняете желаемый тип данных.
- Критерии применяются корректно.
- Столбец сортируется корректно.
Этот принцип относится не только к Nz(), но и к любой функции VBA, возвращающей Variant. Просто Nz() - наиболее распространенный случай.
(Обратите внимание: функция JET IIf() намного более эффективна, чем одноименная функция в VBA. Функция VBA тратит время на оценку и истинной и ложной части и генерирует ошибки, если какая-нибудь из частей не срабатывает (даже если эта часть не нужна.) У JET-овской функции IIf() подобных проблем нет.)
Доменные агрегатные функции
DLookup(), DSum() и т.д. - медленные по выполнению функции. Они требуют вызова VBA, вызова службы выражений (Expression Service) и расходуют ресурсы (открывая дополнительные подключения к файлу данных). Особенно все затягивается, если JET должен выполнить операцию на каждой строке запроса.
Подзапросы будут значительно быстрее, чем доменные агрегатные функции. В большинстве случаев, вложенный запрос будет еще быстрее (т.е. еще один сохраненный запрос, который включается как "таблица" в первый запрос.)
Бывают случаи, когда доменная агрегатная функция все-таки является лучшим решением, которое у вас есть (например, когда нужны редактируемые результаты). Для таких случаев было бы полезно воспользоваться ELookup() вместо встроенных функций.
Составляйте выражения, чтобы использовать индексы
Запрос будет намного быстрее, если база данных может использовать индекс, чтобы выбирать или сортировать записи. Вот два примера.
| | WHERE Year(Table1.MyDate) = 2008 |
| WHERE (Table1.MyDate >= #1/1/2008#) AND (Table1.MyDate < #1/1/2009#) |
Критерии по вычисляемым полям
В примере справа функция Year() выглядит проще, но она будет выполняться намного медленнее.
Для каждой записи JET выполняет вызов функции VBA, получает результат, и затем сканирует таблицу целиком, чтобы отбросить записи с другими годами. Без этого вызова функции JET мог бы использовать индекс, чтобы мгновенно выбрать записи для 2008 года. Выполнение будет на порядок быстрее.
(Вы могли бы использовать WHERE Table1.MyDate Between #1/1/2008# And #12/31/2008#, но так теряются любые даты, у которых есть компонента времени в последнем дне).
Особенно избегайте вызов VBA в критериях отбора или в сортировке с тем, чтобы JET мог использовать индекс.
| SELECT ClientID, Surname & ", " + FirstName AS FullName FROM tblClient ORDER BY Surname & ", " & FirstName; |
| SELECT ClientID, Surname & ", " + FirstName AS FullName FROM tblClient ORDER BY Surname, FirstName, ClientID; |
Сортировка по конкатенированным полям
Представльте комбобокс для выбора людей по имени. Поле ClientID спрятано, а Surname и FirstName сцеплены конкатенацией в один столбец, так что отображается полное имя, даже когда комбобокс не находится в раскрытом состоянии.
Не сортируйте по конкатенированному полю! Сортируйте по двум полям, чтобы JET мог использовать индексы по этим полям для выполнения сортировки.
Оптимизируйте групповые запросы
Оптимизатор JET-а весьма неплох, так что вам может показаться, что простые запросы будут выполняться быстро и без советов из этого раздела. Все равно стоит потраченных усилий сделать запрос как можно лучше, чтобы он не стал внезапно тормозить, когда вы его измените.
| | SELECT ClientID, Count(InvoiceID) AS HowMany FROM tblInvoice GROUP BY ClientID HAVING ClientID = 99; |
| SELECT ClientID, Count(InvoiceID) AS HowMany FROM tblInvoice WHERE ClientID = 99 GROUP BY ClientID; |
WHERE против HAVING
Итоговые запросы (те, что с предложением GROUP BY) могут иметь как предложение WHERE, так и предложение HAVING. Сначала выполняется WHERE - перед группировкой; затем следует HAVING - когда высчитываются итоги. Итак, имеет смысл поместить критерии в предложение WHERE и использовать HAVING только, когда нужно примерить критерии на итоги по группам.
В конструкторе запросов Аксесса это не очевидно. Когда вы добавляете поле в строке конструктора, Аксесс устанавливает строку Групповая операция на Группировка (Group By), и хочется добавить критерии прямо под ним. Если вы это сделаете, критерии отбора окажутся в предложении HAVING. Чтобы использовать предложение WHERE clause, добавьте поле в грид конструктора еще раз и выберите Where в строке Групповая операция.
FIRST против GROUP BY
| SELECT EmployeeID, LastName, Notes FROM Employees GROUP BY EmployeeID, LastName, Notes; |
| SELECT EmployeeID, First(LastName) AS FirstOfLastName, First(Notes) AS FirstOfNotes FROM Employees GROUP BY EmployeeID; |
Когда вы добавляете поле в групповой запрос, Аксесс предлагает Группировку/Group By в строке Групповая операция. Следовательно, по умолчанию Аксесс будет группировать по всем этим полям.
Первичный ключ уникален. Так, если вы группируете по полю первичного ключа, нет никакой необходимости группировать по другим полям в этой таблице. Вы можете оптимизировать запрос, выбрав First вместо Group By в строке Групповая операция под другими полями. First позволяет JET вернуть значение из первой совпадающей записи, без необходимости группировать по этому полю.
Это сильно меняет дело в ситуации с полями типа Memo. Если вы делаете GROUP BY по МЕМО-полю (Notes в примере), Аксесс сравнивает только первые 255 символов, а остальные просто отсекаются! Выбирая First вместо Группировка/Group By, JET может вернуть поле Memo полностью из первого же совпадения. Так что это не только более эффективно; это реально решает проблему усечения полей Memo.
(Минусом использования First является получение полем псевдонима, напр. FirstOfNotes.)
Прочие приемы оптимизации
Прочие предложения общего характера для оптимизации запросов в JET:
- С многотабличными запросами по возможности используйте JOIN-ы. JET выполнит такой запрос быстрее, чем с предложением WHERE по внешнему ключу.
- Возвращайте по возможности меньше полей. Это оптимизирует использование памяти и может сократить количество обращений к диску. Но включайте ключевые поля, чтобы дать JET-у быстрый способ идентифицировать записи.
- Стройте строки запроса динамически,как показано здесь форма поиска. Особенно там, где пользователь будет вводить только несколько из возможных критериев, которые вы ему предоставляете. Это радикально упрощает критерии. Аксесс применяет фильтру интеллектуально. т.е. Filter или WhereCondition обычно применяются перед тем, как он запрашивает записи из файла с данными.
- Избегайте множества таблиц на внешней стороне JOIN-а, так как JET может их неправильно интерпретировать.
- Чтобы контролировать порядок выполнения, сохраните один запрос и используйте его как таблицу для другого запроса (вложенные запросы). Это важно, так как JET не считается со скобками в предложении FROM, когда он составляет план выполнения.
- Подзапросы в общем менее эффективны, чем другие приемы (такие как JOIN-ы или вложенные сохраненные запросы), но более эффективны, чем доменные агрегатные функции.
- Советы по перекрестным запросам см. Техника построений перекрестных запросов.
- Используйте ShowPlan для JET для более подробной информации по поводу того, как JET планирует выполнение запроса.
Оптимизация запросов - огромная тема. Для дальнейшего чтения см. SQL Queries for Mere Mortals авторов Michael Hernandez и John Viescas.