Оптимизация запросов в Intersystems Caché

Опубликовано автором

В одной шутке говорилось, что есть только два способа оптимизации запросов: 1. добавить индексы 2. сменить СУБД. Посмотрим, что можно сделать в случае Intersystems Caché. В качестве примера возьмем результаты прошедшего конкурса SQL Performance Challenge. При подведении итогов, решения тройки лучших были представлены в виде планов запросов с краткими комментариями.

По условиям конкурса, для заданной схемы базы данных надо было оптимизировать 10 запросов.

DataDiagram

Исходный код основных классов. Для наглядности убраны методы генерации тестовых данных

Person:
Class DatesRUs.Person Extends (%Persistent, %Populate) {

/// Person’s name.
Property Name As %String(POPSPEC = ".Name()");
Index NameIndex On Name;

/// Person’s Date of Birth.
Property DOB As %Date(POPSPEC = "Date()");

/// Person’s home address. This uses an embedded object.
Property Home As Address;

/// Person’s office address. This uses an embedded object.
Property Office As Address;

Property Gender As %String(DISPLAYLIST = ",Female,Male,", VALUELIST = ",F,M,");

Relationship Profiles As DatesRUs.Profile [ Cardinality = many, Inverse = CustomerPointer ];

}

Address
Class DatesRUs.Address Extends (%SerialObject, %Populate) {

/// The street address.
Property Street As %String(MAXLEN = 80, POPSPEC = "Street()");

/// The city name.
Property City As %String(MAXLEN = 80, POPSPEC = "City()");

/// The 2-letter state abbreviation.
Property State As %String(MAXLEN = 2, POPSPEC = "USState()");

/// The 5-digit U.S. Zone Improvement Plan (ZIP) code.
Property Zip As %String(MAXLEN = 5, POPSPEC = "USZip()");

}

