Databases
StackOverflow2013
Postsdbo
POwhat is the fastest way to find similar records from different (big) data sources
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POwhat is the fastest way to find similar records from different (big) data sources
    primarykey
    data
    text
    <p>I work for a public health agency that has lots of different demographic datasets--stored in SQL sever, Access and Excel. I've written an application that allows people to find 'matches' in those datasets based on different criteria, set up with a GUI. For instance, one 'match' might be that the First, Last and DOB match in both datasets--but the SSN is 'off by 1' (determined by the Levenshtein algorithm). </p> <p>These are big datasets. The matching criteria can get really complex. Right now, I find matches by pulling both datasets into data tables in memory and then going row-by-row through the first table and seeing if there are any rows in the second table that match (using LINQ). So my code looks something like: </p> <pre><code>For each table1Row in TableOne/DatasourceOne table2Options=from l in table2rows where Levenshtein(table1Row.first, l.first)&lt;2 //first name off by one table2Options=from l in table2rows where Levenshtein(table1Row.last, l.last)&lt;2 //last name off by one if table2Options.count&gt;1 then the row in table1 'matches' table 2 Next </code></pre> <p>The code produces the correct output (finds matches) but it is SLOOOW. I know that going row-by-row is supposed to be slower--but using LINQ to find all the records all at once goes even slower. </p> <pre><code>From l in table1, k in table2 where Levenshtein(l.first, k.first)&lt;2 and Levenshtein(l.last, k.last)&lt;2 select l //this takes forever because it calculates the function for l rows * k rows </code></pre> <p>Any ideas on how to do this core matching faster? </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USbernie2436
    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. VO
      singulars
      1. POwhat is the fastest way to find similar records from different (big) data sources
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POwhat is the fastest way to find similar records from different (big) data sources
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POwhat is the fastest way to find similar records from different (big) data sources
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWould it be possible to cache the top *n* combinations of `table1Row.first, l.first` and `table1Row.last, l.last` with their related data so for those cases, you only have to search through the cache data and not the full table? You'd probably have to do some usage analysis to see if this would make sense in your case.
      singulars
      1. POwhat is the fastest way to find similar records from different (big) data sources
      1. USFrustratedWithFormsDesigner
    2. COConsolidate your data. OR run a nightly job to calculate potential matches and store those results in one of your data sources and then look up that instead of calculating on the fly. RBAR (row by agonizing row) operations are notoriously slow.
      singulars
      1. POwhat is the fastest way to find similar records from different (big) data sources
      1. USJon Raynor
    3. COThanks guys--but unfortunately caching solutions won't work. The whole point is to allow matching between unexpected datasources. I'm looking for a way around RBAR, but am not sure if there is a way around it because I need to calculate the levenshtein function for each possible combination
      singulars
      1. POwhat is the fastest way to find similar records from different (big) data sources
      1. USbernie2436
 

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