У меня есть вопрос о производительности хранимых процедур в ADS. Я создал простую базу данных со следующей структурой:
CREATE TABLE MainTable
(
Id INTEGER PRIMARY KEY,
Name VARCHAR(50),
Value INTEGER
);
CREATE UNIQUE INDEX MainTableName_UIX ON MainTable ( Name );
CREATE TABLE SubTable
(
Id INTEGER PRIMARY KEY,
MainId INTEGER,
Name VARCHAR(50),
Value INTEGER
);
CREATE INDEX SubTableMainId_UIX ON SubTable ( MainId );
CREATE UNIQUE INDEX SubTableName_UIX ON SubTable ( Name );
CREATE PROCEDURE CreateItems
(
MainName VARCHAR ( 20 ),
SubName VARCHAR ( 20 ),
MainValue INTEGER,
SubValue INTEGER,
MainId INTEGER OUTPUT,
SubId INTEGER OUTPUT
)
BEGIN
DECLARE @MainName VARCHAR ( 20 );
DECLARE @SubName VARCHAR ( 20 );
DECLARE @MainValue INTEGER;
DECLARE @SubValue INTEGER;
DECLARE @MainId INTEGER;
DECLARE @SubId INTEGER;
@MainName = (SELECT MainName FROM __input);
@SubName = (SELECT SubName FROM __input);
@MainValue = (SELECT MainValue FROM __input);
@SubValue = (SELECT SubValue FROM __input);
@MainId = (SELECT MAX(Id)+1 FROM MainTable);
@SubId = (SELECT MAX(Id)+1 FROM SubTable );
INSERT INTO MainTable (Id, Name, Value) VALUES (@MainId, @MainName, @MainValue);
INSERT INTO SubTable (Id, Name, MainId, Value) VALUES (@SubId, @SubName, @MainId, @SubValue);
INSERT INTO __output SELECT @MainId, @SubId FROM system.iota;
END;
CREATE PROCEDURE UpdateItems
(
MainName VARCHAR ( 20 ),
MainValue INTEGER,
SubValue INTEGER
)
BEGIN
DECLARE @MainName VARCHAR ( 20 );
DECLARE @MainValue INTEGER;
DECLARE @SubValue INTEGER;
DECLARE @MainId INTEGER;
@MainName = (SELECT MainName FROM __input);
@MainValue = (SELECT MainValue FROM __input);
@SubValue = (SELECT SubValue FROM __input);
@MainId = (SELECT TOP 1 Id FROM MainTable WHERE Name = @MainName);
UPDATE MainTable SET Value = @MainValue WHERE Id = @MainId;
UPDATE SubTable SET Value = @SubValue WHERE MainId = @MainId;
END;
CREATE PROCEDURE SelectItems
(
MainName VARCHAR ( 20 ),
CalculatedValue INTEGER OUTPUT
)
BEGIN
DECLARE @MainName VARCHAR ( 20 );
@MainName = (SELECT MainName FROM __input);
INSERT INTO __output SELECT m.Value * s.Value FROM MainTable m INNER JOIN SubTable s ON m.Id = s.MainId WHERE m.Name = @MainName;
END;
CREATE PROCEDURE DeleteItems
(
MainName VARCHAR ( 20 )
)
BEGIN
DECLARE @MainName VARCHAR ( 20 );
DECLARE @MainId INTEGER;
@MainName = (SELECT MainName FROM __input);
@MainId = (SELECT TOP 1 Id FROM MainTable WHERE Name = @MainName);
DELETE FROM SubTable WHERE MainId = @MainId;
DELETE FROM MainTable WHERE Id = @MainId;
END;
На самом деле проблема я имел - несмотря на это, легкая работа хранимых процедур, очень очень медленная (приблизительно 50-150 мс) относительно к простым (0-5ms) запросам. Для проверения производительности я создал простой тест (в F# с помощью ADS поставщик ADO.NET):
open System;
open System.Data;
open System.Diagnostics;
open Advantage.Data.Provider;
let mainName = "main name #";
let subName = "sub name #";
// INSERT
let cmdTextScriptInsert = "
DECLARE @MainId INTEGER;
DECLARE @SubId INTEGER;
@MainId = (SELECT MAX(Id)+1 FROM MainTable);
@SubId = (SELECT MAX(Id)+1 FROM SubTable );
INSERT INTO MainTable (Id, Name, Value) VALUES (@MainId, :MainName, :MainValue);
INSERT INTO SubTable (Id, Name, MainId, Value) VALUES (@SubId, :SubName, @MainId, :SubValue);
SELECT @MainId, @SubId FROM system.iota;";
let cmdTextProcedureInsert = "CreateItems";
// UPDATE
let cmdTextScriptUpdate = "
DECLARE @MainId INTEGER;
@MainId = (SELECT TOP 1 Id FROM MainTable WHERE Name = :MainName);
UPDATE MainTable SET Value = :MainValue WHERE Id = @MainId;
UPDATE SubTable SET Value = :SubValue WHERE MainId = @MainId;";
let cmdTextProcedureUpdate = "UpdateItems";
// SELECT
let cmdTextScriptSelect = "
SELECT m.Value * s.Value FROM MainTable m INNER JOIN SubTable s ON m.Id = s.MainId WHERE m.Name = :MainName;";
let cmdTextProcedureSelect = "SelectItems";
// DELETE
let cmdTextScriptDelete = "
DECLARE @MainId INTEGER;
@MainId = (SELECT TOP 1 Id FROM MainTable WHERE Name = :MainName);
DELETE FROM SubTable WHERE MainId = @MainId;
DELETE FROM MainTable WHERE Id = @MainId;";
let cmdTextProcedureDelete = "DeleteItems";
let cnnStr = @"data source=D:\DB\test.add; ServerType=local; user id=adssys; password=***;";
let cnn = new AdsConnection(cnnStr);
try
cnn.Open();
let cmd = cnn.CreateCommand();
let parametrize ix prms =
cmd.Parameters.Clear();
let addParam = function
| "MainName" -> cmd.Parameters.Add(":MainName" , mainName + ix.ToString()) |> ignore;
| "SubName" -> cmd.Parameters.Add(":SubName" , subName + ix.ToString() ) |> ignore;
| "MainValue" -> cmd.Parameters.Add(":MainValue", ix * 3 ) |> ignore;
| "SubValue" -> cmd.Parameters.Add(":SubValue" , ix * 7 ) |> ignore;
| _ -> ()
prms |> List.iter addParam;
let runTest testData =
let (cmdType, cmdName, cmdText, cmdParams) = testData;
let toPrefix cmdType cmdName =
let prefix = match cmdType with
| CommandType.StoredProcedure -> "Procedure-"
| CommandType.Text -> "Script -"
| _ -> "Unknown -"
in prefix + cmdName;
let stopWatch = new Stopwatch();
let runStep ix prms =
parametrize ix prms;
stopWatch.Start();
cmd.ExecuteNonQuery() |> ignore;
stopWatch.Stop();
cmd.CommandText <- cmdText;
cmd.CommandType <- cmdType;
let startId = 1500;
let count = 10;
for id in startId .. startId+count do
runStep id cmdParams;
let elapsed = stopWatch.Elapsed;
Console.WriteLine("Test '{0}' - total: {1}; per call: {2}ms", toPrefix cmdType cmdName, elapsed, Convert.ToInt32(elapsed.TotalMilliseconds)/count);
let lst = [
(CommandType.Text, "Insert", cmdTextScriptInsert, ["MainName"; "SubName"; "MainValue"; "SubValue"]);
(CommandType.Text, "Update", cmdTextScriptUpdate, ["MainName"; "MainValue"; "SubValue"]);
(CommandType.Text, "Select", cmdTextScriptSelect, ["MainName"]);
(CommandType.Text, "Delete", cmdTextScriptDelete, ["MainName"])
(CommandType.StoredProcedure, "Insert", cmdTextProcedureInsert, ["MainName"; "SubName"; "MainValue"; "SubValue"]);
(CommandType.StoredProcedure, "Update", cmdTextProcedureUpdate, ["MainName"; "MainValue"; "SubValue"]);
(CommandType.StoredProcedure, "Select", cmdTextProcedureSelect, ["MainName"]);
(CommandType.StoredProcedure, "Delete", cmdTextProcedureDelete, ["MainName"])];
lst |> List.iter runTest;
finally
cnn.Close();
И я получаю следующие результаты:
Тест 'Сценарий - Вставляет' - общее количество: 0:00:00.0292841; на вызов: 2 мс
Тест 'Сценарий - Обновление' - общее количество: 0:00:00.0056296; на вызов: 0ms
Тест 'Сценарий - Выбор' - общее количество: 0:00:00.0051738; на вызов: 0ms
Тест 'Сценарий - Удаляет' - общее количество: 0:00:00.0059258; на вызов: 0ms
Тест 'Вставляет Процедура' - общее количество: 0:00:01.2567146; на вызов: 125 мс
Тест 'Обновление Процедуры' - общее количество: 0:00:00.7442440; на вызов: 74 мс
Тест 'Выбор Процедуры' - общее количество: 0:00:00.5120446; на вызов: 51 мс
Тест 'Процедура - Удаляет' - общее количество: 0:00:01.0619165; на вызов: 106 мс
Ситуация с удаленным сервером намного лучше, но все еще большой разрыв между запросами plaqin и хранимыми процедурами:
Тест 'Сценарий - Вставляет' - общее количество: 0:00:00.0709299; на вызов: 7 мс
Тест 'Сценарий - Обновление' - общее количество: 0:00:00.0161777; на вызов: 1 мс
Тест 'Сценарий - Выбор' - общее количество: 0:00:00.0258113; на вызов: 2 мс
Тест 'Сценарий - Удаляет' - общее количество: 0:00:00.0166242; на вызов: 1 мс
Тест 'Вставляет Процедура' - общее количество: 0:00:00.5116138; на вызов: 51 мс
Тест 'Обновление Процедуры' - общее количество: 0:00:00.3802251; на вызов: 38 мс
Тест 'Выбор Процедуры' - общее количество: 0:00:00.1241245; на вызов: 12 мс
Тест 'Процедура - Удаляет' - общее количество: 0:00:00.4336334; на вызов: 43 мс
Это - шанс улучшить производительность SP? Совет.
Версия драйвера ADO.NET - 9.10.2.9
Серверная версия - 9.10.0.9 (ANSI - НЕМЕЦКИЙ ЯЗЫК, OEM - НЕМЕЦКИЙ ЯЗЫК)
Спасибо!
Бета-версия Advantage v10 включает множество улучшений производительности, направленных непосредственно на производительность хранимых процедур. Однако вот некоторые моменты, которые следует учитывать в текущей версии:
В вашей процедуре CreateItems, вероятно, будет более эффективно заменить
@MainName = (SELECT MainName FROM __input);
@SubName = (SELECT SubName FROM __input);
@MainValue = (SELECT MainValue FROM __input);
@SubValue = (SELECT SubValue FROM __input);
использование одного курсора для получения всех параметров:
DECLARE input CURSOR;
OPEN input as SELECT * from __input;
FETCH input;
@MainName = input.MainName;
@SubName = input.SubName;
@MainValue = input.MainValue;
@SubValue = input.SubValue;
CLOSE input;
Это позволит избежать 3 операций разбора/семантики/оптимизации/выполнения только для получения входных параметров (я знаю, нам действительно нужно полностью отказаться от таблицы __input).
Процедура SelectItems редко когда будет такой же быстрой, как select с клиента, особенно в этом случае, когда она не делает ничего, кроме абстрагирования значения параметра (что можно легко сделать на клиенте). Помните, что поскольку это JOIN, SELECT для заполнения таблицы __output будет статическим курсором (то есть внутренним временным файлом, который сервер должен создать и заполнить), но теперь в дополнение к этому у вас есть таблица __output, которая является еще одним временным файлом для сервера, плюс у вас есть дополнительные накладные расходы на заполнение этой таблицы __output данными, которые уже были помещены в временную таблицу статического курсора, просто ради дублирования (сервер мог бы сделать лучшую работу по обнаружению этого и замене __output существующей ссылкой на статический курсор, но сейчас он этого не делает).
Я постараюсь найти время, чтобы попробовать ваши процедуры на версии 10. Если у вас есть тестовые таблицы, которые вы использовали при тестировании, не стесняйтесь застегнуть их и отправить по адресу Advantage@iAnywhere.com, указав в теме письма attn:JD.
Есть одно изменение, которое поможет с процедурой CreateItems
. Измените следующие два оператора:
@MainId = (SELECT MAX(Id)+1 FROM MainTable);
@SubId = (SELECT MAX(Id)+1 FROM SubTable );
На это:
@MainId = (SELECT MAX(Id) FROM MainTable);
@MainId = @MainId + 1;
@SubId = (SELECT MAX(Id) FROM SubTable );
@SubId = @SubId + 1;
Я просмотрел информацию о плане запроса (в Advantage Data Architect) для первой версии этого оператора. Похоже, оптимизатор не разбивает этот MAX (id) +1
на составляющие. Оператор select max (id) from maintable
может быть оптимизирован с использованием индекса в поле ID. Похоже, что max (id) +1
не оптимизирован. Так что внесение этого изменения будет довольно значительным, особенно по мере роста таблицы.
Еще одна вещь, которая может помочь, - это добавить оператор CACHE PREPARE ON;
в начало каждого скрипта. Это может помочь с определенными процедурами при их многократном выполнении.
Править Сегодня была выпущена бета-версия Advantage v10 . Итак, я выполнил вашу процедуру CreateItems
как с v9.1, так и с новой бета-версией. Я выполнил 1000 итераций на удаленном сервере. Разница в скорости была значительной:
v9.1: 101 seconds
v10 beta: 2.2 seconds
Обратите внимание, что я запускал версию с описанным выше изменением select max (id)
. Это тестирование проводилось на моем довольно старом компьютере разработки.