First of all, I haven't used Access very much, so I'm not sure what the
limitations are. I would expect that doing a LIKE with the "%" on the right
would allow the index to be used and shouldn't be too slow. On the other
hand, 150K records sounds like a lot for an Access database.
You might want to figure out some sort of alternate indexing scheme. For
example, you could make a table that contains the first two letters of the
"ENGLISH" column with an index into MyTable for matching records. Depending
on how large the rows are in MyTable, scanning the index table may be much
faster.
Assuming MyTable look something like this:
MyTable
- int - MyTableID
- char(255) - ENGLISH
- other fields follow -
you could do:
MyIndexTable
- int - MyTableID
- char(2) - MyTableStart
the MyTableStart field would simply contain the first two characters of the
ENGLISH field from MyTable. You could do a select like:
string shortString = string.empty;
if (txtWordManipulation.Text.Length > 1)
{
shortString = txtWordManipulation.Text.Substring(0,2);
}
else
{
shortString = txtWordManipulation.Text[0].ToString();
}
SqlString = "SELECT ENGLISH FROM MyTable WHERE ENGLISH LIKE
'"+txtWordManipulation.Text+"%' and MyTableID in (SELECT MyTableID FROM
MyIndexTable Where MyTableStart = '" + shortString + "')";
I honestly don't know if this would be faster. You'd need to test it, but
the subquery should restrict the search to a much smaller subset of MyTable
and may result in a significant increase in speed.
There are a number of variables that can affect this, however.
If the rows of MyTable are not particularly large, then this technique
doesn't help you much. Also, this technique will slow down inserts, updates,
and deletes as you'll have to update two tables.
Also, it's likely the records in MyTable are spread out across the table,
requiring most of the database to be read in anyway.
Again, I don't know Access very well, so I don't know how it handles the
data internally, but it may be possible that if you generate the table by
inserting the records in alphabetical order (such that words that have
similar starting strings are close together), that it may be faster as well,
but this depends on whether access keeps the records physically in the order
that they're inserted. I don't know that it does this.
Anyway, hope this gives you some possibilities.
Pete
"mp" <my***@volja.net> wrote in message
news:up**************@TK2MSFTNGP12.phx.gbl...
Hi,
MS Access DB, C#, VS, SQL
I have implemented search with SQL statements like follows:
SQLString = "SELECT ENGLISH FROM MyTable WHERE ENGLISH LIKE
'"+txtWordManipulation.Text+"%' ORDER BY ENGLISH";
end everything is desperately slow. DB is more than 150k words and I
haven't defined primary key because indexed option is set on Yes (Duplicates OK).
Do you have some suggestions or idea for different search?
Thanks
