Fastst way to merge 2 DataTables/DataViews/...

[DraguVaso]

Hi,

I have two DataTables (our DataViews or whatever that will suit the best for
the solution). I want to merge these two DataTables the fastest as possible,
but they have to be merged one table after the others: First all the recors
of DataTable1, and afterwarths the records of DataTable2.

Does anybody has any idea how to do this? The purpose it that it goes as
fast as possible! DataTable2 can have up to 15.000 records, DataTable1
100-200 records. The order of the records is really important: The records
of DataTable1 must come before those of DataTable2, and all of htem has to
be in the order they had orignaly in their DataTable.

Any help would be really appreciated! I tried with InsertAt, Import, Merge
etc, but none of them seemd really quick to mee :-/

Thanks a lot in advance,

Pieter

[Cor Ligthert [MVP]]

Pieter,

I think that I would create my own merge, by reading accessing all the
datatables in the same time and write them only once to a *new* table if
the comparassing tells that it should and otherwise skip them.

A very old way of datatape processing.

I hope this helps to give you idea's,

Cor

[DraguVaso]

How do you mean? there shouldn't be any comparisson at all! Just put all the
records in one DataTable/DataView/DataSet/ whatever...

Like this:
DataTable1
-----------
Record1A
Record1B
Record1C

DataTable2
------------
Record2A
Record2B

DataTableResult (which may be DataTable1 or DataTable2 or whatever)
-------------------------------------------------------------------------
Record1A
Record1B
Record1C
Record2A
Record2B

This is what shoudl happen: nothing more, nothing less: ALL the records of
NOTH DataTables should be in the (eventually) new DataTable, in the SAME
ORDER they were before, ONE DataTable AFTER ANOTHER...

Any idea how to do this as fast as possible?

"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:uV******** ******@TK2MSFTN GP14.phx.gbl...

Pieter,

I think that I would create my own merge, by reading accessing all the
datatables in the same time and write them only once to a *new* table if
the comparassing tells that it should and otherwise skip them.

A very old way of datatape processing.

I hope this helps to give you idea's,

Cor

[Cor Ligthert [MVP]]

Pieter,

Than it is even copy the first table and loop to the others while importing
the datarows to that table copied table.

Something as

dim dtBasis as Datatable = DtWaarMeeTeStar ten.Copy
toevoegroutine( dtBasis, TabellekeOmToeT eVoegen)
'To do for every datatable except the first one

\\\
private sub toevoegroutine( byval dt as datatable, byval Patat as DataTable)
for each dr as datarow in Patat.rows
dt.importrow(dr Patat)
Next
///

End sub

I hope that is what you mean

Cor

[DraguVaso]

Hi Cor,

This is indeed something I want, but the problem is that it doesn't go fast
enough... Adding 200 rows like this takes easily 2 seconds... It should take
only 0.01 second :-)

"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:eR******** ******@tk2msftn gp13.phx.gbl...

Pieter,

Than it is even copy the first table and loop to the others while
importing the datarows to that table copied table.

Something as

dim dtBasis as Datatable = DtWaarMeeTeStar ten.Copy
toevoegroutine( dtBasis, TabellekeOmToeT eVoegen)
'To do for every datatable except the first one

\\\
private sub toevoegroutine( byval dt as datatable, byval Patat as
DataTable)
for each dr as datarow in Patat.rows
dt.importrow(dr Patat)
Next
///

End sub

I hope that is what you mean

Cor

[Cor Ligthert [MVP]]

Pieter,

What is it you use as computer a 'telraam' system.

Can you try this sample and tell me how much milliseconds you was using. If
I did not make an error, than it creates a table with 100 filled columns and
15000 rows by steps from 100 in a time.

\\\
Public Class Main
Public Shared Sub Main()
Dim start As Integer = Environment.Tic kCount
Dim dt As New DataTable
For i As Integer = 0 To 99
dt.Columns.Add( )
Next
For x As Integer = 1 To 100
For i As Integer = 1 To 150
Dim dr As DataRow = dt.NewRow
For j As Integer = 0 To 99
dr(j) = "Pieter"
Next
dt.Rows.Add(dr)
Next
Next
MessageBox.Show ((Environment.T ickCount - start).ToString )
End Sub
End Class
//

