Эффективный доступ к данным – запросы

Запросы ABL (ABL queries) – это операции управления данными, используемые для поиска или изменения данных, хранимых в БД.

В языке ABL существуют следующие три типа запросов:

FOR EACH / PRESELECT

FOR EACH
REPEAT PRESELECT
DO PRESELECT

FIND

FIND [ FIRST | NEXT | LAST | PREV ]
FIND
FIND CURRENT
FIND record WHERE ROWID (record) = expression.
CAN-FIND ( [ FIRST | LAST ] )
CAN-FIND (record WHERE …)

GET

DEFINE QUERY
OPEN QUERY
GET [FIRST | NEXT | LAST | PREV]
GET CURRENT

К остальным конструкциям ABL, связанным с запросами относятся:

• BY – определяет очередность вывода результатов посредством индексов или сортировки.
• USE-INDEX – указывает на необходимость использования конкретного индекса. Обеспечивает очередность в случае неиспользования конструкции с BY.
• SCROLLING – присоединяет список результатов к запросу.
• CACHE – присоединяет кэш запроса и список результатов к запросу.
• REPOSITION – изменяет текущую позицию запроса.
• INDEXED-REPOSITION – позволяет изменить позицию запроса, использующего один индекс, путем прямого переноса курсора индекса.
• NO-WAIT – позволяет избежать ожидания в случае блокировки записи и обеспечить непрерывность работы.
• CONTAINS – запрос через индекс по словам (word-index).
• FIELDS / EXCEPT – определяет, какие поля необходимо извлечь из базы данных и передать клиенту.

Для позиционирования внутри индекса используется индексный курсор (index cursor). Каждый активный FOR EACH использует один индексный курсор для каждого используемого индекса.
Выбрав один или более индексов, чтобы удовлетворить запросу, ABL пытается изолировать наименьшее необходимое индексное подмножество, чтобы вернуть минимально возможное число записей. Это называется брэкетирование (bracketing). Тщательный дизайн запроса может усилить возможности брэкетирования, предотвращая анализ всех записей.

Сводка характеристик запросов приведена в таблице (Таблица 15).

Таблица 15. Характеристики запросов

Запросы FOR EACH выполняются внутри итерационного блока ABL. При использовании множественных индексов порядок извлеченных записей не определен. Иначе он базируется на выбранном индексе.  Если требуется определенная последовательность выборки, она может быть задана опцией BY. Использование для этой цели USE-INDEX нежелательно.

Выбор записей в FOR EACH запросе всегда происходит последовательно. Произвольный доступ к записи невозможен.

Вычисление переменных в выражении WHERE выполняется только один раз перед входом в блок. FOR EACH запросы не кэшируются, т.к. кэш полезен при случайном доступе к записям.

С опцией NO-LOCK prefetch выполняется по умолчанию, но может выключаться опцией NO-PREFETCH.

PRESELECT запросы выполняются сервером также как и FOR EACH. На клиенте PRESELECT запросы обрабатываются за два прохода, также как в случае с сортировкой запросов FOR EACH. Сначала все удовлетворяющие запросу записи считываются и формируют список результатов со всеми ROWID. Список сортируется в случае необходимости. Затем, когда происходит обращение к записи через FIND, она считывается снова по ROWID и передается программе.

Запрос PRESELECT указывается в заголовке блоке REPEAT или DO. Запрос отрабатывается до входа в блок, внутри блока записи считываются оператором FIND.

Порядок считывания записей не определен, кроме случаев явного указания опции BY (USE-INDEX).

Переменные в выражении WHERE  определяются до входа в блок, как и для FOR EACH. Запрос не использует кэш.

PREFETCH используется при первом проходе запроса и не может быть отключен.

Возможна произвольная навигация по записям внутри запроса с помощью опций FIRST, NEXT, PREV, LAST.

Запрос FIND всегда использует один индексный брэкет и индексный курсор.
Выражение WHERE или опция USE-INDEX определяют, какой из индексов будет использован. Индекс и буфер, используемый в запросе, определяет, какой индексный курсор будет использован. Например, если два запроса используют тот же буфер и индекс, они используют один индексный курсор. Индексный курсор не принадлежит конкретному FIND и может использоваться несколькими FIND. Таким образом, один FIND может изменить позицию индексного курсора, которая используется другим запросом.

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

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

