title: Advantage Database Server: slow stored procedure performance
body: <p>I have a question about a performance of stored procedures in the ADS. I created a simple database with the following structure:</p>
  <pre><code>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;
  </code></pre>
  <p>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):</p>
  <pre><code>open System;
  open System.Data;
  open System.Diagnostics;
  open Advantage.Data.Provider;
  
  
  let mainName = &quot;main name #&quot;;
  let subName  = &quot;sub name #&quot;;
  
  // INSERT
  let cmdTextScriptInsert = &quot;
      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;&quot;;
  let cmdTextProcedureInsert = &quot;CreateItems&quot;;
  
  // UPDATE
  let cmdTextScriptUpdate = &quot;
      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;&quot;;
  let cmdTextProcedureUpdate = &quot;UpdateItems&quot;;
  
  // SELECT
  let cmdTextScriptSelect = &quot;
      SELECT m.Value * s.Value FROM MainTable m INNER JOIN SubTable s ON m.Id = s.MainId WHERE m.Name = :MainName;&quot;;
  let cmdTextProcedureSelect = &quot;SelectItems&quot;;
  
  // DELETE
  let cmdTextScriptDelete = &quot;
      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;&quot;;
  let cmdTextProcedureDelete = &quot;DeleteItems&quot;;
  
  
  
  
  let cnnStr = @&quot;data source=D:\DB\test.add; ServerType=local; user id=adssys; password=***;&quot;;
  let cnn = new AdsConnection(cnnStr);
  
  try
      cnn.Open();
  
      let cmd = cnn.CreateCommand();
  
      let parametrize ix prms =
          cmd.Parameters.Clear();
          
          let addParam = function
              | &quot;MainName&quot;  -&gt; cmd.Parameters.Add(&quot;:MainName&quot; , mainName + ix.ToString()) |&gt; ignore;
              | &quot;SubName&quot;   -&gt; cmd.Parameters.Add(&quot;:SubName&quot;  , subName + ix.ToString() ) |&gt; ignore;
              | &quot;MainValue&quot; -&gt; cmd.Parameters.Add(&quot;:MainValue&quot;, ix * 3                  ) |&gt; ignore;
              | &quot;SubValue&quot;  -&gt; cmd.Parameters.Add(&quot;:SubValue&quot; , ix * 7                  ) |&gt; ignore;
              | _ -&gt; ()
  
          prms |&gt; List.iter addParam;
  
  
      let runTest testData = 
  
          let (cmdType, cmdName, cmdText, cmdParams) = testData;
          
          let toPrefix cmdType cmdName =
              let prefix = match cmdType with
                  | CommandType.StoredProcedure -&gt; &quot;Procedure-&quot;
                  | CommandType.Text            -&gt; &quot;Script   -&quot;
                  | _                           -&gt; &quot;Unknown  -&quot;
              in prefix + cmdName;
  
          let stopWatch = new Stopwatch();
      
          let runStep ix prms =
              parametrize ix prms;
              stopWatch.Start();
              cmd.ExecuteNonQuery() |&gt; ignore;
              stopWatch.Stop();
  
          cmd.CommandText &lt;- cmdText;
          cmd.CommandType &lt;- cmdType;
          
          let startId = 1500;
          let count = 10;
  
          for id in startId .. startId+count do
              runStep id cmdParams;
  
          let elapsed = stopWatch.Elapsed;
          Console.WriteLine(&quot;Test '{0}' - total: {1}; per call: {2}ms&quot;, toPrefix cmdType cmdName, elapsed, Convert.ToInt32(elapsed.TotalMilliseconds)/count);
          
  
      let lst = [
          (CommandType.Text,            &quot;Insert&quot;, cmdTextScriptInsert,    [&quot;MainName&quot;; &quot;SubName&quot;; &quot;MainValue&quot;; &quot;SubValue&quot;]);
          (CommandType.Text,            &quot;Update&quot;, cmdTextScriptUpdate,    [&quot;MainName&quot;; &quot;MainValue&quot;; &quot;SubValue&quot;]);
          (CommandType.Text,            &quot;Select&quot;, cmdTextScriptSelect,    [&quot;MainName&quot;]);
          (CommandType.Text,            &quot;Delete&quot;, cmdTextScriptDelete,    [&quot;MainName&quot;])
          (CommandType.StoredProcedure, &quot;Insert&quot;, cmdTextProcedureInsert, [&quot;MainName&quot;; &quot;SubName&quot;; &quot;MainValue&quot;; &quot;SubValue&quot;]);
          (CommandType.StoredProcedure, &quot;Update&quot;, cmdTextProcedureUpdate, [&quot;MainName&quot;; &quot;MainValue&quot;; &quot;SubValue&quot;]);
          (CommandType.StoredProcedure, &quot;Select&quot;, cmdTextProcedureSelect, [&quot;MainName&quot;]);
          (CommandType.StoredProcedure, &quot;Delete&quot;, cmdTextProcedureDelete, [&quot;MainName&quot;])];
  
      lst |&gt; List.iter runTest;
  
  finally
      cnn.Close();
  </code></pre>
  <p>And I'm getting the following results:</p>
  <blockquote>
  <p>Test 'Script   -Insert' - total: 00:00:00.0292841; per call: 2ms</p>
  <p>Test 'Script   -Update' - total: 00:00:00.0056296; per call: 0ms</p>
  <p>Test 'Script   -Select' - total: 00:00:00.0051738; per call: 0ms</p>
  <p>Test 'Script   -Delete' - total: 00:00:00.0059258; per call: 0ms</p>
  <p>Test 'Procedure-Insert' - total: 00:00:01.2567146; per call: 125ms</p>
  <p>Test 'Procedure-Update' - total: 00:00:00.7442440; per call: 74ms</p>
  <p>Test 'Procedure-Select' - total: 00:00:00.5120446; per call: 51ms</p>
  <p>Test 'Procedure-Delete' - total: 00:00:01.0619165; per call: 106ms</p>
  </blockquote>
  <p>The situation with the remote server is much better, but still a great gap between plaqin queries and stored procedures:</p>
  <blockquote>
  <p>Test 'Script   -Insert' - total: 00:00:00.0709299; per call: 7ms</p>
  <p>Test 'Script   -Update' - total: 00:00:00.0161777; per call: 1ms</p>
  <p>Test 'Script   -Select' - total: 00:00:00.0258113; per call: 2ms</p>
  <p>Test 'Script   -Delete' - total: 00:00:00.0166242; per call: 1ms</p>
  <p>Test 'Procedure-Insert' - total: 00:00:00.5116138; per call: 51ms</p>
  <p>Test 'Procedure-Update' - total: 00:00:00.3802251; per call: 38ms</p>
  <p>Test 'Procedure-Select' - total: 00:00:00.1241245; per call: 12ms</p>
  <p>Test 'Procedure-Delete' - total: 00:00:00.4336334; per call: 43ms</p>
  </blockquote>
  <p>Is it any chance to improve the SP performance? Please advice.</p>
  <p>ADO.NET driver version - 9.10.2.9</p>
  <p>Server version - 9.10.0.9 (ANSI - GERMAN, OEM - GERMAN)</p>
  <p>Thanks!</p>