Batch Update only able to handle about 125 rows?

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
sugi
Posts: 43
Joined: Wed 07 Dec 2016 02:05

Batch Update only able to handle about 125 rows?

Post by sugi » Sat 10 Nov 2018 04:08

Hello guys,

I using Batch Update operation. It only able to handle update up to about 125 records, more than that, it was raised an error :

Dynamic SQL Error, too many context of relation/procedure/views. Maximum allowed is 256.

IBDAC ver 6.1.4, C++ Builder 6
Here are the sample codes :

Code: Select all

   
   QUpdSrc->SQL->Text = "update t_tbl2 set ln_no = -995 where id = :ID";
   QUpdSrc->Params->Items[0]->DataType = ftInteger;
   QUpdSrc->Params->ValueCount = 1000;
   
   Q->Close();
   Q->Open();
   int iGd = 0;
   while(!Q->Eof)
   {
   	if(!Trs->Active) Trs->StartTransaction();
   	// do some procssing
   	QUpdSrc->Params->Items[0]->Values[iGd]->AsInteger = QID->AsInteger;
   	Q->Next();
   	iGd ++;
   }
   
   if(iGd > 0) QUpdSrc->Execute(iGd);
   Trs->Commit();
Batch Insert also has a limit. I tried ValueCount = 100000, it raised an error :
Access violation.

What is the limit of ValueCount for Batch Insert and Bacth Update?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Batch Update only able to handle about 125 rows?

Post by ViktorV » Mon 12 Nov 2018 11:02

Unfortunately, we can't reproduce the issue on the last IBDAC 6.2.9 version.
Please check whether the issue is reproduced on the latest version of UniDAC IBDAC 6.2.9 If it is, please compose a full sample demonstrating the described behavior and send it to us via form e-support: https://www.devart.com/company/contactform.html, including the scripts for creating and filling database objects. Also, please specify the InterBase (Firebird) version you are using.

sugi
Posts: 43
Joined: Wed 07 Dec 2016 02:05

Re: Batch Update only able to handle about 125 rows?

Post by sugi » Tue 13 Nov 2018 01:46

I am using Firebird 3.0.3 Superserver.

Unfortunately, I don't have IBDAC latest version.

But I think, even with latest version, there has to be a limit with Batch Update ValueCount and more importantly, is it safe to use it?

During the test, after using Batch Update, I encountered error from firebird, something like
internal consistency error

Because of this error, I am not using Batch Update anymore and back to the old method, update record one by one, more safe.

Thanks anyway for your help.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Batch Update only able to handle about 125 rows?

Post by ViktorV » Tue 13 Nov 2018 07:57

Firebird has its own internal limitations: for example, limitation on the SQL statement size, limitation on the use of different data types, etc., which we cannot influence, and, regardless of the ValueCount value, our components try to internally adjust this value in order to circumvent the above Firebird limitations.
The optimal values ​​of the ValueCount parameter depend on a great many factors of the environment of each specific case; therefore, the most optimal values ​​of the ValueCount parameter are selected independently empirically.
Please compose a full sample demonstrating the described behavior and send it to us via form e-support: https://devart.com/company/contactform.html, including the scripts for creating and filling database objects, in orer for us to check whether the error is reproduced on the latest version of IBDAC.

Post Reply