Выпуск 10

Здравствуйте. Представляю Вашему вниманию новый выпуск рассылки. Перед чтением рассылки скачайте, пожалуйста, скрипт создания объектов для базы данных, скаченной в 9-ом номере рассылки или же заново скачайте полную версию базы данных.

Содержание рассылки:

• Новости от Sybase
• Процедура вывода графика плана работ в виде календарика
• Новые приемы программирования - OLAP в ASA 9
• Некоторые аспекты по использованию хранимых процедур в запросах

• Вышло обновление ASA 9.01 до версии 1841 для платформы Windows. Как говорят разработчики ASA в версии 9.01 были проблемы в оптимизаторе запросов, приводившие к тому, что в некоторых случаях выбирался не самый оптимальный план запроса. В версии 1841 все было исправлено.
• Были выпущены EBF 1293 под Юникс, Линукс и 64-разрядные платформы Windows.
• Один из разработчиков команды ASA, Брек Картер, написал книгу, посвященную разработке баз данных на Sybase ASA 9 - "Sybase Anywhere Studio 9. Developer Guide.". Книга будет выпущена в продажу 15 июня 2004 г. и, судя по индексу Amazon.com уже было заказано почти миллион экземпляров книги.
• Российское представительство Sybase подготовило документ по сравнению возможностей всех СУБД от Sybase: Sybase Anywhere Studio 9.01, Sybase Adaptive Enterprise Server 12.5.1 и Sybase IQ 12.5.0 . Ознакомиться с данным документом можно здесь.
• На сайте SQL.RU были открыт раздел FAQ для продуктов на платформе Sybase. Поддерживается механизм добавления информации, так что любой желающий сможет добавить в FAQ собственные материалы, советы и примеры кода. Надеюсь, это поможет создать в русскоязычном интеренете собственную библиотеку материалов и советов по продуктам Sybase.

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

Итак, наша задача вернуть клиентской части с хранимой процедуры набор данных в виде таблицы, где дни недели были бы полями, а каждая неделя месяца записью в таком наборе. Для начала я создал таблицу WeekNum (см. скрипт создания таблицы WeekNum по рассылке 10), в которой будут храниться возможные номера недель. Максимально в месяце может быть до шести недель, т.е. четыре полные и две неполные недели. Поэтому в эту таблицу я занес 6 номеров недель.

Далее я создал саму процедуру возврата календаря плана работ sp_Graph_Calendare. Что в ней происходит:

1. Вызывается процедура расчета рабочих дней указанного графика.
2. Определяется временная таблица, которая заполняется днями указанного месяца и для каждого дня проставляется номер дня недели, номер недели и флаг состояния рабочего дня и праздника.
3. На основе данных временной таблицы выполняется запрос, возвращающий данные в виде календарика.

При получении номера дня недели и номера недели используется системная функция DATEPART. Эта функция позволяет для даты получить нужную часть. В BOL описаны возможные варианты ее использования, однако у функции есть некоторые нюансы при использовании в наших региональных настройках. В США первым днем недели считается воскресенье. Поэтому использование в DATEPART префиксов номера недели "Week" и номера дня недели "WeekDay" будет возвращать неправильные с нашей точки зрения результаты. Вместо них необходимо для получения номера недели необходимо использовать "CalWeekofYear" и для номера дня недели "CalDayofWeek". Эти префиксы будут гарантировать, что функция DATEPART в работе будет использовать региональные настройки ОС. Действие префикса WeekDay для функции DATEPART зависит от значения существующей в ASA опций First_Day_of_Week, указывающей первый день недели (в девятом номере рассылки мы ее уже рассматривали). Однако эта опция никак не влияет на получение номера недели (Week), т.е. номер недели будет рассчитываться исходя из того, что воскресенье является первым днем недели.
Обратите внимание, как получается номер недели - так как функция DATEPART возвращает на указанную дату номер недели с начала года, то номер недели в месяце нужно рассчитать по следующей формуле:

