0

I have a database with two columns that is called using the following query:

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

In my application it is taking a ridiculous amount of time to call this statement

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

Indexing does not help because it is only a two column database and is constantly being updated

Is there any other way that I can improve the speed of this statement?

2
  • What SQL are you using? Microsoft, MySQL, Oracle, PostgreSQL, or something else? First of all your query is using a WHERE (Word LIKE @partialWord) which means that if there is a prefix wildcard (e.g. '%tion') the whole table has to be scanned and the contents of the Word column likewise scanned. That is the base cost of this query. Therefore, you need to investigate some other method of finding the partial words. For example, full text indexing (if available to you) may give you other options to use when finding %partial% words.
    – RLF
    Commented Dec 16, 2014 at 18:29
  • I am using microsoft sql server. How would I do full text indexing?
    – Dman
    Commented Dec 16, 2014 at 18:54

1 Answer 1

2

Thanks for updating that you are using Microsoft SQL Server, which does support Full Text Indexing. (As do many other versions of SQL servers.)

First of all your query is using a WHERE (Word LIKE @partialWord) which means that if there is a prefix wildcard (e.g. '%tion') the whole table has to be scanned and the contents of the Word column likewise scanned.

That is the base cost of this query.

Therefore, you need to investigate some other method of finding the partial words. For example, full text indexing (if available to you) may give you other options to use when finding %partial% words.

Create a full text index on the Predictions table for the Word column. This can be done through SSMS or by a script. This will populate the keywords table in the FTI catalog tables. Example from: http://msdn.microsoft.com/en-us/library/ms187317.aspx

CREATE UNIQUE INDEX pk_PredictionsID ON dbo.Predictions(PredictionsID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Predictions(Word) 
   KEY INDEX pk_PredictionsID 
   WITH STOPLIST = OFF;

Then here is a sample script that would help you to then find all the rows with wildcards.

DECLARE @SearchText NVARCHAR(2000)
SET @SearchText = ''

DECLARE @Pattern NVARCHAR(100)
SET @Pattern = '%colat%'

SELECT @SearchText = @SearchText + display_term + ' OR ' 
FROM sys.dm_fts_index_keywords ( DB_ID('DBName'),
    object_id('Predictions') )
   WHERE display_term like @Pattern

SELECT @SearchText = LEFT (@SearchText, LEN(@SearchText) - 3)
-- Contents "chocolate OR percolator" in my test case

SELECT * FROM Predictions
WHERE CONTAINS(*, @SearchText)

By building a full text query string that includes all strings containing the pattern joined by the OR operator, it allows Full Text Search to then directly access the rows that contain one (or more) of the words. This should be a lot faster than scanning the table.

2
  • RFL I guess it would have been better to put your current answer as comment and after OP replies you can change it as answer by adding more facts specific to RDBMS
    – Shanky
    Commented Dec 16, 2014 at 18:28
  • @Shanky - went through the process.
    – RLF
    Commented Dec 16, 2014 at 21:39

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.