An optimal way of keyword similar search on Access Database

Question

0.00/5 (No votes)

See more:

Hey guys ,
I have a database (access) with 28 fields, each field consists of 2 parts separated by an special string (i.e. *H*)
exp : " John Q *H* Samuel "

I want to search for any similar entry X on this database. X can be whether the 1st part or 2nd part of any of the 28 fields of whole database.

I used the simple code below

C#

DamerauLevensteinMetric DLM = new DamerauLevensteinMetric();
List<int> Item_arrays_2_show = new List<int>();
 
for (int i = 0; i < Database.Rows.Count; i++)
{
   for (int j = 0; j <= 28; j++)
      {
         string[] t = Seperate(Database.Rows[i].ItemArray[j].ToString(), "*H*");
 
         if (DLM.GetDistance(t[0],textbox.Text) < 10 ||
         DLM.GetDistance(t[1], textbox.Text) < 10)
            Item_arrays_2_show.Add(i);
      }
}

but as you notice the complexity of this algorithm is O(Database.Rows.Count ^ (28 * 2))
if we suppose that the running time of DamerauLevensteinMetric function is 1

if the database has 1,000 records its imaginable that What a LONG time is needed to find matches !!

Is there any optimal way of doing this ?

Posted 23-Dec-11 0:32am

Hamed Metalgear

Add a Solution

2 solutions

Solution 1

First things that come in mind are:
1. Why do you store the values in columns. To efficiently search the data they should be on rows
2. Different values shouldn't be concatenated into a single column. Using two columns in this case would be much more efficient.

You could possibly search for the data with something like:

SQL

... Field LIKE 'SomeValue *H*%'
OR  Field LIKE ' %*H* SomeValue'
...

But that seems quite ackward...

Posted 23-Dec-11 1:37am

Wendelius

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hamed Metalgear · Accepted Answer · 2011-12-23T03:06:00

Thanks for response,

1- the values belong to some product, and they are the specifications of the product. actually on one row, I have 65 fields, which 28 of them keeps the info needed for this search.

2- Values stored in those 28 fields are not 2 different values, actually only one value, but my search key can be in either parts.

3- using LIKE I might be able to separate the two parts in a field, but I might not be able to search for any similarities
suppose the value on one of my field be : " Sean Bower *H* Anthony Eastwood "
I wana do search for say Antoni !!
if I use LIKE, I will not be able to find Anthony while my search key is Antoni

An optimal way of keyword similar search on Access Database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

An optimal way of keyword similar search on Access Database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us