Most efficient way to append records containing lots of text

jeremyw · Post by **jeremyw** » Tue 05 Oct 2010 21:31

I figured I'd put this question out there for the experts.

I have a table designated for logging events in our software program, and this table has a few ntext type columns for storing text specific to that event. Because this text can sometimes be quite long, I can't really embed it in a simple SQL INSERT command, so I'm forced to find an alternative method for appending these types of records.

With that being said, what's the best way to do this? I know I can use the TMSTable or TMSQuery components to directly append a row using the standard VCL TDataset methods, but how efficient is this? I obviously don't need to query or retrieve any of the existing records, so how should I go about simply and quickly appending a new record so I can write long text strings to the underlying ntext columns?

AndreyZ · Post by **AndreyZ** » Wed 06 Oct 2010 11:19

Hello,

You can use the TMSLoader component for appending records in the most efficient way. TMSLoader serves for fast loading of data to the server. For more information please read SDAC Reference Manual.

jeremyw · Post by **jeremyw** » Thu 07 Oct 2010 00:03

Thanks for the fast reply!

The TMSLoader was looking like a good solution, but then I realized its Connection property is of type TMSConnection, which means I can't use a TMSCompactConnection object (for SQL Server Compact). We're planning on supporting both SQL Server Express and Compact, so the TMSLoader option probably won't work, at least not for Compact databases.

With that being said, what's the next best option? We're really not looking at adding tons of rows at once either, but do keep in mind that this table will fill up pretty quickly, so I hate to do any sort of query that has to fetch this data before I can start appending a new row.

AndreyZ · Post by **AndreyZ** » Thu 07 Oct 2010 11:37

You can use the following code:

Code: Select all

  MSQuery.SQL.Add('insert into tbl_ntext(id, bigtext) values(:id, :bigtext)');
  MSQuery.ParamByName('id').AsInteger := 1;
  MSQuery.ParamByName('bigtext').AsString := 'bigtext';
  MSQuery.Execute;

In this case the TMSQuery component doesn't fetch data from database.

Also you can use the TMSQuery.CashedUpdates and TMSQuery.Options.UpdateBatchSize options to implement quick insertion of data into the table.
You can look at the example of using this options in SDACDemo.

jeremyw · Post by **jeremyw** » Tue 12 Oct 2010 16:12

I should have thought of trying parameterized SQL earlier. Anyway, that seems to work perfectly. Thanks again for the quick responses!

AndreyZ · Post by **AndreyZ** » Wed 13 Oct 2010 06:25

Feel free to contact us if you have any further questions about SDAC.