CODEWITHSUNDEEP
sqloptimizationcoldfusion
kbtbc
kbtbc

Reputation: 1

Optimizing Database Queries using ColdFusion QoQ

I'm trying to optimize my queries by using ColdFusion's Query of Queries feature to access a cached query of about 45,000 words.

With this below query I had lots of success in speed switching to QoQ:

<cfquery name="FindAnagrams" dbtype="query" >
    SELECT AllWords.Word, AllWords.AnagramKey
    FROM AllWords
    WHERE AllWords.WordLength = #i#
</cfquery>

Executions went from ~400ms to ~15ms.

This below query however was only slightly reduced in execution time (from ~500ms to ~400ms):

 <cfquery name="TopStartWith" dbtype="query" maxrows="15">
    SELECT AllWords.Word
    FROM AllWords
    WHERE AllWords.Word LIKE <cfoutput>'#Word#%' </cfoutput> 
    AND AllWords.Word <> '#Word#'
    ORDER BY AllWords.Frequency DESC;
  </cfquery>

Removing 'Maxrows' did not really help. My Database fields are indexed and I'm ad the end of my knowledge of optimizing queries (Can you index a column of a CF QoQ object?) I suspect it is the 'ORDER BY' that is causing the delay, but am unsure. How can I further improve the speed of such queries? Many thanks.

Upvotes: 0

Views: 219

Answers (2)

barnyr
barnyr

Reputation: 5678

For optimizing the second query, there are a couple of approaches you could take.

Firstly, see if your database supports something like function-based indexes (an oracle term, but it is available in other platforms). See this for a mySQL example: Is it possible to have function-based index in MySQL?

Secondly, you could pre-process your words into a structure which supports the query you're after. I'm assuming you're currently loading the query into application or session scope elsewhere. When you do that you could also process the words into a structure like:

{
    'tha':['thames','that'],
    'the':['them','then','there'],
    //etc
}

Instead of running a QoQ, you get the first 3 letters of the word, look up the array, then iterate over it, finding matches. Essentially, it's pretty similar to what a function-based index is doing, but in code. You're trading memory for speed, but with on 45000 words, the structure isn't going to be enormous.

Upvotes: 2

Carl Von Stetten
Carl Von Stetten

Reputation: 1149

The LIKE clause probably causes the poor performance of your second query. You can see a similar performance penalty if you use LIKE in a regular database query. Since LIKE performs a wildcard search against the entire string stored in the database column, it can't just do an EQUALS comparison.

Upvotes: 0

Related Questions