DATEPART(cwk, [ИсходнаяДата]) - DATEPART(cwk, [НачалоМесяцаИсходнойДаты]) + 1

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

  SELECT w.Week,
         d1.Day AS d1_Day, convert(bit, IsNull(d1.IsHoliday, 0)) AS d1_IsHoliday, 
                           convert(bit, IsNull(d1.IsWork, 0)) AS d1_IsWork,
                           
         d2.Day AS d2_Day, convert(bit, IsNull(d2.IsHoliday, 0)) AS d2_IsHoliday, 
                           convert(bit, IsNull(d2.IsWork, 0)) AS d2_IsWork,
                           
         d3.Day AS d3_Day, convert(bit, IsNull(d3.IsHoliday, 0)) AS d3_IsHoliday, 
                           convert(bit, IsNull(d3.IsWork, 0)) AS d3_IsWork,
                           
         d4.Day AS d4_Day, convert(bit, IsNull(d4.IsHoliday, 0)) AS d4_IsHoliday, 
                           convert(bit, IsNull(d4.IsWork, 0)) AS d4_IsWork,
                           
         d5.Day AS d5_Day, convert(bit, IsNull(d5.IsHoliday, 0)) AS d5_IsHoliday, 
                           convert(bit, IsNull(d5.IsWork, 0)) AS d5_IsWork,
                           
         d6.Day AS d6_Day, convert(bit, IsNull(d6.IsHoliday, 0)) AS d6_IsHoliday, 
                           convert(bit, IsNull(d6.IsWork, 0)) AS d6_IsWork,
                           
         d7.Day AS d7_Day, convert(bit, IsNull(d7.IsHoliday, 0)) AS d7_IsHoliday, 
                           convert(bit, IsNull(d7.IsWork, 0)) AS d7_IsWork
  FROM WeekNum w
    LEFT JOIN ( // Понедельники всех недель месяца
      SELECT Week, Day, IsHoliday, IsWork
      FROM #D
      WHERE WeekDay = 1
    ) AS d1 ON d1.Week = w.Week
    LEFT JOIN ( // Вторники всех недель месяца
      SELECT Week, Day, IsHoliday, IsWork
      FROM #D
      WHERE WeekDay = 2
    ) AS d2 ON d2.Week = w.Week
    LEFT JOIN ( // Среды всех недель месяца
      SELECT Week, Day, IsHoliday, IsWork
      FROM #D
      WHERE WeekDay = 3
    ) AS d3 ON d3.Week = w.Week
    LEFT JOIN ( // Четверги всех недель месяца
      SELECT Week, Day, IsHoliday, IsWork
      FROM #D
      WHERE WeekDay = 4
    ) AS d4 ON d4.Week = w.Week
    LEFT JOIN ( // Пятницы всех недель месяца
      SELECT Week, Day, IsHoliday, IsWork
      FROM #D
      WHERE WeekDay = 5
    ) AS d5 ON d5.Week = w.Week
    LEFT JOIN ( // Субботы всех недель месяца
      SELECT Week, Day, IsHoliday, IsWork
      FROM #D
      WHERE WeekDay = 6
    ) AS d6 ON d6.Week = w.Week
    LEFT JOIN ( // Воскресенья всех недель месяца
      SELECT Week, Day, IsHoliday, IsWork
      FROM #D
      WHERE WeekDay = 7
    ) AS d7 ON d7.Week = w.Week
  WHERE w.Week <= DatePart(cwk, @CalcDateLast) - DatePart(cwk, @CalcDate) + 1
  ORDER BY w.Week;

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

Начиная с версии 9.01, в ASA появилась поддержка OLAP функций. Было не только реализовано множество функций, но и добавлены новые алгоритмы в оптимизатор запросов, для наиболее эффективного выполнения аналитических запросов. Так же была расширена семантика WatcomSQL для поддержки Window-функций. Эти функции позволяют для каждой строки в запросе вычислить агрегированное значение для описанного ранга записей. В каком то плане эти возможности похожи на принципы работы формул Excel. В BOL приведен пример расчета нарастающей суммы для каждого отделения (база ASADEMO):

