Search code examples
c#performancelinqdatatabledatarow

How do you correctly and quickly compare Datarows / Datatables?

Update: to explain what type of Datatables I Am comparing- "Compare two Datatables with the same columns, one Datatable is being pulled form an external server and inserted Initially, from then onwards only last 6 months of records are pulled off external database (for various reasons) , and the data is compared with local data (for a 6 month date range) to see if a DataRow has changed,needs to be deleted or added where the Row Identifier (PKey)which is essentially the SalesID + LineRow match and other columns are values to be compared to see if that row needs to be re-added/deleted because incoming columns are different to current columns and also delete rows where the incoming data does not contain those rows

so basically I want a Exclusive Left Join [to insert that data] and Exclusive Right Join [to delete that data] "

I have been doing some database coding as well as JSON pulling and I want to know what is the standard way / correct way of doing things , I started with 2 hour compare times (on dummy DB table) down to 1 hour to 1 secs (after applying my janky method to DB table Compare) and then finally used it on live pull with what seems to be correct and consistent results and so I started doing testing on Dummy data with it going from 1 hour to 26 minutes to finally <1 seconds (using my own janky way), Table sizes tested and assumed are between 100,000 & 200,000 rows so lets go through the standard methods I tried and then go onto the janky solution I Made.

The first and obvious idea was to use two ForEach iterations (even mentally this seemed it would be slow however I thought it wouldn't be that bad considering how quick Add is, and how quickly you can compare JSON Tokens when iterating through Jarrays). Code was something like the following:

DataTable dtQueryItemsDiff = dtItems.Clone();
DataTable dtItemsDiff = dtItems.Clone();

int maxRowCountCache = dtItems.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name")).Count();
int rowcountCCache = 0;
var query = dtQuery.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name"));
foreach (DataRow drDTI in dtItems.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name")))
{
    int innerrowcount = 0;
    bool rowfound = false;
    if (query.Count() != 0)
    {
        foreach (DataRow drDTQ in query)
        {
            if (drDTI["SalesID"].ToString() == drDTI["SalesID"].ToString() && drDTI["LineNumber"].ToString() == drDTI["LineNumber"].ToString())
            {
                rowfound = true;
                break;
            }
            innerrowcount++;
        }
    }
    else
    {
        dtItemsDiff.ImportRow(drDTI);
        continue;
    }
    if (rowfound == true)
    {
        orderedDtquery.ElementAt(innerrowcount).Delete();
    }
    else
    {
        dtItemsDiff.ImportRow(drDTI);
    }
    rowcountCCache++;
    BeginInvoke(new MethodInvoker(delegate
    {
        lblDataLoadC.Text = rowcountCCache.ToString() + " / " + maxRowCountCache.ToString();
    }));
}
if (query.Count() != 0)
{
    foreach (DataRow drDTQ in query)
    {
        dtQueryItemsDiff.ImportRow(drDTQ);
    }
}

This took quite a long time about an 1H (1 Hour) to 1.5H depending on data how it was ordered etc. The benefit was that I could granularly change the code and it gave me Non-matched data in both tables, also it reduced query size searched but this wasn't fast enough for me so then I tried Linq searching where I didn't reduce list size as a I went (it was slower to delete then search then it was to just search) and this took about ~40-50 mins and looked like:

int maxRowCountCache = dtItems.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name")).Count();
int rowcountCCache = 0;
dtItems.AcceptChanges();
foreach (DataRow drDTI in dtItems.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name")))
{
    var checkIfRecordInIDB = progSettings.query.AsEnumerable().Where(row => row.Field<string>("CardRecordID") == drDTI["CardRecordID"].ToString()
    && row.Field<string>("Date") == drDTI["Date"].ToString() && row.Field<string>("SaleID") == drDTI["SaleID"].ToString()
    && row.Field<string>("ItemID") == drDTI["ItemID"].ToString() && row.Field<Int64>("LineNumber") == Convert.ToInt64(drDTI["LineNumber"].ToString())).FirstOrDefault();
    if (checkIfRecordInIDB != null)
    {
        drDTI.Delete();
    }
    rowcountCCache++;
    BeginInvoke(new MethodInvoker(delegate
    {
        lblDataLoadC.Text = rowcountCCache.ToString() + " / " + maxRowCountCache.ToString();
    }));
}
dtItems.AcceptChanges();

Benefit of this is its slightly more lazy, faster and concise, however it only gives you data in one table much like Except does and that is exactly what I tried next using ~100,000 rows of dummy data, this took 26 minutes and 35 seconds.

dtItems.Rows.Clear();
query.Rows.Clear();
Thread start = new Thread(timerAndUIupdate);
start.Start();

dtItems.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "72421ee8-459b-46fb-bf5a-f51e80976e5a", "Pioneer 1kg (FT), RRP $42", "100115", 1, 25.0, "N");
dtItems.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "8885a911-8d32-4dfe-93e5-2e453fd54db9", "Decaf Beans 250g FT", "1002302", 2, 2.0, "N");
dtItems.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "e3aa4b15-b774-4f6a-ac21-77fa05a4332f", "P&R Cups 06oz (1000)", "30056", 3, 1.0, "N");
dtItems.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "51e1a867-4079-4a3c-9ddc-e93d87d80b46", "P&R Cups 12oz (1000)", "30058", 4, 1.0, "N");
query.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "72421ee8-459b-46fb-bf5a-f51e80976e5a", "Pioneer 1kg (FT), RRP $42", "100115", 1, 25.0, "N");
query.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "8885a911-8d32-4dfe-93e5-2e453fd54db9", "Decaf Beans 250g FT", "1002302", 2, 2.0, "N");
query.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "e3aa4b15-b774-4f6a-ac21-77fa05a4332f", "P&R Cups 06oz (1000)", "30056", 3, 1.0, "N");
query.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "51e1a867-4079-4a3c-9ddc-e93d87d80b46", "P&R Cups 12oz (1000)", "30058", 4, 1.0, "N");
for (int i = 1; i < 100000; i++)
{
    dtItems.Rows.Add("Bennett St Dairy", "ed0c8d30-6469-4e13-af5a-36d7357a4a70", "2019-07-01", "2019-07-01", "8b909a4b-a07b-4a06-bebc-6a3387433aaf", "c8cc1115-da02-42cf-b427-accc1b6d07e3", "Trailblazer 1Kg, RRP $44", "10011", i, (i * 4), "N");
    query.Rows.Add("Bennett St Dairy", "ed0c8d30-6469-4e13-af5a-36d7357a4a70", "2019-07-01", "2019-07-01", "8b909a4b-a07b-4a06-bebc-6a3387433aaf", "c8cc1115-da02-42cf-b427-accc1b6d07e3", "Trailblazer 1Kg, RRP $44", "10011", i, (i * 4), "N");
}
dtItems.Rows.Add("Air Coffee International Cafe Pty Ltd", "bb4fa724-9759-4c60-93fe-70fbdfd00417", "2019-07-01", "2019-07-01", "b972f020-3740-4ef2-941f-78b1a9edefa8", "0be54733-ac0e-43f9-8ea5-204c7cdb5f48", "Custom 1kg", "100116", 1, 4.0, "N");
dtItems.Rows.Add("Allure Cafe & Co.", "f76f383f-e9f4-45c9-bb93-81102629b9c3", "2019-07-01", "2019-07-01", "2ad0667f-2254-4df5-8b24-eb36736cabb0", "6edc584b-a8eb-4f0b-a449-dbcb76a40a24", "Porter St 1Kg, RRP $40", "100111", 1, 10.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "6edc584b-a8eb-4f0b-a449-dbcb76a40a24", "Porter St 1Kg, RRP $40", "100111", 1, 30.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "51e1a867-4079-4a3c-9ddc-e93d87d80b46", "P&R Cups 12oz (1000)", "30058", 2, 12.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "401ce902-e158-4f21-85a5-3312c32457fc", "Lids 06/08/12oz (White) (1000)", "30062", 3, 7.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "9b80c825-6e9f-4f6b-9c77-f3378cc220e4", "4-Cup Cardboard Holders (300)", "41003", 4, 1.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "ea4c906e-fab1-4b15-8845-619f20e53c6a", "Organic Panela 1kg", "20014", 5, 2.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "bb3e1c10-9e67-46d3-99b4-17df45dead90", "Chocolate Powder 1Kg, RRP $25", "20034", 6, 1.0, "N");

