48648 (Разработка программы запросов), страница 3

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

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

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

5. Заполнив все узлы и связи, вписать числа для коэффициентов фильтрации и коэффициентов соединения, основываясь, если возможно, на статистике по таблицам для промышленного приложения. Если нет промышленных данных, то постараться представить коэффициенты как можно точнее. Нет необходимости добавлять коэффициенты соединения рядом со связями, представляющими внешние соединения. Практически всегда для дополнительной таблицы внешнего соединения (сразу за ключевыми словами LEFT OUTER) условия фильтрации не указаны, поэтому коэффициент фильтрации равен 1,0, что обозначается просто фактом отсутствия числа на диаграмме.

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

Пример.

Имеется запрос

SELECT C.Phone_Number, C.Honorific, C.First_Name, C.Last_Name,

C.Suffix, C.AddressJD, A.AddressJD, A.Street_Address_Linel,

A.Street_Address_Line2, A.City_Name, A.State_Abbreviation, A.ZIP_Code,

DD.Deferred_Shipment_Date, OD.Item_Count. DOT.Text, OT.Text,

P.Product_Description, S.Shipment_Date

FROM Orders O, Orderjtetails OD, Products P, Customers C, Shipments S,

Addresses A, Code_Translations DOT, Code_Translations OT

WHERE UPPER(C.Last_Name) LIKE :Last_Name||'%'

AND UPPER(C.First_Name) LIKE :First_Name||'%'

AND OD.OrderID = O.Order_ID

AND O.CustomerID = C.Customer_ID

AND OD.Product_ID = P.Product_ID(+)

AND OD.Shipment_ID = S.Shipment_ID(+)

AND S.Address_ID = A.Address_ID(+)

AND O.Status_Code = OT.Code

AND DT.CodeJype = 'ORDER_STATUS'

AND OD.Status_Code = ODT.Code

AND CDT.CodeJype = 'ORDERJIETAILJTATUS

AND O.Order_Date > :Now - 366

ORDER BY C.CustomerID, O.Drder_ID DESC, S.ShipmentID, DD.Order_Detail_ID;

Построенная по вышеперечисленным правилам для данного запроса диаграмма показана на рис.1.2.

Множество подробностей, присутствующих на полных диаграммах запросов, не обязательны, только для самых редких проблем. Для концентрации на необходимых элементах нужен только скелет диаграммы и приблизительные коэффициенты фильтрации. Изредка требуются коэффициенты соединений, но обычно только когда любой из детальных коэффициентов соединения меньше 1,5 или главный коэффициент соединения меньше 0,9. Это, в свою очередь, значит, что меньшее количество данных требует создания более простых диаграмм соединения. Нет необходимости узнавать количество строк для таблиц без фильтров. На практике в многосторонних соединениях обычно есть фильтры только для 3-5 таблиц, поэтому даже самый сложный запрос легко изобразить на диаграмме, не используя множество запросов для сбора статистики.

Отбросив перечисленные детали, можно упростить рис. 1.1 до рис.1.3.

1.5 Программы построения плана выполнения запроса

В различных СУБД имеются собственные средства для построения плана выполнения запроса.

План выполнения запроса для MS SQL Server проще всего просмотреть из SQL Server Management Studio [2]/

Для того чтобы получить информацию об ожидаемом плане выполнения запроса, можно в меню Query (Запрос) выбрать команду Display Estimated Execution Plan (Отобразить ожидаемый план выполнения). Если нужно узнать реальный план выполнения запроса, можно перед его выполнением установить в том же меню параметр Include Actual Execution Plan (Включить реальный план выполнения). В этом случае после выполнения запроса в окне результатов в SQL Server Management Studio появится еще одна вкладка Execution Plan (План выполнения), на которой будет представлен реальный план выполнения запроса. При наведении указателя мыши на любой из этапов можно получить о нем дополнительную информацию (рис. 1.4).

Еще одно интерактивное графическое средство, которое позволяет администратору базы данных или разработчику писать запросы, выполнять различные запросы одновременно, просматривать результаты, анализировать план запроса и получать поддержку для улучшения плана выполнения – SQL Query Analyzer. Опция просмотра плана выполнения графически показывает методы получения данных, используемые оптимизатором запроса Microsoft SQL Server. В графическом исполнении плана используются иконки для представления специфичных действий и запросов в SQL Server, а не изображения в виде таблиц, созданных инструкциями SET SNOWPLAN_ALL или SET SNOWPLAN_TEXT. Это очень полезно для понимания скоростных показателей запроса. Кроме того, SQL Query Analyzer показывает советы по дополнительным индексам и статистическим данным в неиндексируемых колонках, что улучшит возможности оптимизатора запроса рационально обработать запрос. В частности, SQL Query Analyzer показывает какие статистические данные пропущены, тем самым, заставляя оптимизатор запроса давать оценку по селективности, а затем дает возможность создать пропущенные статистические данные.

