Advantage Database Server: slow stored procedure performance.
- by ie
I have a question about a performance of stored procedures in the ADS. I created a simple database with the following structure:
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;
Actually, the problem I had - even so light stored procedures work very-very slow (about 50-150 ms) relatively to plain queries (0-5ms). To test the performance, I created a simple test (in F# using ADS ADO.NET provider):
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();
And I'm getting the following results:
Test 'Script -Insert' - total: 00:00:00.0292841; per call: 2ms
Test 'Script -Update' - total: 00:00:00.0056296; per call: 0ms
Test 'Script -Select' - total: 00:00:00.0051738; per call: 0ms
Test 'Script -Delete' - total: 00:00:00.0059258; per call: 0ms
Test 'Procedure-Insert' - total: 00:00:01.2567146; per call: 125ms
Test 'Procedure-Update' - total: 00:00:00.7442440; per call: 74ms
Test 'Procedure-Select' - total: 00:00:00.5120446; per call: 51ms
Test 'Procedure-Delete' - total: 00:00:01.0619165; per call: 106ms
The situation with the remote server is much better, but still a great gap between plaqin queries and stored procedures:
Test 'Script -Insert' - total: 00:00:00.0709299; per call: 7ms
Test 'Script -Update' - total: 00:00:00.0161777; per call: 1ms
Test 'Script -Select' - total: 00:00:00.0258113; per call: 2ms
Test 'Script -Delete' - total: 00:00:00.0166242; per call: 1ms
Test 'Procedure-Insert' - total: 00:00:00.5116138; per call: 51ms
Test 'Procedure-Update' - total: 00:00:00.3802251; per call: 38ms
Test 'Procedure-Select' - total: 00:00:00.1241245; per call: 12ms
Test 'Procedure-Delete' - total: 00:00:00.4336334; per call: 43ms
Is it any chance to improve the SP performance? Please advice.
ADO.NET driver version - 9.10.2.9
Server version - 9.10.0.9 (ANSI - GERMAN, OEM - GERMAN)
Thanks!