query.Rows.Add("Aussie Bites Cafe", "30389aca-9089-4b37-9a1e-5fbc3c2af485", "2019-07-01", "2019-07-01", "85df1af6-3d1e-4e04-8fe9-d90462a59d4c", "ea89ade4-c7ff-4d79-abcd-dcdbb8122562", "X Blend 1Kg, RRP $40", "100112", 1, 4.0, "N");
query.Rows.Add("Aussie Bites Cafe", "30389aca-9089-4b37-9a1e-5fbc3c2af485", "2019-07-01", "2019-07-01", "85df1af6-3d1e-4e04-8fe9-d90462a59d4c", "21fe57ad-08f9-4c8b-81d0-d7b88b291571", "webfreight", "webfreight", 2, 1.0, "N");
query.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "6edc584b-a8eb-4f0b-a449-dbcb76a40a24", "Porter St 1Kg, RRP $40", "100111", 1, 30.0, "N");
query.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "51e1a867-4079-4a3c-9ddc-e93d87d80b46", "P&R Cups 12oz (1000)", "30058", 2, 1.0, "N");
query.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "401ce902-e158-4f21-85a5-3312c32457fc", "Lids 06/08/12oz (White) (1000)", "30062", 3, 2.0, "N");
query.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "9b80c825-6e9f-4f6b-9c77-f3378cc220e4", "4-Cup Cardboard Holders (300)", "41003", 4, 1.0, "N");
Stopwatch pullTime = new();
pullTime.Start();

