Datatable: adding rows very slow

[Andrius B.]

Hi all.
I have VB.NET 2005. And I still continue to use Datatable, for its fast
sorting (comparing to arrays or generics) and posibillity to apply data
filter in Dataview.
The main problem is that adding many rows (row by row) to Datatable is very
slow. The fact that a datatable has no posibility to add a range of rows
(smth like generics have - AddRange) makes things complicated.
Is there some kind of similar object for manipulating data like the
Datatable does? T.i., to keep the fast sorting and some other good things,
and also a better mechanism for adding new members (rows) to it?


Thanks for any answer.

 

[Patrice]

And the rows you are adding are coming from ? You could perhaps use the
Merge method if they come from another datatable.

It could be also that it does some additional checking that you may want
disable if you know they are already correct...

 

[Andrius B.]

And the rows you are adding are coming from ? You could perhaps use the
Merge method if they come from another datatable.

Data come from a file, I read them using IO.File.ReadAllLines into an array
of strings and then manipulate line by line using loop

Dim Items () as Object
Dim dt As New DataTable

For ... 'Loop
'< here is some code to add values to members of Items >
dt.Rows.Add(Items)
Next...

It could be also that it does some additional checking that you may want
disable if you know they are already correct...

What do You mean "checking"? By whom? The datatable?

 

[Patrice]

Data come from a file, I read them using IO.File.ReadAllLines into an

array of strings and then manipulate line by line using loop

You may want to insert them directly in the DB. In particular if you are
using SQL Server see
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(VS.80).aspx.
It would be my first choice.

If you just want them in a datatable but don't plan to add them later to
your db, you can fill a datable using an IDataReader. You could implement
this yourself or could read the text file using a suitable driver and see if
it is faster...

I was thinking about primary keys or perhaps foreign keys checking done by
datatable/dataset but it seems you have a very simple datatable and that how
lines are read from the source could be a first step in enhancing things...

 

[Andrius B.]

My data source is a CSV file, data are separeted with ";". In the loop each
line is splitted using string.split function.
I have read about the using of Jet drivers or smth, t.i. to connecting to
CSV file like to a database using a connection. But also it is said that
using simple text file reading technologies is better (faster and so on)
than using database-connection like methods.

And my Datatable has no primary key an no foreign key. The datatable also is
not a part of Dataset.

 

[Patrice]

But also it is said that using simple text file reading technologies is

better (faster and so on) than using database-connection like methods.

In theory but for example split involves allocation for each row etc, the
driver might split this as it is reads etc... IMO your best bet is always to
try by yourself as it depends also on how it is done...

So is the ultimate goal to load those data in a backend database ? If yes, I
would use the bulkcopy link i mentioned in an earlier response.

My next option would be to use the IDataReader interface (either as
currently with your own file processing or with a driver).

A first step could be first to test just how much time is spent in adding
rows by constructing those rows out of thin air. This is just to see if this
is the file processing part that takes time or the addition itself (I
believe I participated in a similar thread but don't remember the
conclusion, if I remember it was something like adding the row and filling
the values vs filling the row values and then adding the row).

 

[Andrius B.]

Hi Patrice,

Thanks for the suggestion. I'll try to compare all the approches.

 

[Cor Ligthert[MVP]]

. But also it is said that

using simple text file reading technologies is better (faster and so on)
than using database-connection like methods.

There is also written that sails on a boat is better than using an engine,

I'm curious, who said the culprit above?

Cor

 

[Family Tree Mike]

There is also written that sails on a boat is better than using an engine,

I'm curious, who said the culprit above?

Cor

Do you mean the following:

"What, sir, would you make a ship sail against the wind and currents by
lighting a bonfire under her deck? I pray you, excuse me, I have not the
time to listen to such nonsense."

-- Napoleon Bonaparte, when told of Robert Fulton's steamboat, 1800s