• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2696
  • Last Modified:

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.

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

Open in new window

  • 2
  • 2
1 Solution
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.
azyet24Author 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
azyet24Author 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now