BeginInvoke(new MethodInvoker(delegate
{
    lblTimerAddRowEnd.Text = "Start Time,Except: " + pullTime.Elapsed.ToString("mm\\:ss\\.ff");
}));

var orderedDtItems = dtItems.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name"));
var orderedDtquery = query.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name"));
DataTable excepteditems = orderedDtItems.Except(orderedDtquery, DataRowComparer.Default).CopyToDataTable();

BeginInvoke(new MethodInvoker(delegate
{
    labelControl1.Text = "End Time,Except: " + pullTime.Elapsed.ToString("mm\\:ss\\.ff");
}));

BeginInvoke(new MethodInvoker(delegate
{
    dgvResults.DataSource = excepteditems;
    btnStart.Enabled = true;
    simpleButton1.Enabled = true;
}));

With this updater code for UI (this was threaded and used for all test comparisons):

private void timerAndUIupdate()
{
    Stopwatch pullTime = new();
    pullTime.Start();
    do
    {
        Thread.Sleep(500);
        BeginInvoke(new MethodInvoker(delegate
        {
            lblTimer.Text = "Timer: " + pullTime.Elapsed.ToString("mm\\:ss\\.ff");
            Application.DoEvents();
        }));
    } while (btnStart.Enabled == false);
    pullTime.Stop();
    BeginInvoke(new MethodInvoker(delegate
    {
        lblTimer.Text = "Timer: " + pullTime.Elapsed.ToString("mm\\:ss\\.ff");
        Application.DoEvents();
    }));
}

And results on Winforms looked like this:

Winforms Window

Then I did my janky way and the results were very fast and seemed quite accurate, and because it only took a fractions of a second I could perform this multiple times to get, New rows , Old rows not in pull that should not be deleted and Old Rows that should be Deleted -> code looked like this