Иконки, изображенные в графическом плане исполнения, представляют физические операторы, которые используются MS SQL Server для выполнения запроса.

Пример

Запрос

SELECT DISTINCT t.date AS c0,

c.prefijoext AS c1,

c.numeroext AS c2,

c.checkbook AS c3

FROM Transac t (nolock)

JOIN cmpasociados c (nolock)

ON t.nrotrans = c.nrotrans

JOIN tiposcmp you (nolock)

ON c.codcmp = you.codcmp

JOIN checkbooks so (nolock)

ON c.checkbook = so.checkbook

AND t.codemp = so.codemp

WHERE T.Nrotranselim is null

AND

(

CASE

WHEN T.Codcmp

IN (

' CA', ' CC', ' CB', ' CE'

,' LR', ' LO', ' LP', ' CZ'

,' VA', ' VB', ' VC', ' YOU'

,' VZ'

)

THEN T.Nrotransaut

WHEN T.Codcmp

IN (' I', ' E', ' RD')

THEN T.Nrotransctrl

ELSE T.Nrotrans END

)

IS NOT NULL

AND (t.CodEmp IS NULL OR t.codemp = 1)

AND c.checkbook = 25

AND t.codsuc = 1

ORDER BY C2 DESC

имел следующий план исполнения в Query Analyzer (см. рис.1.5).

После применения индексов, получился план исполнения, показанный на рис.1.6.

1.6 Выводы

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

В настоящее время отсутствуют обучающие программы работе с реляционной алгеброй.

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

• изучить особенности работы всех реляционных операций;

• разработать структуру данных для хранения необходимой информации;

• разработать алгоритмы преобразования последовательности реляционных операций в запрос к СУБД для представления результата выполнения процедурного плана;

• разработать удобный интерфейс для формирования процедурного плана запроса;

• разработать обучающий и контролирующий компоненты программы.

2 Техническое задание на разработку обучающей программы построению запросов с использованием реляционных операций

Обучающая программа работе с реляционными операциями предназначена для формирования навыков построения плана выполнения запроса в процедурном виде, то есть в виде последовательности реляционных операций. В основе программы находится БД, позволяющая администратору программы формировать список вопросов для выполнения обучаемыми с контролем правильности введенных результатов.

2.1 Основание для разработки

Основанием для разработки программы «Обучающая программа построению запросов в процедурном виде» является задание на дипломное проектирование.

2.2 Назначение разработки

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

2.3 Требования к программе

Программа «Обучающая программа построению запросов в процедурном виде» должна обеспечивать автоматизацию следующих функций:

1) формирование вопросов для обучаемых;

а) соединение с внутренней БД программы;

б) подключение БД, к которой формируются учебные запросы;

в) запись текста запроса;

г) доступ к метаданным учебной БД;

д) подключение схемы данных учебной БД в виде графического файла;

е) формирование множества операций ответа на запрос;

ж) формирование различных верных последовательностей операций;

2) прием ответа от обучаемого;

а) возможность выбора операции из заданного множества;

б) формирование списка таблиц учебной БД;

в) динамическое подключение к списку таблиц имен таблиц, которые являются результатом предыдущих операций;

г) определение списка столбцов заданной таблицы;

д) подключение операндов выбранной операции;

е) изменение последовательности введенных операций;

ж) сравнение введенного ответа с эталонным множеством последовательностей операций;

з) формирование сообщений о неправильных действиях пользователя;

3) обучающая часть;

а) возможность просмотра результата промежуточной операции запроса;

б) возможность просмотра результата запроса;

в) просмотр правильного ответа;

4) контролирующая часть;

а) формирование вопросника из имеющихся во внутренней БД запросов;

б) выставление баллов за ответ;

в) формирование итоговой оценки;

г) сохранение результатов теста;

д) сохранение введенных ответов.

5) работа с отчетами;

а) формирование сообщений о результатах теста;

б) печать списка вопросов и списка ответов.

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

Входными данными должны быть:

информация для соединения с учебной БД;

структура таблиц учебной БД;

соединение с внутренней БД.

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

Выходными данными являются:

а) множество вопросов в виде текста запроса к учебной БД;

б) множество последовательностей операций для реализации запросов;

в) последовательности операций для реализации запросов к учебной БД, введенные пользователем;

7) оценки за введенные ответы при использовании программы в контролирующем режиме.

Выходные данные должны распечатываться на принтере, храниться в виде файла и/или записей БД программы и отображаться на экране монитора.

Надежность и устойчивость функционирования программы должны обеспечиваться:

- продуманной технологией обработки информации;

- контролем правильности ввода входной информации;

- системой диагностических сообщений;

- минимизацией операций, осуществляемых пользователем;

- соблюдением требований эксплуатационной документации.

При функционировании программы «Обучающая программа построению запросов в процедурном виде» должно обеспечиваться:

- решение задачи за приемлемое время;

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

Сохранность программ должна обеспечиваться наличием дубликатов программы и хранением программного обеспечения на внешних носителях.