GET запросы могут использовать множественные брэкеты, если это возможно. GET запросы не относятся к конкретному блоку и распространяются на все блоки и процедуры в области видимости запроса. Порядок выборки записей не определен, но может задаваться опцией BY (USE-INDEX).

Для позиционирования внутри запросов используются операторы GET PREV, LAST и т.п., а также оператор REPOSITION.

Оператор GET NEXT может быть выполнен для любого запроса, тогда как остальные опции (FIRST, PREV, LAST)  выполняются только для следующих запросов:

• С указанной опцией SCROLLING
• С использованием одного индексного курсора.

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

• Все запросы получения следующей записи
• Запросы, использующие один индексный брэкет, могут возвращать первую, предыдущую и последнюю запись, просто позиционируясь по индексному брэкету. Это схоже с запросами FIND.
• REPOSITION TO ROWID может быть выполнен на сервере, если ROWID не содержится в списке результата и включена опция INDEXED-REPOSITION.

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

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

PREFETCH используется, если указаны опции SCROLLING или CACHE и опция NO-LOCK. Для выключения PREFETCH используется опция NO-PREFETCH. Если используется сортировка, то в пакеты по умолчанию включается максимальное число записей.

Компилятор использует следующую систему приоритетов при выборе доступных индексов:

1. Индекс, который указывается в опции USE-INDEX
2. Уникальный индекс, когда все ключевые компоненты сопоставляются на равенство
3. Индекс с наибольшим количеством сопоставлений на равенство
4. Индекс с наибольшим количеством сопоставлений по интервалу
5. Word-index на который ссылается оператор CONTAINS
6. Индекс с наибольшим количеством сопоставлений по сортировке
7. Первичный индекс

Для исполнения запроса ABL всегда использует, по крайней мере, один брэкет индекса (за исключением поиска по rowid, когда запись извлекается без использования индекса). Компилятор ABL анализирует запрос на предмет возможного использования элементов WHERE, OF, USING и т.д., доступных индексов и их компонентов для построения брэкетов.

Существует два типа брэкетов: брэкеты равенства (equality brackets) и брэкеты диапазона (range brackets). Брэкеты равенства определяют совокупность последовательных индексов, имеющих одинаковые значения (или начинающиеся с одной последовательности символов). Брэкеты диапазона определяют совокупность индексов от нижнего до верхнего пределов их значений.

Если запрос не позволяет построить никаких брэкетов, компилятор будет использовать брэкет по умолчанию, обычно это первичный индекс таблицы. В примере Программа 62 каждый из первых трех запросов использует брэкет по умолчанию для всего индекса целиком (whole-index). Во втором случае дополнительно выполняется сортировка по не индексному полю State. В запросе 4 используется брэкет равенства по индексу Name. В запросе 5 по тому же индексу используется брэкет диапазона.

Программа 62. Whole-Index и Brackets

for each customer: /* 1. entire table - no where clause */
end.

for each customer by state: /* 2. sort, no index on state */
end.

for each customer /* 3. no index on state */
    where (state = "OH"): 
end.

for each customer /* 4. Equality bracket */
    where name begins "M":
end.

for each customer /* 5. Range bracket */
    where ((name  >  "Off The Wall") and (name  <  "Quick Toss Lacrosse")) :
end.

Запросы GET, FOR EACH и PRESELECT (но не FIND) могут использовать несколько брэкетов индекса, что позволяет улучшить выполнение запросов, содержащих операторы OR и AND. Например, если для исполнения запроса использовался бы один единственный брэкет, то седьмой запрос в примере Программа 59 просматривал бы всю таблицу. Это легко проверить, задав стартовый параметр –v6q и получив листинг перекрестных ссылок.

Если одно из условий запроса не может быть выполнено поиском по индексу, производится оценка выражения в условии по содержимому записи, как в запросе 2 (Программа 59). Каждая из 21 записи, выбранной по индексу по первому условию, проверяется на содержимое поля balance. В общем случае такую проверку пытается выполнить сервер базы данных. Если сервер не может выполнить проверку в силу того, что для этого требуется доступ к программным переменным клиента, или потому что она не реализована на сервере (например, CAN-FIND), то сервер посылает все выбранные записи клиенту, и тогда проверка условия выполняется клиентом.

Следует еще раз подчеркнуть, что поиск по ROWID не использует индексы.

