Skip to main content
Known Participant
May 27, 2011
Question

My shared CF SERVER Environment, is usually down many times each day, well is it search-site-procedu

  • May 27, 2011
  • 2 replies
  • 293 views

My shared CF SERVER Environment, is usually down many times each day, well is it search-site-procedure the problem?

well, can you guess what needs improvement?

500 The request has exceeded the allowable time limit Tag: CFQUERY

The request has exceeded the allowable time limit Tag: CFQUERY

<cfif URL.searchKeyword EQ 1>
<cfset searchKeyword=#form.searchKeyword#>
<cfparam name="Session.searchKeyword" default="polis">

<cfquery datasource="mysqlcf_" name="getData" >
<CFOUTPUT>
SELECT Products.*, Categories.* FROM Products INNER JOIN Categories ON Categories.Category_ID = Products.Category_ID WHERE Visibility=1 AND Category LIKE '%#searchKeyword#%' OR Product_ID LIKE '%#searchKeyword#%' OR Product_Name LIKE '%#searchKeyword#%' OR Product_Description LIKE '%#searchKeyword#%'
</CFOUTPUT>

</cfquery>

<cfelse>
<cfset searchKeyword=Session.searchKeyword>
<cfquery datasource="mysqlcf_" name="getData" >
<CFOUTPUT>
SELECT Products.*, Categories.* FROM Products INNER JOIN Categories ON Categories.Category_ID = Products.Category_ID WHERE Visibility=1 AND Category LIKE '%#searchKeyword#%' OR Product_Name LIKE '%#searchKeyword#%' OR Product_Description LIKE '%#searchKeyword#%'
</CFOUTPUT>

</cfquery>

</cfif>
<cfif getData.recordcount LE endrow>
<cfset endrow=getData.recordcount>
</cfif>

    This topic has been closed for replies.

    2 replies

    Inspiring
    May 27, 2011

    In addition to Adam's comments, I have found that "best practices" are not always the best approach in every situation.  In this case, the "best practice" is to minimize the number of trips to the database.  You might get better performance if you run 4 queries against the database and put them together with a Q of Q union query.

    Also, an index on the Visibility field might help, if you don't have one already.

    Inspiring
    May 27, 2011

    Well you need to speed your query up.

    This isn't an SQL forum, but things I can see you shoudl check / fix:

    * don't SELECT *.  Only SELECT the columns you need

    * check you've got suitable indexes / relationships on the columns you're joining

    * parameterise your dynamic values.

    * you might consider row-limiting the query (both ont he DB and in the CFQUERY).  If it's for a search, you probably only need a max of... I dunno... 100-odd results?

    * consider using full-text searching for this, rather than LIKE statements

    But DB tuning is not really "in scope" for a CF forum.  You should be asking questions like this on a DB forum.  And probably get hold of a book about SQL and read it.

    --
    Adam