dtItems.Rows.Clear();
query.Rows.Clear();
Thread start = new Thread(timerAndUIupdate);
start.Start();

      
dtItems.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "72421ee8-459b-46fb-bf5a-f51e80976e5a", "Pioneer 1kg (FT), RRP $42", "100115", 1, 25.0, "N");
dtItems.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "8885a911-8d32-4dfe-93e5-2e453fd54db9", "Decaf Beans 250g FT", "1002302", 2, 2.0, "N");
dtItems.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "e3aa4b15-b774-4f6a-ac21-77fa05a4332f", "P&R Cups 06oz (1000)", "30056", 3, 1.0, "N");
dtItems.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "51e1a867-4079-4a3c-9ddc-e93d87d80b46", "P&R Cups 12oz (1000)", "30058", 4, 1.0, "N");
query.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "72421ee8-459b-46fb-bf5a-f51e80976e5a", "Pioneer 1kg (FT), RRP $42", "100115", 1, 25.0, "N");
query.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "8885a911-8d32-4dfe-93e5-2e453fd54db9", "Decaf Beans 250g FT", "1002302", 2, 2.0, "N");
query.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "e3aa4b15-b774-4f6a-ac21-77fa05a4332f", "P&R Cups 06oz (1000)", "30056", 3, 1.0, "N");
query.Rows.Add("4 Beans Cafe", "2af0f4bf-52ea-44fb-b1b3-36181fe7bfdf", "2019-07-01", "2019-07-01", "7fc4f98a-35af-4da3-afe3-f7cfcd922ea7", "51e1a867-4079-4a3c-9ddc-e93d87d80b46", "P&R Cups 12oz (1000)", "30058", 4, 1.0, "N");
for (int i = 1; i < 100000; i++)
{
    dtItems.Rows.Add("Bennett St Dairy", "ed0c8d30-6469-4e13-af5a-36d7357a4a70", "2019-07-01", "2019-07-01", "8b909a4b-a07b-4a06-bebc-6a3387433aaf", "c8cc1115-da02-42cf-b427-accc1b6d07e3", "Trailblazer 1Kg, RRP $44", "10011", i, (i * 4), "N");
    query.Rows.Add("Bennett St Dairy", "ed0c8d30-6469-4e13-af5a-36d7357a4a70", "2019-07-01", "2019-07-01", "8b909a4b-a07b-4a06-bebc-6a3387433aaf", "c8cc1115-da02-42cf-b427-accc1b6d07e3", "Trailblazer 1Kg, RRP $44", "10011", i, (i * 4), "N");
}
dtItems.Rows.Add("Air Coffee International Cafe Pty Ltd", "bb4fa724-9759-4c60-93fe-70fbdfd00417", "2019-07-01", "2019-07-01", "b972f020-3740-4ef2-941f-78b1a9edefa8", "0be54733-ac0e-43f9-8ea5-204c7cdb5f48", "Custom 1kg", "100116", 1, 4.0, "N");
dtItems.Rows.Add("Allure Cafe & Co.", "f76f383f-e9f4-45c9-bb93-81102629b9c3", "2019-07-01", "2019-07-01", "2ad0667f-2254-4df5-8b24-eb36736cabb0", "6edc584b-a8eb-4f0b-a449-dbcb76a40a24", "Porter St 1Kg, RRP $40", "100111", 1, 10.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "6edc584b-a8eb-4f0b-a449-dbcb76a40a24", "Porter St 1Kg, RRP $40", "100111", 1, 30.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "51e1a867-4079-4a3c-9ddc-e93d87d80b46", "P&R Cups 12oz (1000)", "30058", 2, 12.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "401ce902-e158-4f21-85a5-3312c32457fc", "Lids 06/08/12oz (White) (1000)", "30062", 3, 7.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "9b80c825-6e9f-4f6b-9c77-f3378cc220e4", "4-Cup Cardboard Holders (300)", "41003", 4, 1.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "ea4c906e-fab1-4b15-8845-619f20e53c6a", "Organic Panela 1kg", "20014", 5, 2.0, "N");
dtItems.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "bb3e1c10-9e67-46d3-99b4-17df45dead90", "Chocolate Powder 1Kg, RRP $25", "20034", 6, 1.0, "N");
        
