fasted way to merge

[timsteeman]

I have two csv files.
i have put them into 2 tables.

Both files contain something like 5000 records.
The first field of both files are unique. (mostly the same number, but not all records correspondent)

I want to lookup the keyfield of table a in table B. And if the system finds it, copy somefields from table B to table A.
What is the fastest way to do this.

I use the code beneath, and it takes 10 minutes to do the job on the PC.
Is there are better way?


sub Button1_Click
reader.New1
pb.New1("form1",500,45,100,25)
label1.Text=Time(Now)
table1.LoadCSV(listbox1.Item(0),";",True,True)
table2.LoadCSV(listbox1.Item(1),";",True,True)
table1.AddCol(cString,"lsktonr",30)
table1.AddCol(cString,"lhktonr",30)
table1.AddCol(cString,"bzart",30)
table1.AddCol(cString,"bzdat",30)
table1.TableSort("CNR asc")
table2.TableSort("CNR asc")
Con.CreateSQLTable("table1","table1")
Con.CreateSQLTable("table2","table2")
For a=0 To table1.RowCount-1
If table1.Cell("cnr",a)<>"" Then
cmd.commandText="SELECT lsktonr,lhktonr,bzart,bzdat FROM table2 WHERE cnr='"&table1.Cell("cnr",a)&"'"
Reader.Value = cmd.ExecuteReader
verwerk(table1.Cell("cnr",a),Reader.GetValue(0),Reader.GetValue(1),Reader.GetValue(2),Reader.GetValue(3))
End If
label2.text=a
Next
cmd.CommandText = "SELECT * FROM table1"
cmd.ExecuteTable("table3",0)
table3.Visible=True
label2.Text=Time(Now)
End Sub
----------
Sub verwerk(cnr,lskontonr,lhkontonr,bzart,bzdat)
Reader.Close
Con.BeginTransaction
cmd.AddParameter("0")
cmd.AddParameter("1")
cmd.AddParameter("2")
cmd.AddParameter("3")
cmd.AddParameter("4")
cmd.SetParameter("0" ,cnr)
cmd.SetParameter("1" ,lskontonr)
cmd.SetParameter("2" ,lhkontonr)
cmd.SetParameter("3", bzart)
cmd.SetParameter("4", bzdat)
cmd.CommandText = "UPDATE table1 SET lsktonr=@1, lhktonr=@2, bzart=@3, bzdat=@4 WHERE cnr=@0"
cmd.ExecuteNonQuery
Con.EndTransaction
End Sub-----------

 

[Erel]

It shouldn't take so long. Can you upload your complete program with the files?

 

[timsteeman]

sample files

Hi Erel,
Included are the sample files.
I try to convert some old database tables with very much not needed bulk.
I would be very pleased if you could take a look at it.

Tim

 

[Erel]

The first improvement is to have only one BeginTransacion/EndTransaction:

    Con.BeginTransaction
    For a=0 To table1.RowCount-1
        If table1.Cell("cnr",a)<>"" Then
            cmd.commandText="SELECT lsktonr,lhktonr,bzart,bzdat FROM table2 WHERE cnr='"&table1.Cell("cnr",a)&"'"
            Reader.Value = cmd.ExecuteReader
            verwerk(table1.Cell("cnr",a),Reader.GetValue(0),Reader.GetValue(1),Reader.GetValue(2),Reader.GetValue(3))
        End If
    label2.text=a
    Next
    Con.EndTransaction

And remove the transaction calls in sub verwerk.
This change makes it run 10 times faster.

 

[timsteeman]

thank you!!

:icon_clap::icon_clap::icon_clap::icon_clap:
Dear Erel,
Your reply was, as ever, good and very to the point.
Works like charme.
Thank you very much.
Tim