Advantage Database Server: langsame Leistung von gespeicherten Prozeduren

Question

Ich habe eine Frage zur Leistung von gespeicherten Prozeduren in der ADS. Ich habe eine einfache Datenbank mit der folgenden Struktur erstellt:

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;

Eigentlich war das Problem, das ich hatte - selbst so leichte Stored Procedures arbeiten sehr-sehr langsam (ca. 50-150 ms) im Vergleich zu einfachen Abfragen (0-5ms). Um die Leistung zu testen, habe ich einen einfachen Test erstellt (in F# mit ADS ADO.NET-Anbieter):

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();

Und ich erhalte die folgenden Ergebnisse:

Test 'Script -Insert' - insgesamt: 00:00:00.0292841; pro Aufruf: 2ms

Test 'Script -Update' - insgesamt: 00:00:00.0056296; pro Aufruf: 0ms

Test 'Script -Select' - insgesamt: 00:00:00.0051738; pro Aufruf: 0ms

Test 'Script -Delete' - insgesamt: 00:00:00.0059258; pro Aufruf: 0ms

Test 'Procedure-Insert' - insgesamt: 00:00:01.2567146; pro Aufruf: 125ms

Test 'Procedure-Update' - insgesamt: 00:00:00.7442440; pro Aufruf: 74ms

Test 'Procedure-Select' - insgesamt: 00:00:00.5120446; pro Aufruf: 51ms

Test 'Prozedur-Löschen' - insgesamt: 00:00:01.0619165; pro Aufruf: 106ms

Die Situation mit dem Remote-Server ist viel besser, aber immer noch eine große Kluft zwischen Plaqin-Abfragen und gespeicherten Prozeduren:

Test 'Script -Insert' - insgesamt: 00:00:00.0709299; pro Aufruf: 7ms

Test 'Script -Update' - insgesamt: 00:00:00.0161777; pro Aufruf: 1ms

Test 'Script -Select' - insgesamt: 00:00:00.0258113; pro Aufruf: 2ms

Test 'Script -Delete' - insgesamt: 00:00:00.0166242; pro Aufruf: 1ms

Test 'Procedure-Insert' - insgesamt: 00:00:00.5116138; pro Aufruf: 51ms

Test 'Procedure-Update' - insgesamt: 00:00:00.3802251; pro Aufruf: 38ms

Test 'Procedure-Select' - insgesamt: 00:00:00.1241245; pro Aufruf: 12ms

Test 'Prozedur-Löschen' - insgesamt: 00:00:00.4336334; pro Aufruf: 43ms

Gibt es eine Möglichkeit, die SP-Leistung zu verbessern? Bitte um Rat.

ADO.NET-Treiber Version - 9.10.2.9

Server-Version - 9.10.0.9 (ANSI - DEUTSCH, OEM - DEUTSCH)

Merci !

Answer 1

Le site Vorteil v10 beta enthält eine Reihe von Leistungsverbesserungen, die direkt auf die Leistung von gespeicherten Prozeduren abzielen. Es gibt jedoch einige Dinge, die bei der aktuellen Version zu beachten sind:

In Ihrer CreateItems-Prozedur wäre es wahrscheinlich effizienter, die

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

mit der Verwendung eines einzigen Cursors zum Abrufen aller Parameter:

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;

Dadurch werden 3 Anweisungs-Parsing-/Semantik-/Optimierungs-/Ausführungsoperationen vermieden, nur um die Eingabeparameter abzurufen (ich weiß, wir müssen die __input-Tabelle wirklich ganz abschaffen).

Die SelectItems-Prozedur wird in den seltensten Fällen so schnell sein wie ein Select vom Client, insbesondere in diesem Fall, in dem sie eigentlich nichts anderes tut als einen Parameterwert zu abstrahieren (was auf dem Client leicht möglich ist). Denken Sie daran, dass es sich um einen JOIN handelt und der SELECT zum Füllen der __Output-Tabelle ein statischer Cursor sein wird (d.h. eine interne temporäre Datei, die der Server erstellen und füllen muss), aber jetzt haben Sie zusätzlich die __Output-Tabelle, die eine weitere temporäre Datei für den Server ist, Außerdem entsteht zusätzlicher Aufwand, um die Tabelle __output mit Daten zu füllen, die bereits in der temporären Tabelle des statischen Cursors gespeichert sind, nur um sie zu duplizieren (der Server könnte dies besser erkennen und die Tabelle __output durch den bestehenden Verweis auf den statischen Cursor ersetzen, was er aber derzeit nicht tut).

Ich werde versuchen, etwas Zeit zu finden, um Ihre Verfahren für Version 10 auszuprobieren. Wenn Sie die Testtabellen haben, die Sie bei Ihren Tests verwendet haben, können Sie sie gerne zippen und an Advantage@iAnywhere.com schicken und im Betreff attn:JD angeben.

Answer 2

Es gibt eine Änderung, die helfen würde, die CreateItems Verfahren. Ändern Sie die folgenden zwei Anweisungen:

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

Zu diesem:

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

Ich habe mir die Abfrageplaninformationen (in Advantage Data Architect) für die erste Version dieser Anweisung angesehen. Es sieht so aus, als ob der Optimierer nicht bricht, dass MAX(id)+1 in die einzelnen Bestandteile zerlegt. Die Aussage select max(id) from maintable kann mit Hilfe des Index für das ID-Feld optimiert werden. Es scheint, dass max(id)+1 ist nicht optimiert. Eine solche Änderung wäre also ziemlich bedeutsam, vor allem, wenn die Tabelle wächst.

Eine andere Möglichkeit, die helfen könnte, ist das Hinzufügen einer CACHE PREPARE ON; Anweisung an den Anfang jedes Skripts. Dies kann bei bestimmten Verfahren hilfreich sein, wenn sie mehrfach ausgeführt werden.

Modifier Die Vorteil v10 beta wurde heute veröffentlicht. Also habe ich Ihre CreateItems Verfahren sowohl mit v9.1 als auch mit der neuen Beta-Version. Ich habe 1000 Iterationen auf dem Remote-Server durchgeführt. Der Geschwindigkeitsunterschied war signifikant:

v9.1:      101 seconds
v10 beta:  2.2 seconds

Beachten Sie, dass ich eine Version mit dem select max(id) Änderung, die ich oben beschrieben habe. Diese Tests wurden auf meinem ziemlich alten Entwicklungs-PC durchgeführt.