Profile
Class DatesRUs.Profile Extends (%Persistent, %Populate) {

Property Hair As %String(DISPLAYLIST = ",Black,Blonde,Brown,Red,", VALUELIST = ",Bk,Be,Bn,R,");

Property Eye As %String(DISPLAYLIST = ",Brown,Blue,Green,Red,Yellow,", VALUELIST = ",Br,BL,G,R,Y,");

Property Gender As %String(DISPLAYLIST = ",Female,Male,", VALUELIST = ",F,M,");
Index GenderIndex On Gender;

Property Hobbies As list Of %String(DISPLAYLIST = ",Jogging,Hiking,Reading,Skiing,Music,Video Games", VALUELIST = ",J,H,R,S,M,V,");

Property Description As %String(MAXLEN = 32000) [ InitialExpression = {##class(DatesRUs.Profile).Description()} ];

Relationship CustomerPointer As DatesRUs.Person [ Cardinality = one, Inverse = Profiles ];
Index CustomerPointerIndex On CustomerPointer;

Relationship Datehistory As DatesRUs.DateHistory [ Cardinality = children, Inverse = ProfilePointer ];

Property Active As %Boolean;
Index ActiveIndex On Active;

}

Employee
Class DatesRUs.Employee Extends (DatesRUs.Person, %Populate){

Property Username As %String;

Property Password As %String(COLLATION = "EXACT");

Property Active As %Boolean;

Property EmployeeID As %Integer [ InitialExpression = {$I(^DatesRUs.EmployeeC)} ];

Index EmpIDIndex On EmployeeID [ Unique ];

}

Customers
Class DatesRUs.Customers Extends (DatesRUs.Person, %Populate){

Property CustomerId As %Integer [ InitialExpression = {$I(^DatesRUs.CustomerC)} ];

Property Status As %String(DISPLAYLIST = ",Available, Dating, Married, Closed,", VALUELIST = ",A,D,M,C,");

Property SalesRep As DatesRUs.Employee;

Relationship Dates As DatesRUs.DateHistory [ Cardinality = many, Inverse = DateWith ];

Relationship SurveyByMe As DatesRUs.Survey [ Cardinality = many, Inverse = SurveyBy ];

Relationship SurveyOfMe As DatesRUs.Survey [ Cardinality = many, Inverse = SurveyOf ];

Property Photo As %Stream.GlobalBinary;

}

DateHistory
Class DatesRUs.DateHistory Extends (%Persistent, %Populate) {

Property DateofDate As %Date(MAXVAL = "03/18/2012", MINVAL = "01/01/2010");

Relationship DateWith As DatesRUs.Customers [ Cardinality = one, Inverse = Dates ];

Index DateWithIndex On DateWith;

Relationship ProfilePointer As DatesRUs.Profile [ Cardinality = parent, Inverse = Datehistory ];

}

Survey
Class DatesRUs.Survey Extends (%Persistent, %Populate) {

Property Rating As %Integer(MAXVAL = 10, MINVAL = 1);

Property Comments As %String(MAXLEN = 32000) [ InitialExpression = {##class(DatesRUs.Survey).Comment()} ];

Relationship SurveyBy As DatesRUs.Customers [ Cardinality = one, Inverse = SurveyByMe ];

Index SurveyByIndex On SurveyBy;

Relationship SurveyOf As DatesRUs.Customers [ Cardinality = one, Inverse = SurveyOfMe ];

//Index SurveyOfIndex On SurveyOf;

}

SQL проекция:
ER-Diagram

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

1. Вставка 5000 строк в Customers
ClassMethod Method1() As %Integer {
    for i=1:1:5000 { 
        set zip = "02134"   
        if (i#100=0) set zip = "02762"
        set Rep = 113113
        if ( i#2 = 0 ) set Rep = 113114
        if ( i#3 = 0 ) set Rep = 113115
        
        &sql( 
            INSERT INTO DatesRUs.Customers 
            (Name, Gender, Status, DOB, Home_Zip, SalesRep)
            VALUES (‘Test,Test’,‘M’,‘C’,+$H,:zip,:Rep)
        )
    }
    Quit i
}

В таблицу Customers с помощью встроенного запроса вставляется пять тысяч строк.
Судя по комментарию , оптимизация была достигнута за счет использования минимального количества индексов. Широко известная дилемма: много индексов – медленная вставка (удаление), мало индексов – медленная выборка

2. Выборка из Customers и вложенная выборка из Survey
    set Statement1 = ##class(%SQL.Statement).%New()
    set sql="SELECT Id, Name FROM DatesRUs.Customers WHERE Status = ‘A’"
    do Statement1.prepare()
    set Result1 = Statement1.%Execute()
    while Result1.%Next() {
        s id=Result1.%GetData(1), name=Result1.%GetData(2)
        
        set Statement2 = ##class(%SQL.Statement).%New()
        set sql="SELECT avg(Rating) FROM DatesRUs.Survey WHERE SurveyOf = ?"
        do Statement2.prepare()
        set Result2 = Statement2.%Execute(id)
        while Result2.%Next() {
            s rate=Result2.%GetData(1)
        }
        
    }

Решение:

  • добавление bitmap индекса по свойству Status в таблицу Customers
  • добавление bitmap индекса по свойству SurveyOf в таблице Survey
  • замена типа запроса с динамического на встроенный

3.Выборка первых десяти записей из Customers по свойству унаследованному от родительского класса Person
    set Statement1 = ##class(%SQL.Statement).%New()
    set sql="SELECT TOP 10 Id, Name FROM DatesRUs.Customers WHERE Home_Zip = ‘02762’ "
    do Statement1.prepare( sql )
    set Result1 = Statement1.%Execute()
    while Result1.%Next() {
       s id=Result1.%GetData(1), name=Result1.%GetData(2)
    }

Решение:

  • В Customers добавлен bitmap индекс по свойству Home_Zip
  • Замена типа запроса с динамического на встроенный

4.Выборка из объединения таблиц Employee и Customers
        set Statement1 = ##class(%SQL.Statement).%New()
        set sql="SELECT C.id, C.Name FROM DatesRUs.Employee E "
                _" JOIN DatesRUs.Customers C ON E.ID = C.SalesRep "
                _" WHERE EmployeeID = 113"
        do Statement1.prepare(sql)
        set Result1 = Statement1.%Execute()
        while Result1.%Next() {
                s id=Result1.%GetData(1), name=Result1.%GetData(2)
        }

Решение:

  • Запрос разбит на два отдельных запроса к таблицам Customers и Employee
  • В Customers добавлен bitmap индекс по свойству SalesRep
  • Замена типа запроса с динамического на встроенный

5.Выборка из соединения таблиц Customers и Profile по значениям нескольких свойств из Profile
         set Statement1 = ##class(%SQL.Statement).%New()
        set sql="SELECT C.ID, C.Name, P.description "
            _" FROM DatesRUs.Customers C JOIN DatesRUs.Profile P ON C.ID = P.CustomerPointer "
            _" WHERE P.Active = 1 AND P.Eye = ‘BL’ AND P.Hair = ‘Be’"
        do Statement1.prepare(sql)
        set Result1 = Statement1.%Execute()
        while Result1.%Next() {
                s id=Result1.%GetData(1), name=Result1.%GetData(2)
        }

Решение:

  • условие JOIN вынесено в WHERE:

         &sql(SELECT C.ID, C.Name, P.description into :id1,:name1,:desc1
               FROM DatesRUs.Customers C,DatesRUs.Profile P 
               WHERE C.ID = P.CustomerPointer 
               And P.Active = 1 AND P.Eye = ‘BL’ AND P.Hair = ‘Be’
        )

  • В Profile добавлен bitmap индекс по свойствам Active,Eye,Hair
  • Замена типа запроса на с динамического на встроенный

6.Выборка из соединения таблиц Customers и Profile по значениям одного свойства из Profile
    set Statement1 = ##class(%SQL.Statement).%New()
    s sql="SELECT C.ID, C.Name, P.description "
          _" FROM DatesRUs.Customers C JOIN DatesRUs.Profile P ON C.ID = P.CustomerPointer "
          _" WHERE P.Active = 1"
    do Statement1.prepare( sql )
    set Result1 = Statement1.%Execute()
    while Result1.%Next() {
          s id=Result1.%GetData(1), name=Result1.%GetData(2)
    }

Решение:
Запрос почти полностью аналогичен запросу №5, поэтому способы оптимизации такие же

  • Вынос условия JOIN в WHERE
  • Замена типа запроса с динамического на встроенный
  • Замена типа индекса по свойству Profile.Active с обычного на bitmap

7.Выборка из соединения таблиц DateHistory и Profile по свойству Profile.Active
    set Statement1 = ##class(%SQL.Statement).%New()
     s sql="SELECT C.ID, C.Name, P.description "
    _" FROM DatesRUs.Customers C JOIN DatesRUs.Profile P ON C.ID = P.CustomerPointer "
    _" WHERE P.Active = 1"
    do Statement1.prepare( sql )
    set Result1 = Statement1.%Execute()
    while Result1.%Next() {
          s id=Result1.%GetData(1), name=Result1.%GetData(2)
    }

Решение:

  • Как и в случае с запросами №5 и №6 условие JOIN перенесено в WHERE
  • Тип запроса изменен с динамического на встроенный

8. Выборка из Employee по унаследованному свойству Name
     set Statement1 = ##class(%SQL.Statement).%New()
        set sql = "SELECT Name FROM DatesRUs.Employee "
        do Statement1.prepare( sql )
        set Result1 = Statement1.%Execute()
        while Result1.%Next() {
            s id=Result1.%GetData(1), name=Result1.%GetData(2)
        }

Решение:

  • Тип запроса изменен с динамического на встроенный

    • 9. Соединение таблиц Customers, Profile и Survey
        set Statement1 = ##class(%SQL.Statement).%New()
        set sql="SELECT C.Name,S.Rating "
                _" FROM DatesRUs.Customers C "
                _" JOIN DatesRUs.Profile P ON C.ID = P.CustomerPointer "
                _" LEFT OUTER JOIN DatesRUs.Survey S ON C.ID = S.SurveyOf "
                _" WHERE P.Hair=’R’ "
        do Statement1.prepare(sql)
        set Result1 = Statement1.%Execute()
        while Result1.%Next() {
            s id=Result1.%GetData(1), name=Result1.%GetData(2)
        }

    Решение:

    • В Profile добавлен обычный индекс по свойствам Active,Eye,Hair
    • В Survey добавлен bitmap индекс по свойству SurveyOf

    10.Удаление из Customers по унаследованному свойству
    &SQL(DELETE FROM DatesRUs.Customers WHERE Name = ‘Test,Test’)

    Решение:

    • Хотя в Person есть индекс по свойству Name, для Customers был построен отдельный bitmap индекс по нему же

    Добавить комментарий

    Ваш e-mail не будет опубликован. Обязательные поля помечены *