azyet24
asked on
Compare DataTables
I have two tables in a MySQL database and I am filling a DataTable for each table. I then am trying to compare them. I am not getting any errors, but it continues to run and after an hour I have no output so I'm wondering if there is a better way. Table 1 (masterlist) has around 250,000 records and table 2 has 17,000 records so there is alot to compare!
Master list (table 1) has all the records that is in table 2 (and even more) but they have different statuses (such as sold or whatnot) for some records and I want to look through them and find all that do not have a status of A. (All of table 2 has a status of A, so I want to find the records in table 1 that doesn't have status A which will indicate that I need to do something). Below is my loops.
Master list (table 1) has all the records that is in table 2 (and even more) but they have different statuses (such as sold or whatnot) for some records and I want to look through them and find all that do not have a status of A. (All of table 2 has a status of A, so I want to find the records in table 1 that doesn't have status A which will indicate that I need to do something). Below is my loops.
For j As Integer = 0 To dt.Rows.Count - 1 ' Table 2 Used inventory to check. DataTable has two columns
Triomln = dt.Rows(j).ItemArray(0).ToString().Trim
For i As Integer = 0 To dt2.Rows.Count - 1 'Table 1 Total Inventory List (master list). Datatable has 2 columns
mln = dt2.Rows(i).ItemArray(0).ToString().Trim
strstatus = dt2.Rows(i).ItemArray(1).ToString().Trim
If mln = Triomln Then
If strstatus <> "A" Then
ListBox2.Items.Add(mln & " " & strstatus)
End If
End If
Next
Next
ASKER
Yes, I initially try it with just a sql statement but it basically froze up my database..perhaps I had my sql wrong. Here's what I tried:
Select a.mln,a.status,b.mln,b.boa rd from table1 a left join table 2 b on a.mln = b.mln where a.status <> 'A';
Are there better methods?
Select a.mln,a.status,b.mln,b.boa
Are there better methods?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Datatypes were different for mln and that was causing the drag. You sql worked perfectly.
One last question if you don't mind. How can you output the process as the actions are taking place? Right now it processes the request after the process is completed then the output fills the listbox. I'd like to see what is happening similar to what you'd see on console.
One last question if you don't mind. How can you output the process as the actions are taking place? Right now it processes the request after the process is completed then the output fills the listbox. I'd like to see what is happening similar to what you'd see on console.
such work is not ment to be done using teh programming anguage.
post a diagram for the 2 tables , or just state thier columns . so we can write a single sql statement that will return the result in 0.00005 sec.
try reading more in sql.
waiting for your reply.