query.Rows.Add("Aussie Bites Cafe", "30389aca-9089-4b37-9a1e-5fbc3c2af485", "2019-07-01", "2019-07-01", "85df1af6-3d1e-4e04-8fe9-d90462a59d4c", "ea89ade4-c7ff-4d79-abcd-dcdbb8122562", "X Blend 1Kg, RRP $40", "100112", 1, 4.0, "N");
query.Rows.Add("Aussie Bites Cafe", "30389aca-9089-4b37-9a1e-5fbc3c2af485", "2019-07-01", "2019-07-01", "85df1af6-3d1e-4e04-8fe9-d90462a59d4c", "21fe57ad-08f9-4c8b-81d0-d7b88b291571", "webfreight", "webfreight", 2, 1.0, "N");
query.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "6edc584b-a8eb-4f0b-a449-dbcb76a40a24", "Porter St 1Kg, RRP $40", "100111", 1, 30.0, "N");
query.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "51e1a867-4079-4a3c-9ddc-e93d87d80b46", "P&R Cups 12oz (1000)", "30058", 2, 1.0, "N");
query.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "401ce902-e158-4f21-85a5-3312c32457fc", "Lids 06/08/12oz (White) (1000)", "30062", 3, 2.0, "N");
query.Rows.Add("Mad Hatter Wine Co", "49340e5f-c7ef-41d9-9f1b-200711e6e629", "2021-07-28", "2021-07-28", "e16cbbac-c319-45f3-ac53-89d979fbcdc1", "9b80c825-6e9f-4f6b-9c77-f3378cc220e4", "4-Cup Cardboard Holders (300)", "41003", 4, 1.0, "N");

Stopwatch pullTime = new();
pullTime.Start();

BeginInvoke(new MethodInvoker(delegate
{
    lblTimerAddRowEnd.Text = "Start Time,Except: " + pullTime.Elapsed.ToString("mm\\:ss\\.ff");
}));
var orderedDtItems = dtItems.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name"));
var orderedDtquery = query.AsEnumerable().OrderBy(row => Convert.ToDateTime(row.Field<String>("Date"))).ThenBy(row => row.Field<String>("Name"));
      
