I have a database with two columns: 1)Word 2) Times Used

When the user enters a word it find the combination of words already typed (ex: combo = hello world) and updates the database using a dataset

The query is as follows:

 SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC

In my application I am calling it as follows:

PredTable = PredTA.GetDataByPartialWord(combo)
            If PredTable.Count > 1 Then MsgBox("problem")
            If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
            If PredTable.Count = 0 Then PredTA.Insert(combo, 1)

When I debug it is taking 0.15 seconds to just do this query Is there anyway that I can quicken this up?

  • Perhaps look into indexing those columns. You might have to use a full-text index on Word, based on your description, but you could try a non-clustered index first and see if it works for what you need. Commented Dec 1, 2014 at 16:12
  • i am not sure how you do that in msssql but in PostgreSQL you can index on regular expressions using the pg_trgm module along with a gist index. it also fixes generic like queries such as LIKE '%abc%cde%'. Commented Dec 1, 2014 at 18:27


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.