mORMot Open Source

firstfriday

Member

Registered: 2015-07-21

Posts: 26

Memory Leak when using Parameter in Query with MSSQL

When doing mass insertions with MSSQL (only here, not SQLite) there is a quite big memory loss when using parameters(see Version2).
Without params everything works fine (see Version1).

Code to reproduce:

procedure TForm2.cmdErrorClick(Sender: TObject);
var
  i:integer;
  SQL,s:string;
  Props:TOleDBMSSQL2012ConnectionProperties;
  SQLDBConnection:TSQLDBConnection;
  Query : TQuery;
begin
  (*
    create a mssql database execute this
    create table [dbo].[test] (
      [id] [int] not null ,
      [id_u] [int] null,
      [fkey] [nvarchar] (255) collate sql_latin1_general_cp1_ci_as null ,
      [svalue] [nvarchar] (255) collate sql_latin1_general_cp1_ci_as null ,
      [ivalue] [int] null ,
      [fvalue] [float] null
    ) on [primary]
    go
  *)
  Props:=TOleDBMSSQL2012ConnectionProperties.create('localhost','test_db','sa','');
  SQLDBConnection:=props.NewConnection;

  SQL := 'TRUNCATE TABLE test';
  query:=Tquery.Create(SQLDBConnection);
  query.SQL.clear;
  query.SQL.Add(SQL);
  query.ExecSQL;
  query.free;

  s:='String';
  query:=Tquery.Create(SQLDBConnection);
  for i:=1 to 100000 do
  begin
    //SQL := format('insert into test (id,ivalue,fkey,svalue) values (%d,%d,''%s'',''%s'')',[i,i,s,s]); // Version 1=> works
    SQL := 'insert into test (id,ivalue,fkey,svalue) values (:id,:ivalue,:fkey,:svalue)' ;// Version 2 =>memory leak when executed
    query.SQL.Clear;
    query.SQL.Add(SQL);
    // Version 2
    query.ParamByName('id').AsInteger     :=i;
    query.ParamByName('ivalue').AsInteger :=i;
    query.ParamByName('fkey').AsString   :='String '+inttostr(i);
    query.ParamByName('svalue').AsString :='String '+inttostr(i);

    query.ExecSQL;
  end;
  query.free;
end;

Arnauld, can you fix this?

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: Memory Leak when using Parameter in Query with MSSQL

@AB. I have just checked the sample.
The problem is in OleDB statement caching. As I wrote in this topic we should disable a cache for OleDB

@firstfriday
Please, add a line

 Props.UseCache := False; 

just after connection creation.
and

SQLDBConnection.Destroy;
Props.Free;

to the end of test.

BTW I hope it just test code, but in any case it is better to: 
1) Wrap your code in transaction ( SQLDBConnection.StartTransaction; try .. finally SQLDBConnection.Commit except SQLDBConnection.Rollback ) ( x2 - x10 faster)
2) Move query.SQL.Add(SQL) out of circle ( + 20%)
3) do not use TQuery use a SQLDBConnection.NewStatementPrepared with ? as a parameter placeholder, when in circle bind params and execute query

firstfriday

Member

Registered: 2015-07-21

Posts: 26

Re: Memory Leak when using Parameter in Query with MSSQL

Props.UseCache := False;
was the a goog idea! Works now. (yes, it's only a test case)
Thanks