SELECT dept_id, emp_lname, start_date, salary,
       SUM(salary) OVER (PARTITION BY dept_id
                         ORDER BY start_date
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Sum_Salary"
FROM employee
ORDER BY dept_id, start_date;

Ключевое слово OVER указывает, что функция SUM будет считать ставку в пределах указанного ранга, который будет определяться (PARTITION BY) для каждого отделения с начала ранга по текущую запись (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). В итоге мы получим для каждого отделения на каждую дату не только сумму за эту дату, но и итоговую сумму все продаж с первой по текущую дату.

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

Начиная с версии 9.01, разработчики ASA доработали оптимизатор запросов для более эффективного построения плана запросов в ситуациях, когда используются хранимые процедуры. Если в теле хранимой процедуры стоит только оператор SELECT, то оптимизатор при включении такой процедуры в запрос включает ее SELECT в план запроса и может оптимизировать его выполнение. В качестве примера я организовал в базе данных процедуру для возврата цен продукции на конец указанного месяца "sp_ProductValue_Last_NotOpt".

CREATE PROCEDURE "DBA"."sp_ProductValue_Last_NotOpt" (
  IN @CalcDate date
)
BEGIN
  SET @CalcDate = fn_CDate(@CalcDate, 1);

  SELECT p.Product_id, p.BeginDate, p.Value
  FROM ProductValue p
    INNER JOIN (
      SELECT Product_id, Max(BeginDate) AS LastBeginDate
      FROM ProductValue
      WHERE BeginDate <= @CalcDate
      GROUP BY Product_id
    ) AS x ON x.Product_id = p.Product_id AND
              x.LastBeginDate = p.BeginDate;
END;

Как видите, с точки зрения оптимизации запросов я поступил правильно, определив результат выполнения функции в переменную и уже ее использовав в запросе процедуры (основным советом оптимизации запросов в большинстве СУБД как раз является рекомендация не использовать для фильтрации данных пользовательские функции). Давайте, используем процедуру в запросе и посмотрим на текстовый план запроса:

SELECT n.Product_id, n.Name, v.BeginDate, v.Value
FROM sp_ProductValue_Last_NotOpt (@@CalcDate) v
  INNER JOIN Product n ON n.Product_id = v.Product_id
WHERE n.ProductGroup_id = 'Хлеб'
ORDER BY n.Name;

( Plan [ Total Cost Estimate: .0032523684 ] 
  ( WorkTable
    ( Sort
      ( NestedLoopsJoin[ TRUE ]
        ( IndexScan ( Product n ) ProductGroup )
        ( ProcCall sp_ProductValue_Last_NotOpt )
      )
    )
  )
)

Из плана видно, что оптимизатор сначала вызывает процедуру, и результат ее выполнения использует для соединения с таблицей Product.
Перепишем процедуру так, чтобы в ней был только один оператор SELECT:

CREATE PROCEDURE "DBA"."sp_ProductValue_Last_Opt" (
  IN @CalcDate date
)
BEGIN
  SELECT p.Product_id, p.BeginDate, p.Value
  FROM ProductValue p
    INNER JOIN (
      SELECT Product_id, Max(BeginDate) AS LastBeginDate
      FROM ProductValue
      WHERE BeginDate <= fn_CDate(@CalcDate, 1)
      GROUP BY Product_id
    ) AS x ON x.Product_id = p.Product_id AND
              x.LastBeginDate = p.BeginDate;
END

и выполним аналогичный запрос:

SELECT n.Product_id, n.Name, v.BeginDate, v.Value
FROM sp_ProductValue_Last_Opt (@@CalcDate) v
  INNER JOIN Product n ON n.Product_id = v.Product_id
WHERE n.ProductGroup_id = 'Хлеб'
ORDER BY n.Name;

( Plan [ Total Cost Estimate: .0001182812 ] 
  ( WorkTable
    ( Sort
      ( NestedLoopsJoin[ TRUE ]
        ( NestedLoopsJoin[ TRUE ]
          ( IndexScan ( Product n ) ProductGroup )
          ( IndexScan ( ProductValue p ) Product )
        )
        ( Filter [ ( p.Product_id = ProductValue.Product_id : 10% COLUMN ) AND 
                   ( p.BeginDate = expr() : 10% COLUMN ) ]
          ( OrderedGroupBy
            ( IndexScan ProductValue Product[ 
                ProductValue.BeginDate <= "DBA"."fn_CDate"(2004-01-01,1) : 25% Guess ] )
          )
        )
      )
    )
  )
)

В плане запроса видно, что вместо выполнения хранимой процедуры ASA соединила ее план запроса с основным и смогла оптимизировать его по нужным индексам. Фактически получилось параметризированное представление или аналог INLINE UDF функций в MSSQL 2000. Обратите внимание, что в версии 9.0.1 оптимизатор способен обрабатывать статические пользовательские функции, участвующие в фильтре запроса. Не смотря на то, что мы перенесли функцию fn_CDate() в секцию WHERE запроса, оптимизатор воспользовался индексом на таблицу ProductValue, так как выполнение функции статично, т.е. она возвращает на протяжении запроса один и тот же результат.

Итак, давайте подведем итоги по рекомендациям оптимизации процедур, участвующих в запросах:

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

До встречи в следующей рассылке, с уважением, ASCRUS.