15,995,388 members
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

## 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...

## Solution 2

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

v2

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

Top Experts
Last 24hrsThis month
 Richard Deeming 50 Dave Kreskowiak 45 RickZeeland 10 #realJSOP 10 Pete O'Hanlon 10
 OriginalGriff 431 Pete O'Hanlon 215 Dave Kreskowiak 115 CPallini 110 CHill60 42

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