4

I am using array DML operations to speed insert of large number of records into SQL database. The principle is described here. Sample code how this functionality is used:

TFDQuery *FDQuery1;
...
FDQuery1->SQL->Text = "insert into MyTab values (:p1, :p2, :p3)";
// here FDQuery1->Params collection is filled by 3 parameters
const int array_size = 100;
FDQuery1->Params->ArraySize = array_size;
FDQuery->Prepared = true;
for(int i = 0; i < array_size; i++)
{
  FDQuery1->Params[0]->AsIntegers[i] = i;
  FDQuery1->Params[1]->AsStrings[i] = "qwe";
  FDQuery1->Params[2]->Clear(i);
}
FDQuery1->Execute(array_size);

Essentially it means that instead of calling database engine client function for every row inserted, I first prepare data I need to insert as array. Typical size of array is 1000 items. Then I call client function with array as parameter. Unfortunately nowhere in documentation is described when is memory for array of parameters is released. Is it done when I unprepare query ?

TFDQuery *query;
...
query->Prepared = false;

Or is it done when I close the query ?

query->Close();

Or is it done when I set array size to 1 ?

query->Params->ArraySize = 1
Ken White
  • 123,280
  • 14
  • 225
  • 444
truthseeker
  • 1,220
  • 4
  • 25
  • 58
  • Can you show the code how you write the array parameters? – Uwe Raabe Jul 01 '16 at 07:42
  • It doesn't bear directly on your q, but which Sql Server type are you using? – MartynA Jul 01 '16 at 08:44
  • I am using Firebird 2.5.4. – truthseeker Jul 01 '16 at 08:46
  • The memory for the param arrays is released when setting ArraySize to 1, but I am not sure if that is relevant here. Do you experience any problem or are you just curious? – Uwe Raabe Jul 01 '16 at 09:07
  • 2
    Just dont want to leave allocated memory if it is no longer used. According to documentation (see link above) Oracle database will alocate 4000 bytes for every string. So for 1000 records its 4MB and we are talking only about one column. Some ot my tables have tens of columns. And my program uses more than one query. Its a matter of good programming style to release memory when it is no longer needed. – truthseeker Jul 01 '16 at 09:18
  • In that case setting ArraySize to 1 should be sufficient. – Uwe Raabe Jul 01 '16 at 11:31

1 Answers1

1

Parameter collection should IMHO remain unchanged when unpreparing or closing the query by design because it's a manually populated collection (it is automatically prefilled by preprocessor when the command is assigned and ParamCreate enabled; corrected at command preparation). But you may want to execute the same command later without setting up the same collection.

If you know that you won't execute the same command again, you can call the Clear method:

query->Params->Clear();

If you want to preserve parameter settings, and release only resources consumed by the value storage, you can reduce number of parameter rows through the ArraySize property:

query->Params->ArraySize = 1
Victoria
  • 7,822
  • 2
  • 21
  • 44