Advantage Database Server: slow stored procedure performance

Question

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!

Answer 1

The Advantage v10 beta includes a variety of performance improvements directly targeting stored procedure performance. Here are some things to consider with the current shipping version, however:

In your CreateItems procedure it would likely be more efficient to replace

@MainName  = (SELECT MainName  FROM __input);
@SubName   = (SELECT SubName   FROM __input);
@MainValue = (SELECT MainValue FROM __input);
@SubValue  = (SELECT SubValue  FROM __input);

with the use of a single cursor to retrieve all parameters:

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;

That will avoid 3 statement parse/semantic/optimize/execute operations just to retrieve the input parameters (I know, we really need to eliminate the __input table altogether).

The SelectItems procedure is rarely ever going to be as fast as a select from the client, especially in this case where it really isn't doing anything except abstracting a parameter value (which can easily be done on the client). Remember that because it is a JOIN, the SELECT to fill the __output table is going to be a static cursor (meaning an internal temporary file for the server to create and fill), but now in addition you have the __output table which is yet another temporary file for the server, plus you have additional overhead to populate this __output table with data that has already been place in the static cursor temp table, just for the sake of duplicating it (server could do a better job of detecting this and replacing __output with the existing static cursor reference, but it currently doesn't).

I will try to make some time to try your procedures on version 10. If you have the test tables you used in your testing feel free to zip them up and send them to [email protected] and put attn:JD in the subject.

Answer 2

There is one change that would help with the CreateItems procedure. Change the following two statements:

@MainId = (SELECT MAX(Id)+1 FROM MainTable);
@SubId  = (SELECT MAX(Id)+1 FROM SubTable );

To this:

@MainId = (SELECT MAX(Id) FROM MainTable);
@MainId = @MainId + 1;
@SubId  = (SELECT MAX(Id) FROM SubTable );
@SubId  = @SubId + 1;

I looked at the query plan information (in Advantage Data Architect) for the first version of that statement. It looks like the optimizer does not break that MAX(id)+1 into the component pieces. The statement select max(id) from maintable can be optimized using the index on the ID field. It appears that max(id)+1 is not optimized. So making that change would be fairly significant particularly as the table grows.

Another thing that might help is to add a CACHE PREPARE ON; statement to the top of each script. This can help with certain procedures when running them multiple times.

Edit The Advantage v10 beta was released today. So I ran your CreateItems procedure with both v9.1 and the new beta version. I ran 1000 iterations against the remote server. The speed difference was significant:

v9.1:      101 seconds
v10 beta:  2.2 seconds

Note that I ran a version with the select max(id) change I described above. This testing was on my fairly old development PC.