:-)

Cor

[DraguVaso]

Werid, it took only 550-650 ticks each time... That's so much less than the
results I got? I even used DataTable.Begin LoadData and EndLoadData...
I guess I'll have to review some stuff, hehe :) Thanks a lot Cor! You did
that hartstikke good!

Was there any reason why you did that in steps of 100 times 150? I didn't
notice any remarkable changes on first sight when changing these parameters?
Should they?

Thanks,

Pieter
"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:u4******** ******@TK2MSFTN GP09.phx.gbl...

Pieter,

What is it you use as computer a 'telraam' system.

Can you try this sample and tell me how much milliseconds you was using.
If I did not make an error, than it creates a table with 100 filled
columns and 15000 rows by steps from 100 in a time.

\\\
Public Class Main
Public Shared Sub Main()
Dim start As Integer = Environment.Tic kCount
Dim dt As New DataTable
For i As Integer = 0 To 99
dt.Columns.Add( )
Next
For x As Integer = 1 To 100
For i As Integer = 1 To 150
Dim dr As DataRow = dt.NewRow
For j As Integer = 0 To 99
dr(j) = "Pieter"
Next
dt.Rows.Add(dr)
Next
Next
MessageBox.Show ((Environment.T ickCount - start).ToString )
End Sub
End Class
//

:-)

Cor

[DraguVaso]

Well thansk Cor, you helped me a lot! In my application, which has a
DataTable with only 2 columns it takes like 50 ticks to merge a DataRow() of
200 records and a DataTable of 12000 records! So really a great performance.

I now have another part that becomes the bottleneck: adding the datatable to
the combobox: It teks easily 850 ticks... any idea to get it better?

Here's my code:

Private Sub AddSearchedToLi st()
Dim dtbl As New DataTable
dtbl = MyWorkSpace.Get AllArticlesClie nt(61).Copy

Dim drows() As DataRow
drows = dtbl.Select("No mArticle LIKE '*HUILE*'", "NomArticle ")

dtbl = Me.InsertDataRo ws(drows, dtbl)

'add to the combo
Dim start As Integer = Environment.Tic kCount
cmbTest.Suspend Layout()
cmbTest.BeginUp date()
Me.cmbTest.Data Source = dtbl 'dtv
Me.cmbTest.Disp layMember = "NomArticle "
Me.cmbTest.Valu eMember = "CodeArticl e"
cmbTest.EndUpda te()
cmbTest.ResumeL ayout()
MessageBox.Show ((Environment.T ickCount - start).ToString ) '-> +- 850
End Sub

Private Function InsertDataRows( ByVal drows() As DataRow, ByVal dtbl2 As
DataTable) As DataTable
Dim dtbl As New DataTable
Dim dr As DataRow
Dim intX, intY As Integer

Dim start As Integer = Environment.Tic kCount

'For intX = 0 To intColumns
' dtbl.Columns.Ad d()
'Next
dtbl.Columns.Ad d("CodeArticle" )
dtbl.Columns.Ad d("NomArticle ")

dtbl.BeginLoadD ata()

'first dtbl1
For intX = 0 To (drows.Length - 1)
dr = dtbl.NewRow
For intY = 0 To 1
dr(intY) = drows(intX).Ite m(intY)
Next
dtbl.Rows.Add(d r)
Next

'add the lines betweeen the 2 datatables...
dr = dtbl.NewRow
dr(1) = " ------------------------"
dtbl.Rows.Add(d r)

'than dtbl2
For intX = 0 To (dtbl2.Rows.Cou nt - 1)
dr = dtbl.NewRow
For intY = 0 To 1
dr(intY) = dtbl2.Rows(intX ).Item(intY)
Next
dtbl.Rows.Add(d r)
Next

dtbl.EndLoadDat a()

