Advantage数据库服务器：存储过程性能缓慢

Question

我对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） 。

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

而且我得到以下结果：为了测试性能，我（ADS使用ADO.NET提供的F＃）创建了一个简单的测试

测试 '脚本 - 插入' - 总计：00：00：00.0292841;每次调用：2ms

测试“脚本更新” - 总计：00：00：00.0056296;每次调用：0ms

Test'Script -Select' - total：00：00：00.0051738;每次通话：0ms

Test'Script-Delete' - total：00：00：00.0059258;每次调用：0ms

Test'Procedure-Insert' - total：00：00：01.2567146;每次通话：125ms

测试'程序更新' - 总计：00：00：00.7442440;每次通话：74ms

测试'程序 - 选择' - 总计：00：00：00.5120446;每次通话：51ms

测试'程序 - 删除' - 总计：00：00：01.0619165;每次通话：106ms

与远程服务器的情况就好多了，但还是plaqin查询和存储过程的差距很大：

测试“脚本 - 插入” - 总：00：00 ：00.0709299;每个电话：7ms

测试'脚本 - 更新' - 总计：00：00：00.0161777;每次通话：1ms

测试“脚本 - 选择” - 总计：00：00：00.0258113;每个电话：2ms

Test'Script -Delete' - total：00：00：00.0166242;每次调用：1ms

测试'程序 - 插入' - 总计：00：00：00.5116138;每次通话：51ms

测试'Procedure-Update' - 总计：00：00：00.3802251;每次通话：38ms

测试'程序 - 选择' - 总计：00：00：00.1241245;每次调用：12ms

测试'程序 - 删除' - 总计：00：00：00.4336334;每通电话：43ms

难道有机会提高SP的性能吗？请指教。

ADO.NET驱动程序版本 - 9.10.2.9

服务器版本 - 9.10.0。9（ANSI - 德国，OEM - 德国）

谢谢！

Answer 1

Advantage v10 beta包括直接针对存储过程性能的各种性能改进。这里有一些事情要考虑与目前的出货版本，但是：

在你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过程很少会像从客户端选择一样快，特别是在这种情况下，除了抽象参数值（可以很容易地在客户端上完成）之外，它没有做任何事情。请记住，因为它是JOIN，所以填充__output表的SELECT将成为一个静态游标（意味着服务器要创建和填充的内部临时文件），但是现在还有另一个__output表临时文件的服务器，再加上你有额外的开销来填充这个__output表中已经放置在静态游标临时表中的数据，只是为了复制它（服务器可以更好地检测这个并替换__output与现有的静态游标引用，但它目前不）。

我会尝试花一些时间在版本10上尝试您的过程。如果您有测试表在测试中使用，请随意将它们压缩并发送到[email protected]并将attn：JD在主题。

Answer 2

有一个变化可以帮助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; 

我看着查询计划信息（数据优势建筑师）该语句的第一个版本。看起来好像优化器不会将MAX(id)+1分解成组件。语句select max(id) from maintable可以使用ID字段上的索引进行优化。看起来max(id)+1未优化。因此，随着表格的增长，做出这种改变将会非常重要。

另一件可能有用的事情是在每个脚本的顶部添加一个CACHE PREPARE ON;语句。这可以在多次运行时帮助执行某些过程。

编辑Advantage v10 beta今天发布。所以我跑了你的CreateItems程序与v9.1和新的beta版本。我对远程服务器运行了1000次迭代。速度差异显著：

v9.1:  101 seconds 
v10 beta: 2.2 seconds 

注意，我跑了一个版本，我上述的select max(id)变化。这个测试是在我相当老的开发PC上。