Databases
StackOverflow2013
Postsdbo
POOptimized Algorithm for matching between two (unsorted) data tables?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimized Algorithm for matching between two (unsorted) data tables?
    primarykey
    data
    text
    <p>I am working on some functionality in my application that queries our database and pulls the data to one datatable, then opens an excel file and populates another datatable. </p> <p>Because the excel file contains no usable ID, I cannot sort the data, and probably cannot use <code>DataTable.Merge()</code>. </p> <p>Here is the code for the matching algorithm I have created.</p> <pre><code> private void RunMatchingAlgorithm() { // Initialize variables string partNumber = ""; DateTime expiration_date = DateTime.Now; decimal contract_cost = 0; string contract_no = ""; string partNumber2 = ""; DateTime expiration_date2 = DateTime.Now; decimal contract_cost2 = 0; string contract_no2 = ""; //Get values from DataBase for (int i = 0; i &lt; dtFromTableContracts.Rows.Count; i++) { partNumber2 = dtFromTableContracts.Rows[i]["supplier_part_no"].ToString(); contract_no2 = dtFromTableContracts.Rows[i]["contract_no"]. expiration_date2 = Convert.ToDateTime(dtFromTableContracts.Rows[i]["con_end_date"]).Date; //Get Values from converted Excel table for (int j = 0; j &lt; dtConversion.Rows.Count; j++) { contract_no = dtConversion.Rows[j]["vend_contract_no"].ToString(); //If we have even a partial match, check for a part number match if (contract_no2.StartsWith(contract_no)) { partNumber = dtConversion.Rows[j]["vend_item_id"].ToString(); //If the values match, populate from both tables if (partNumber == partNumber2) { dtConversion.Rows[j]["wpd_expiration_date"] = expiration_date2.Date; dtConversion.Rows[j]["wpd_cont_cost"] = dtFromTableContracts.Rows[i]["contract_cost"]; dtConversion.Rows[j]["wpd_contract_no"] = dtFromTableContracts.Rows[i]["contract_no"]; dtConversion.Rows[j]["wpd_item_id"] = dtFromTableContracts.Rows[i]["supplier_part_no"]; dtConversion.Rows[j]["wpd_item_no"] = dtFromTableContracts.Rows[i]["item_id"]; dtConversion.Rows[j]["discontinued"] = dtFromTableContracts.Rows[i]["discontinued"]; dtConversion.Rows[j]["job_no"] = dtFromTableContracts.Rows[i]["job_no"]; } } } } } </code></pre> <p>If you're curious, a later method removes any unmatched lines and we display only the matched records in a DGV.</p> <p>This currently works as expected but if my Big O notation is correct, i'm dealing with O(m*n) which gets quite slow with larger data sets, and is extremely processor intensive. </p> <p>I am looking for a more efficient way to accomplish this than looping over every single row as some of the excel spreadsheets we work with are close to 40,000 rows. This algorithm takes about 6 minutes to complete with that size of a set.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSoner Gönül
    1. USEvan L
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COOf course you can sort - just sort on *all* the fields rather than just one.
      singulars
      1. POOptimized Algorithm for matching between two (unsorted) data tables?
      1. USMark Ransom
    2. CO@MarkRansom this would still produce slower than linear algorithm as sorting itself can be slow
      singulars
      1. POOptimized Algorithm for matching between two (unsorted) data tables?
      1. USSten Petrov
    3. CONobody is asking for a linear algorithm. But using a sorted structure to find matching `contract_no` is faster than this approach. Note: Sorting is O(n log n)
      singulars
      1. POOptimized Algorithm for matching between two (unsorted) data tables?
      1. USalzaimar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload