Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Log each query call to the dB to see which query is troublesome

Troubleshooting Techniques

Log each query call to the dB to see which query is troublesome

by  GUJUm0deL  Posted    (Edited  )
Hey all, I had some major issues with my company's site and didn't know why our site kept crashing. Well, the actual reason was something quite different, but as I was trying to debug I implemented this technique to help troubleshoot in the future.

The idea is to log all query calls to the dB to see which query takes an abnormal time to process. You can then look at the logfiles table to see the count.

THE CODE:
Code: 
[color red]<cfset gm_startTime = gettickcount()>[/color]
<cfquery name="testQuery" datasource="#ds#">
  select user_id, name, address, city, state, zip
  from users
  where user_id = '#url.user_id#'
</cfquery>
[color red]<cfset gm_endTime = gettickcount()>
<cfset totalTime = (gm_endTime - gm_startTime)/1000>[/color]
...
...
...
[color green]
<cfquery name="logrecs" datasource="#ds#">
  insert into LOGFILE(pagecall,starttime,endtime,totaltime,createdate,queryname)
  vales('page1.cfm',#gm_startTime#,#gm_endTime#,#totalTime#,getdate(),'testQuery')
</cfquery>
[/color]

Now if/when your site acts odd you can check this table first to see if the culprit is any query processing.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top