|
|
|
Forum Member
 
Group: Forum Members
Last Login: 8/4/2009 4:32:22 PM
Posts: 25,
Visits: 131
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 3:36:56 PM
Posts: 151,
Visits: 452
|
|
.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
--Cal
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/4/2009 4:32:22 PM
Posts: 25,
Visits: 131
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 3:36:56 PM
Posts: 151,
Visits: 452
|
|
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.
--Cal
|
|
|
|