Поиск по FIND без модификаторов NEXT, PREV и т.д. выполняет операцию, которая отличается от запросов FIND с модификатором, а именно, он не только ищет запись, но и гарантирует, что найденная им запись является единственной в запросе. Если находится более чем одна запись, то возвращается ошибка. Для обеспечения такой функциональности, ABL необходимо найти первую запись, а затем проверить, нет ли еще записей. 2-й шаг является очень неэффективным, особенно если оценка запроса выполняется не сервером, а клиентом.

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

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

Для снижения сетевого трафика сервер может упаковывать несколько записей в один сетевой пакет при выполнении запроса. Эта операция называется prefetch, т.к. сервером пересылаются дополнительные записи, которых клиент еще не требовал. Prefetch кэш используется по умолчанию, когда ABL извлекает записи без блокировки (NO-LOCK) из базы данных, доступной через сеть. По умолчанию, при выполнении CAN-FIND, FIND, FOR или OPEN QUERY, сервер упаковывает несколько записей в сетевой пакет и отправляет их клиенту, где они сохраняются в кэше. Следующий запрос к серверу на получение записей посылается только в том случае, если запрошенная запись не находится в текущем кэше. Если запись находится в кэше, повторное чтение не выполняется (даже если задан параметр –rereadnolock). Чтобы отключить использование prefetch кэша и обеспечить чтение наиболее свежей версии записи, используйте опцию NO-PREFETCH в соответствующем операторе. Однако использование NO-PREFETCH может существенно снизить производительность.

Список результатов (result list) представляет собой список ROWID записей, которые попали в запрос. Он хранится на клиенте и обновляется когда поступают результаты от сервера. Список результатов строится при выполнении операций PRESELECT или OPEN QUERY, а также при необходимости сортировки результатов запроса. Список результатов используются для выполнения операций GET или FIND NEXT в PRESELECT-блоках.

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

Выбор типа запроса определяется функциональными соображениями, а также особенностями архитектуры конкретного приложения.

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

PRESELECT имеет два свойства, которых нет у FOR EACH – возможность произвольного доступа к записям и фиксированный набор записей, возвращаемый запросом, не смотря на обновления. Это гарантирует, что каждая запись будет возвращена один раз.

Если не используется список результата, то обновление записей в таблице может привести к тому, что одна и та же запись будет возвращена несколько раз. Ниже (Программа 63) приведен пример программы, потенциально выполняющей бесконечный цикл обработки таблицы customer и временной таблицы. Код, изменяющий записи customer, закомментирован для безопасности. Код, изменяющий временную таблицу, может быть прерван с клавиатуры. При присваивании C = 11 произойдет ошибка, так как такая запись уже существует. Но это не остановит цикл – будет выполнена обработка по умолчанию, в данном случае UNDO, NEXT – и цикл будет продолжен!

Программа 63. Опасность зацикливания оператора FOR EACH

/* Потенциально бесконечный цикл */
/* Не пытайтесь выполнять – прервать такой код можно только аварийно!  */
/* Код закомментирован
FOR EACH customer:
    cust-num = cust-num + 2.
END.
*/

/* Можно выполнить следующий пример, который можно прервать с клавиатуры */
/* Возникновение ошибки при нахождении записи с c = 11 не останавливает цикл! */

DEF TEMP-TABLE t
  FIELD c AS INT
  INDEX c AS PRIMARY UNIQUE c.
         
CREATE t.
c = 1.
CREATE t.
c = 11.

FOR EACH t:
    c = c + 2.
    DISPLAY t.c.
END.

Запросы FIND имеют следующие преимущества:

• FIND NEXT, PREV, FIRST и LAST работают с одинаковой скоростью и не требуют никаких затратных операций, таких как предварительная выборка и сортировка записей.
• Автоматическое позиционирование индексных курсоров позволяет осуществлять произвольный доступ к записи оптимальным образом.

В то же время, запросы FIND используют только один индексный брэкет, и порядок выборки определяется им (использовать BY нельзя).

GET запросы имеют следующие преимущества:

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

Недостатки GET запросов:

• Некоторые опции, используемые при перемещении внутри запроса, иногда замедляют работу, например GET LAST для мульти-индексного запроса.
• Опция INDEXED-REPOSITION, которая обеспечивает навигацию в стиле FIND запросов, трудна для использования, т.к. она не работает с объединениями и множественными индексами. Кроме того, сложно предсказать и понять поведение списка результатов с этой опцией.
• При использовании GET запросов приходится печатать больше, чем для FIND, по крайней мере, нужны 3 оператора: DEFINE QUERY, OPEN и GET.