STOP query if time exceeds 30 seconds

4 replies
1111 views
Translate

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"

cfquery

This topic is closed to new replies. Start a new post to keep the conversation going.

WolfShadeCorrect answer

Legend

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

^ _ ^

WolfShadeCorrect answer

Legend

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

^ _ ^

Translate

W

weezerboyAuthor

Inspiring

Thank you. Works great!

Translate

WolfShade

Legend

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

V/r,

^ _ ^

Translate

WolfShade

Legend

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,

^ _ ^

Translate

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.