Microsoft Access: ЗапросыАвтор Allen Browne: июнь 2008 г. Последнее обновление: март 2010 г. Оригинал http://allenbrowne.com/QueryPerfIssue.html Перевел с английского Александр Артамонов, ноябрь 2011 г. Содержание:Типичные тормоза запросовПредмет этой статьи - часто совершаемые ошибки, приводящие к ухудшению производительности запросов. Мы предполагаем, что ваши таблицы содержат первичные ключи, внешние ключи и индексы на полях, по которым совершается поиск и сортировка.
Используйте SQL, а не VBAJET/ACE (движок запросов в Аксессе) использует Structured Query Language (SQL), как и многие базы данных. JET также способен вызывать код Visual Basic for Applications (VBA). Это радикально расширяет возможности JET-а, но вызов VBA теряет смысл, если работу может выполнить SQL. Is Null, не IsNull()
Is Null является родным выражением SQL. IsNull() - вызов VBA функции. Не бывает веских причин вызывать IsNull() в запросе, так как SQL может самостоятельно оценить смысл выражения.
IIf(), не Nz()
Функция 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(), но и к любой функции VBA, возвращающей Variant. Просто Nz() - наиболее распространенный случай. (Обратите внимание: функция JET IIf() намного более эффективна, чем одноименная функция в VBA. Функция VBA тратит время на оценку и истинной и ложной части и генерирует ошибки, если какая-нибудь из частей не срабатывает (даже если эта часть не нужна.) У JET-овской функции IIf() подобных проблем нет.)
Доменные агрегатные функцииDLookup(), DSum() и т.д. - медленные по выполнению функции. Они требуют вызова VBA, вызова службы выражений (Expression Service) и расходуют ресурсы (открывая дополнительные подключения к файлу данных). Особенно все затягивается, если JET должен выполнить операцию на каждой строке запроса. Подзапросы будут значительно быстрее, чем доменные агрегатные функции. В большинстве случаев, вложенный запрос будет еще быстрее (т.е. еще один сохраненный запрос, который включается как "таблица" в первый запрос.) Бывают случаи, когда доменная агрегатная функция все-таки является лучшим решением, которое у вас есть (например, когда нужны редактируемые результаты). Для таких случаев было бы полезно воспользоваться ELookup() вместо встроенных функций.
Составляйте выражения, чтобы использовать индексыЗапрос будет намного быстрее, если база данных может использовать индекс, чтобы выбирать или сортировать записи. Вот два примера.
Критерии по вычисляемым полямВ примере справа функция Year() выглядит проще, но она будет выполняться намного медленнее. Для каждой записи JET выполняет вызов функции VBA, получает результат, и затем сканирует таблицу целиком, чтобы отбросить записи с другими годами. Без этого вызова функции JET мог бы использовать индекс, чтобы мгновенно выбрать записи для 2008 года. Выполнение будет на порядок быстрее. (Вы могли бы использовать WHERE Table1.MyDate Between #1/1/2008# And #12/31/2008#, но так теряются любые даты, у которых есть компонента времени в последнем дне). Особенно избегайте вызов VBA в критериях отбора или в сортировке с тем, чтобы JET мог использовать индекс.
Сортировка по конкатенированным полямПредставльте комбобокс для выбора людей по имени. Поле ClientID спрятано, а Surname и FirstName сцеплены конкатенацией в один столбец, так что отображается полное имя, даже когда комбобокс не находится в раскрытом состоянии. Не сортируйте по конкатенированному полю! Сортируйте по двум полям, чтобы JET мог использовать индексы по этим полям для выполнения сортировки. Оптимизируйте групповые запросыОптимизатор JET-а весьма неплох, так что вам может показаться, что простые запросы будут выполняться быстро и без советов из этого раздела. Все равно стоит потраченных усилий сделать запрос как можно лучше, чтобы он не стал внезапно тормозить, когда вы его измените.
WHERE против HAVINGИтоговые запросы (те, что с предложением GROUP BY) могут иметь как предложение WHERE, так и предложение HAVING. Сначала выполняется WHERE - перед группировкой; затем следует HAVING - когда высчитываются итоги. Итак, имеет смысл поместить критерии в предложение WHERE и использовать HAVING только, когда нужно примерить критерии на итоги по группам. В конструкторе запросов Аксесса это не очевидно. Когда вы добавляете поле в строке конструктора, Аксесс устанавливает строку Групповая операция на Группировка (Group By), и хочется добавить критерии прямо под ним. Если вы это сделаете, критерии отбора окажутся в предложении HAVING. Чтобы использовать предложение WHERE clause, добавьте поле в грид конструктора еще раз и выберите Where в строке Групповая операция.
FIRST против GROUP BY
Когда вы добавляете поле в групповой запрос, Аксесс предлагает Группировку/Group By в строке Групповая операция. Следовательно, по умолчанию Аксесс будет группировать по всем этим полям. Первичный ключ уникален. Так, если вы группируете по полю первичного ключа, нет никакой необходимости группировать по другим полям в этой таблице. Вы можете оптимизировать запрос, выбрав First вместо Group By в строке Групповая операция под другими полями. First позволяет JET вернуть значение из первой совпадающей записи, без необходимости группировать по этому полю. Это сильно меняет дело в ситуации с полями типа Memo. Если вы делаете GROUP BY по МЕМО-полю (Notes в примере), Аксесс сравнивает только первые 255 символов, а остальные просто отсекаются! Выбирая First вместо Группировка/Group By, JET может вернуть поле Memo полностью из первого же совпадения. Так что это не только более эффективно; это реально решает проблему усечения полей Memo. (Минусом использования First является получение полем псевдонима, напр. FirstOfNotes.) Прочие приемы оптимизацииПрочие предложения общего характера для оптимизации запросов в JET:
Оптимизация запросов - огромная тема. Для дальнейшего чтения см. SQL Queries for Mere Mortals авторов Michael Hernandez и John Viescas. |