MessageBox.Show ((Environment.T ickCount - start).ToString ) '-> +-50

Return dtbl

End Function
"DraguVaso" <pi**********@h otmail.com> wrote in message
news:OT******** ******@TK2MSFTN GP14.phx.gbl...

Werid, it took only 550-650 ticks each time... That's so much less than
the results I got? I even used DataTable.Begin LoadData and EndLoadData...
I guess I'll have to review some stuff, hehe :) Thanks a lot Cor! You did
that hartstikke good!

Was there any reason why you did that in steps of 100 times 150? I didn't
notice any remarkable changes on first sight when changing these
parameters? Should they?

Thanks,

Pieter
"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:u4******** ******@TK2MSFTN GP09.phx.gbl...

Pieter,

What is it you use as computer a 'telraam' system.

Can you try this sample and tell me how much milliseconds you was using.
If I did not make an error, than it creates a table with 100 filled
columns and 15000 rows by steps from 100 in a time.

\\\
Public Class Main
Public Shared Sub Main()
Dim start As Integer = Environment.Tic kCount
Dim dt As New DataTable
For i As Integer = 0 To 99
dt.Columns.Add( )
Next
For x As Integer = 1 To 100
For i As Integer = 1 To 150
Dim dr As DataRow = dt.NewRow
For j As Integer = 0 To 99
dr(j) = "Pieter"
Next
dt.Rows.Add(dr)
Next
Next
MessageBox.Show ((Environment.T ickCount - start).ToString )
End Sub
End Class
//

:-)

Cor

[Cor Ligthert [MVP]]

Pieter,

Why not do it in a way like this?

I know some reasons however I think that I than have a solution for that.

\\\
Dim dtbl As New DataTable
dtbl.Columns.Ad d("CodeArticle" , GetType(System. Int32))
dtbl.Columns.Ad d("NomArticle ")
For i As Integer = 1 To 3
dtbl.LoadDataRo w(New Object() {i, "Huitr1-3"}, True)
Next
For i As Integer = 5 To 10000
dtbl.LoadDataRo w(New Object() {i, "Moules"}, True)
Next
For i As Integer = 10001 To 15000
dtbl.LoadDataRo w(New Object() {i, "HuitrEnd"} , True)
Next
dtbl.DefaultVie w.RowFilter = "NomArticle LIKE '*HUItr*'"
dtbl.DefaultVie w.Sort = "CodeArticl e"
Me.cmbtest.Data Source = dtbl.DefaultVie w 'dtv
Me.cmbtest.Disp layMember = "NomArticle "
Me.cmbtest.Valu eMember = "CodeArticl e"
Dim start As Integer = Environment.Tic kCount
dtbl.LoadDataRo w(New Object() {4, "Huitr4"}, True)
MessageBox.Show ((Environment.T ickCount - start).ToString ) '-> +- 850
///

I hope this helps,

Cor

[Phillip Trelford]

The performance of DataSet Merge is very poor with large numbers of rows.
The reason is that for each column name in the source table must be looked
up against each column name in the destination table for each row.
A better strategy for performance would be to first create a mapping table
for the source to destination columns and then use these when copying the
values from the rows.

Hope this helps,
Phil

P.S. I used Anakrino to disassemble the DataSet Merge code:
http://www.saurik.com/net/exemplar/
"DraguVaso" wrote:

Hi,

I have two DataTables (our DataViews or whatever that will suit the best for
the solution). I want to merge these two DataTables the fastest as possible,
but they have to be merged one table after the others: First all the recors
of DataTable1, and afterwarths the records of DataTable2.

Does anybody has any idea how to do this? The purpose it that it goes as
fast as possible! DataTable2 can have up to 15.000 records, DataTable1
100-200 records. The order of the records is really important: The records
of DataTable1 must come before those of DataTable2, and all of htem has to
be in the order they had orignaly in their DataTable.

Any help would be really appreciated! I tried with InsertAt, Import, Merge
etc, but none of them seemd really quick to mee :-/

Thanks a lot in advance,

Pieter