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

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
Mon Oct 17 09:09:44 CDT 2005

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
Mon Oct 17 09:26:25 CDT 2005

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]" <notmyfirstname@planet.nl> wrote in message
news:uVsCTSy0FHA.1108@TK2MSFTNGP14.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
Mon Oct 17 09:49:42 CDT 2005

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

DraguVaso
Mon Oct 17 10:05:33 CDT 2005

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]" <notmyfirstname@planet.nl> wrote in message
news:eRo7noy0FHA.1264@tk2msftngp13.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 = 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
>

Cor
Mon Oct 17 10:44:56 CDT 2005

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

DraguVaso
Tue Oct 18 05:07:01 CDT 2005

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


"Cor Ligthert [MVP]" <notmyfirstname@planet.nl> wrote in message
news:u4cefHz0FHA.2884@TK2MSFTNGP09.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.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
>
>
>

DraguVaso
Tue Oct 18 05:43:15 CDT 2005

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


"DraguVaso" <pietercoucke@hotmail.com> wrote in message
news:OTQKwu80FHA.3180@TK2MSFTNGP14.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.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
>
>
> "Cor Ligthert [MVP]" <notmyfirstname@planet.nl> wrote in message
> news:u4cefHz0FHA.2884@TK2MSFTNGP09.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.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
>>
>>
>>
>
>

Cor
Wed Oct 19 03:39:58 CDT 2005

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?

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

Cor

Cor
Wed Oct 19 04:12:04 CDT 2005

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

PhillipTrelford
Sun Oct 23 10:33:02 CDT 2005

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
>
>
>