dtOnlyNewRows.Rows.Clear();
HashSet<String> orderedDtItemsHS = new();
HashSet<String> orderedDtqueryHS = new();
HashSet<String> orderedDtItemsHSRemains = new();
HashSet<String> orderedDtqueryHSRemains = new();
foreach (DataRow dr in orderedDtquery)
{
    orderedDtqueryHSRemains.Add(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    orderedDtqueryHS.Add(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
}
foreach (DataRow dr in orderedDtItems)
{
    orderedDtItemsHSRemains.Add(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    orderedDtItemsHS.Add(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    bool added = orderedDtqueryHSRemains.Add(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    if (added == false)
    {
        orderedDtqueryHSRemains.Remove(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    }
    else if (added == true)
    {
        dtOnlyNewRows.ImportRow(dr);
        orderedDtqueryHSRemains.Remove(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    }
}

foreach (DataRow dr in orderedDtquery)
{
    bool added = orderedDtItemsHSRemains.Add(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    if (added == false)
    {
        orderedDtItemsHSRemains.Remove(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    }
    else if (added == true)
    {
        DateTime rowTime = Convert.ToDateTime(dr["date"].ToString());
        if (rowTime <= MonthCutOff)
        {
            dtOnlyLeftoverRows.ImportRow(dr);
        }
        else
        {
            dtOnlyDeleteRows.ImportRow(dr);
        }
        orderedDtItemsHSRemains.Remove(dr["CardRecordID"].ToString() + "⌁" + dr["Date"].ToString() + "⌁" + dr["SaleID"].ToString() + "⌁" + dr["ItemID"].ToString()
        + "⌁" + dr["LineNumber"].ToString() + "⌁" + dr["Quantity"].ToString());
    }
}

Debug.WriteLine(dtOnlyNewRows.Rows.Count.ToString());
BeginInvoke(new MethodInvoker(delegate
{
    labelControl1.Text = "End Time,Except: " + pullTime.Elapsed.ToString("mm\\:ss\\.ff");
}));
pullTime.Stop();
BeginInvoke(new MethodInvoker(delegate
{
    dgvRowsRemaing.DataSource = dtOnlyLeftoverRows;
    dgvResults.DataSource = dtOnlyNewRows;
    dgvDeleteRows.DataSource = dtOnlyDeleteRows;
    btnStart.Enabled = true;
}));

And the end result looked as follows:

Winforms Window

After all this explanation comes my questions:

  1. What did I do wrong in the other methods, can they be made faster?
  2. How should I compare Datatables, if my janky methods is not OK?
  3. As long as it works and is fast even if janky, is it OK?
  4. What are the possible problems with my janky method?

Edited: 2021-08-03 11:25 PM AEST(Australian Eastern Standard Time)

the Code Juris wrote is Neater and Much Quicker, What it looks like when applied to my dummy data Windows Forms

3 x quicker , less messy code, way shorter this was exactly what I was looking for thank you

Solution

  • I'd do this by indexing the datatables with a pair of dictionaries. DataTable can have primary keys defined and perform fast lookups that use dictionaries internally but in general working with datatables is pretty ugly stuff so no point adding to it with more PK ugly

    So we have some datatable on the right, and it's downlaoded from a DB, and you have decided that the "Foo" and "Bar" columns are the PK. Foo is a string, Bar is an int:

    Dim rIndex = new Dictionary(Of (ValueTuple(Of String, Integer), DataRow)

For Each r as DataRow In rightDt.Rows
  Dim key = ( r.Field(Of String)("Foo"), r.Field(Of Integer)("Bar") )
  rIndex(key) = r
Next r

    And we have some file that's been read into the left datatable. The file's columns happen to be called Wit (string) and Woo (int)

    Dim lIndex = new Dictionary(Of (ValueTuple(Of String, Integer), DataRow)

For Each r as DataRow In leftDt.Rows
  Dim key = (r.Field(Of String)("Wit"), r.Field(Of Integer)("Woo") )
  lIndex(key) = r
Next r

    Now it's probably going to make life easy if we stash the keys into a hashset too as we go; this represents the union of the left and the right

    Dim allKeys as New HashSet(Of ValueTuple(Of String, Integer))

Dim rIndex = new Dictionary(Of (ValueTuple(Of String, Integer), DataRow)

For Each r as DataRow In rightDt.Rows
  Dim key = ( r.Field(Of String)("Foo"), r.Field(Of Integer)("Bar") )
  rIndex(key) = r
  allKeys.Add(key)
Next r

Dim lIndex = new Dictionary(Of (ValueTuple(Of String, Integer), DataRow)

For Each r as DataRow In leftDt.Rows
  Dim key = (r.Field(Of String)("Wit"), r.Field(Of Integer)("Woo") )
  lIndex(key) = r
  allKeys.Add(key)
Next r

    All that remains is to enumerate the allKeys and ask the dictionaries whether they contain it or not and decide what to do

    For Each k in allKeys
  Dim inL = lIndex.ContainsKey(k)
  Dim inR = rIndex.ContainsKey(k)

  If inL AndAlso inR Then
    Dim updateRo = lIndex(k) 'update the db using this datarow
    ...
  ElseIf inL Then
    Dim insertRo = lIndex(k) 'insert this row to the db
    ...
  Else
    Dim deleteRo = rIndex(k) 'delete this row from the db
    ...
  End If
Next k

    --

    Hah, just realized my brain is still in VB mode. Here's the C# version of the above:

    var allKeys = new HashSet<(string, int)>();

var rIndex = new Dictionary<(string, int), DataRow>();

foreach(DataRow r in rightDt.Rows){
  var key = (r.Field<string>("Foo"), r.Field<int>("Bar"));
  rIndex[key] = r;
  allKeys.Add(key);
}

var lIndex = new Dictionary<(string, int), DataRow>();

foreach(DataRow r in leftDt.Rows){
  var key = (r.Field<string>("Wit"), r.Field<int>("Woo"));
  lIndex[key] = r;
  allKeys.Add(key);
}

foreach(var k in allKeys){
  var inL = lIndex.ContainsKey(k);
  var inR = rIndex.ContainsKey(k);

  if(inL && inR){
    var updateRo = lIndex[k]; //update the db using this datarow
    ...
  } else if(inL){
    var insertRo = lIndex[k]; //insert this row to the db
    ...
  } else {
    var deleteRo = rIndex[k]; //delete this row from the db
    ...
  }
}

    You can see a working sample at https://dotnetfiddle.net/3jfrPl