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. – Your_comment_is_not_funny Dec 1 '14 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%'. – Hans-Jürgen Schönig Dec 1 '14 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.