Out of memory error as inserting a 600MB files into sql server express as filestream data

Question

(please read the update section below, I leave the original question too for clarity)

I am inserting many files into a SQL Server db configured for filestream.

I am inserting in a loop the files from a folder to a database table.

Everything goes fine until I try to insert a 600 MB file.

As it inserts it there is a +600MB memory usage in task manager and I have the error.

The DB size is < 1 GB and the total size of documents is 8 GB, I am using SQL Server Express R2, and according to the documentation I could have problems only if trying to insert a document that is greater than 10 GB (Express limitation) - Current DB Size.

Can anyone tell me why do I have this error? It is very crucial for me.

UPDATE FOR BOUNTY:

I offered 150 because it is very crucial for me!

This seems to be a limitation of Delphi memory Manager, trying to insert a document bigger than 500MB, I didn't check the exact threshold anyway it is between 500 and 600MB). I use SDAC components, in particular a TMSQuery (but I think the same can be done with and TDataset descendant), to insert the document in a table that has a PK (ID_DOC_FILE) and a varbinary(max) field (DOCUMENT) I do:

procedure UploadBigFile;
var 
  sFilePath: String; 
begin 
  sFilePath := 'D:\Test\VeryBigFile.dat'; 
  sqlInsertDoc.ParamByName('ID_DOC_FILE').AsInteger := 1; 
  sqlInsertDoc.ParamByName('DOCUMENT').LoadFromFile(sFilePath, ftblob); 
  sqlInsertDoc.Execute; 
  sqlInsertDoc.Close; 
end;

SDAC team told me this is a limitation of Delphi memory manager. Now since SDAC doesn't support filestream I cannot do what has been suggested in c# in the first answer. Is the only solution reporting to Embarcadero and ask a bug fix?

FINAL UPDATE:

Thanks, really, to all you that answered me. For sure inserting big blobs can be a problem for the Express Edition (because the limitations of 1 GB of ram), anyway I had the error on the Enterprise edition, and it was a "delphi" error, not a sql server one. So I think that the answer that I accepted really hits the problem, even if I have no time to verify it now.

Answer 1

SDAC team told me this is a limitation of Delphi memory manager

To me that looked like an simplistic answer, and I investigated. I don't have the SDAC components and I also don't use SQL Server, my favorites are Firebird SQL and the IBX component set. I tried inserting an 600Mb blob into a table, using IBX, then tried the same using ADO (covering two connection technologies, both TDataSet descendants). I discovered the truth is somewhere in the middle, it's not really the memory manager, it's not SDAC's fault (well... they are in a position to do something about it, if many more people attempt inserting 600 Mb blobs into databases, but that's irrelevant to this discussion). The "problem" is with the DB code in Delphi. As it turns out Delphi insists on using an single Variant to hold whatever type of data one might load into an parameter. And it makes sense, after all we can load lots of different things into an parameter for an INSERT. The second problem is, Delphi wants to treat that Variant like an VALUE type: It copies it around at list twice and maybe three times! The first copy is made right when the parameter is loaded from the file. The second copy is made when the parameter is prepared to be sent to the database engine.

Writing this is easy:

var V1, V2:Variant;
V1 := V2;

and works just fine for Integer and Date and small Strings, but when V2 is an 600 Mb Variant array that assignment apparently makes a full copy! Now think about the memory space available for a 32 bit application that's not running in "3G" mode. Only 2 Gb of addressing space are available. Some of that space is reserved, some of that space is used for the executable itself, then there are the libraries, then there's some space reserved for the memory manager. After making the first 600 Mb allocation there just might not be enough available addressing space to allocate an other 600 Mb buffer! Because of this it's safe to blame it on the memory manager, but then again, why exactly does the DB stuff need an other copy of the 600 Mb monster?

One possible fix

Try splitting up the file into smaller, more manageable chunks. Set up the database table to have 3 fields: ID_DOCUMENT, SEQUENCE, DOCUMENT. Also make the primary key on the table to be (ID_DOCUMENT, SEQUENCE). Next try this:

procedure UploadBigFile(id_doc:Integer; sFilePath: String);
var FS:TFileStream;
    MS:TMemoryStream;
    AvailableSize, ReadNow:Int64;
    Sequence:Integer;
const MaxPerSequence = 10 * 1024 * 1024; // 10 Mb
begin

  FS := TFileStream.Create(sFilePath, fmOpenRead);
  try
    AvailableSize := FS.Size;
    Sequence := 0;
    while AvailableSize > 0 do
    begin
      if AvailableSize > MaxPerSequence then
        begin
          ReadNow := MaxPerSequence;
          Dec(AvailableSize, MaxPerSequence);
        end
      else
        begin
          ReadNow := AvailableSize;
          AvailableSize := 0;
        end;
      Inc(Sequence); // Prep sequence; First sequence into DB will be "1"
      MS := TMemoryStream.Create;
      try
        MS.CopyFrom(FS, ReadNow);

        sqlInsertDoc.ParamByName('ID_DOC_FILE').AsInteger := id_doc; 
        sqlInsertDoc.ParamByName('SEQUENCE').AsInteger := sequence; 
        sqlInsertDoc.ParamByName('DOCUMENT').LoadFromStream(MS, ftblob); 
        sqlInsertDoc.Execute; 

      finally MS.Free;
      end;
    end;
  finally FS.Free;
  end;

  sqlInsertDoc.Close;       

end;