Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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...
 
Share this answer
 
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
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900