Skip to main content
W
weezerboy
Inspiring
August 28, 2017
Answered

STOP query if time exceeds 30 seconds

  • August 28, 2017
  • 2 replies
  • 1086 views

I have a MySQL DB with 20 million records. I need to search through it to find certain records.

But I don't want the query running so long that it brings down the DB server

Long running queries have done this before.

Is it possible to write code that says if this query is taking longer than 20 seconds then stop processing and output a message

Like "please try your query again later"

This topic has been closed for replies.
Correct answer WolfShade
<cftry>
     <cfquery name="foo" datasource="#application.dsn#" timeout="30">
     ...
     </cfquery>
     <cfcatch>
     Try the search later
     <cfmail from="webmaster@domain.com" to="you@domain.com" subject="ERROR" type="html">
          There has been an error.. information below..
          <cfdump var="#cfcatch#" />
     </cfmail>
     </cfcatch>
</cftry>

Just an example.

HTH,

^ _ ^

2 replies

WolfShade
WolfShadeCorrect answer
Legend
August 28, 2017
<cftry>
     <cfquery name="foo" datasource="#application.dsn#" timeout="30">
     ...
     </cfquery>
     <cfcatch>
     Try the search later
     <cfmail from="webmaster@domain.com" to="you@domain.com" subject="ERROR" type="html">
          There has been an error.. information below..
          <cfdump var="#cfcatch#" />
     </cfmail>
     </cfcatch>
</cftry>

Just an example.

HTH,

^ _ ^

W
weezerboyAuthor
Inspiring
August 28, 2017

Thank you. Works great!

WolfShade
WolfShade
Legend
August 29, 2017

Thank you for marking my answer correct.  I do appreciate it.

V/r,

^ _ ^

WolfShade
WolfShade
Legend
August 28, 2017

CFQUERY has a timeout attribute that registers in seconds (not milliseconds) that you can use to shut down the query with.  If the time is exceeded, it throws an error.  You can place it within a CFTRY/CFCATCH to display the message.

HTH,

^ _ ^

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices