We help IT Professionals succeed at work.

Compare DataTables

azyet24
azyet24 asked
on
Medium Priority
2,707 Views
Last Modified: 2008-01-26
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.


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

Open in new window

Comment
Watch Question

have not you heared about sql yet ???


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.

Author

Commented:
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.board 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.board
from a,b
where a.mln = b.mln
and a.status <> 'A';

take care of the fields data types. a.mln should be the same type of b.mln

waiting for your reply

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.