TLoader, blobs and alternatives

[sandy771]

I have an app that writes 100's of thousands of entries to an sqlite database, most of the fields are integers, strings etc. and I currently use a TLoader to add about 1000 rows at a time.

The database is populated only once and after that minor changes are made to existing records but typically (but not always) additonal rows are not added.

I have now decided that I want to add a blob type record to each row and that I want to populate this record at load time.

As TLoader does not support blobs I can redesign my code to insert rows one at a time but cache and do each 1000 in a single transaction.

There will be quite a bit of coding to modify my code and before I sit down and do this I wondered what the groups thoughts were on any speed implications. i.e. I am trying to load the database in the smallest possible time so will 1000 x UniQuery->Add() suitably wrapped as a single transaction be as fast as as loading a 1000 rows via TLoader?

Thanks

[sandy771]

Any thoughts from the devart team? is TLoader the fastest way of populating a large sqlite table or is it more of a convenience thing?

[AlexP]

Hello,

It is true that TuniLoader does not directly support working with Blob data, however, you can convert your data into Variant and load them using TuniLoader. An example is provided below:

Code: Select all

procedure TForm1.PutData(Sender: TDALoader);
var
   Count: Integer;
   i: Integer;
   MS: TMemoryStream;
begin
   for i := 1 to 10 do begin
      Sender.PutColumnData(0, i, i);
      Sender.PutColumnData(1, i, IntToStr(i));

      MS:= TMemoryStream.Create;
      try
        MS.LoadFromFile('d:\+IntToStr(i)+'.html');
        Sender.PutColumnData(2, i, StreamToVariant(MS));
      finally
        MS.Free;
      end;
   end;
end;


function TForm1.StreamToVariant(Value: TMemoryStream): variant;
var
  buf: pointer;
  binaryData: Variant;
begin
  binaryData := VarArrayCreate([0, Value.Size-1], varByte);
  buf := VarArrayLock(binaryData);
    try
      Value.Seek(0, soBeginning);
      Value.ReadBuffer(buf^, Value.Size);
      Result := binaryData;
    finally
      VarArrayUnlock(binaryData);
    end;
end;

The speed of data loading is proportional to the volume of Blob data being loaded, consequently, the volume of Blob data also defines which method of loading data (Loader or Query) should be used. Loader loads data faster than query, however, if the volume of Blob is large, a higher speed of loading using Loader will be of no importance.

[sandy771]

I have seen a possible bug while implementing the above.

If a column in the underlying database does not exist. an exception is generated on OnPutdata rather than when the loader column is created.

[AlexP]

Hello,

If you add a field to TUniLoader manually, the check if the corresponding field is present in the table is not performed, and the error occurs only when the Putdata method is called.