В одной шутке говорилось, что есть только два способа оптимизации запросов: 1. добавить индексы 2. сменить СУБД. Посмотрим, что можно сделать в случае Intersystems Caché. В качестве примера возьмем результаты прошедшего конкурса SQL Performance Challenge. При подведении итогов, решения тройки лучших были представлены в виде планов запросов с краткими комментариями.
По условиям конкурса, для заданной схемы базы данных надо было оптимизировать 10 запросов.
Исходный код основных классов. Для наглядности убраны методы генерации тестовых данных
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;
}
В конкурсный комплект также входит специальный класс 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 индекс по нему же