Insufficient memory when I perform a command

Database
Enthusiast

Insufficient memory when I perform a command

Hi guys!

I'm facing the following problem:

I need to run at about 5000 INSERT commands at the same time.
I programmed it in C#/.Net:

command.CommandText = strInserts;
command.ExecuteNonQuery();


Into the variable string strInserts I have these values:
"INSERT INTO TABLE1(FILED1) VALUES(0);
INSERT INTO TABLE1(FILED1) VALUES(1);
.
.
.
INSERT INTO TABLE1(FILED1) VALUES(4999);"


When the command is performed, this error message appears:
"[Teradata Database] [3710] Insufficient memory to parse this request, during Resolver phase."

Please, what can I do to resolve this problem?

Thanks for help,
Anderson

4 REPLIES
Senior Apprentice

Re: Insufficient memory when I perform a command

Hi Anderson,
you're submitting a huge multi-statement request, too huge to parse it as a single block.

- split it into several smaller batches
- Or better check if iterated request are available for .NET and use that. In JDBC it's addbatch & executebatch.

Dieter
Teradata Employee

Re: Insufficient memory when I perform a command

.NET Data Provider for Teradata supports batch updates.

See documentation for DbDataAdapter.UpdateBatchSize in MSDN:

http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

http://msdn.microsoft.com/en-us/library/system.data.common.dbdataadapter.updatebatchsize.aspx
Enthusiast

Re: Insufficient memory when I perform a command

Hi guys!

Thanks for help me!

I got the same problem (Insufficient memory) referencing my 5000 inserts to the DataAdpter's property InsertCommand.

This is sheet of the programming:

DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
//_sbComandos is a StringBuilder. It contains the 5000 insert commands splitted by ';'
command.CommandText = _sbComandos.ToString();

adapter.InsertCommand = (TdCommand)comando;
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

dataSet.Tables[0].Rows.Add("0");
adapter.UpdateBatchSize = 0;

adapter.Update(dataSet);


What should I do to can use the 5000 insert in Update Batch Size?
Or is there another solution?

Thanks,
Anderson
Teradata Employee

Re: Insufficient memory when I perform a command

The DataAdapter.InsertCommand.CommandText must be a single-row parameterized query:

Insert Into Table1(Field1) Values (?)

The DataTable, which serves as input parameter rows, must hold X rows. In your case 5000 rows.

See: http://www.teradata.com/teradataforum/Topic11803-11-2.aspx

Note I am not sure if DBS will accept 5000 rows in one query. There are other limits which govern how many rows can be send in one Query. .NET Data Provider will divide the Batch Update into one or more queries when UpdateBatchSize is set to 0. However it is very easy to experiment with different Batch Sizes.