STOP query if time exceeds 30 seconds

Participant ,
Aug 28, 2017 Aug 28, 2017

Copy link to clipboard

Copied

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"

Views

574

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

LEGEND , Aug 28, 2017 Aug 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,^ _ ^

Likes

Translate

Translate
LEGEND ,
Aug 28, 2017 Aug 28, 2017

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 28, 2017 Aug 28, 2017

Copy link to clipboard

Copied

<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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Aug 28, 2017 Aug 28, 2017

Copy link to clipboard

Copied

Thank you. Works great!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 29, 2017 Aug 29, 2017

Copy link to clipboard

Copied

LATEST

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

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines