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

  • Thread starter Thread starter DraguVaso
  • Start date Start date
D

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
 
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
 
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?
 
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 = DtWaarMeeTeStarten.Copy
toevoegroutine(dtBasis, TabellekeOmToeTeVoegen)
'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(drPatat)
Next
///

End sub

I hope that is what you mean

Cor
 
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 :-)
 
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.TickCount
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.TickCount - start).ToString)
End Sub
End Class
//

:-)

Cor
 
Werid, it took only 550-650 ticks each time... That's so much less than the
results I got? I even used DataTable.BeginLoadData 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
 
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 AddSearchedToList()
Dim dtbl As New DataTable
dtbl = MyWorkSpace.GetAllArticlesClient(61).Copy

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

dtbl = Me.InsertDataRows(drows, dtbl)

'add to the combo
Dim start As Integer = Environment.TickCount
cmbTest.SuspendLayout()
cmbTest.BeginUpdate()
Me.cmbTest.DataSource = dtbl 'dtv
Me.cmbTest.DisplayMember = "NomArticle"
Me.cmbTest.ValueMember = "CodeArticle"
cmbTest.EndUpdate()
cmbTest.ResumeLayout()
MessageBox.Show((Environment.TickCount - 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.TickCount

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

dtbl.BeginLoadData()

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

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

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

dtbl.EndLoadData()

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

Return dtbl

End Function
 
Pieter,
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?
Click to expand...

To show that it even with 100 tables instead of 2 would go quick.

Cor
 
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.Add("CodeArticle", GetType(System.Int32))
dtbl.Columns.Add("NomArticle")
For i As Integer = 1 To 3
dtbl.LoadDataRow(New Object() {i, "Huitr1-3"}, True)
Next
For i As Integer = 5 To 10000
dtbl.LoadDataRow(New Object() {i, "Moules"}, True)
Next
For i As Integer = 10001 To 15000
dtbl.LoadDataRow(New Object() {i, "HuitrEnd"}, True)
Next
dtbl.DefaultView.RowFilter = "NomArticle LIKE '*HUItr*'"
dtbl.DefaultView.Sort = "CodeArticle"
Me.cmbtest.DataSource = dtbl.DefaultView 'dtv
Me.cmbtest.DisplayMember = "NomArticle"
Me.cmbtest.ValueMember = "CodeArticle"
Dim start As Integer = Environment.TickCount
dtbl.LoadDataRow(New Object() {4, "Huitr4"}, True)
MessageBox.Show((Environment.TickCount - start).ToString) '-> +- 850
///

I hope this helps,

Cor
 
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/
 
Back
Top