For years, I’ve been advocating that people put [DEBUG](https://www.bennadel.com/blog/3058-putting-debug-comments-in-your-sql-statements-makes-debugging-performance-problems-easier.htm) comments in their SQL statements to help identify performance bottlenecks in their applications. I feel so strongly about this that I’m absolutely nonplussed whenever I see a SQL query show-up in the MySQL slow-log or the process-list without a DEBUG statement. Recently, we installed FusionReactor at work; and, I was thrilled to see that my DEBUG statements were showing up in the database profiling that FusionReactor provides.

To see what I mean, let’s create a test ColdFusion page that executes a slow SQL query that will show up in FusionReactor’s slow transaction list. For this simulation, we can use MySQL’s SLEEP() command:

<cfscript>

	dump( getPost( 3 ) );

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	public array function getPost( required numeric id ) {

		// NOTE: We're using MySQL's SLEEP() command to simulate a performance bottleneck
		// in order to get this page to show up in FusionReactor's slow transaction log.
		var result = queryExecute(
			sql = "
				/* DEBUG: fusionReactorTesting.getPosts(). */
				SELECT
					e.id,
					e.content,
					e.content_markdown,

					-- Simulating a slow query, sleep for 5-seconds.
					( SLEEP( 5 ) ) AS temp
				FROM
					blog_entry e
				WHERE
					e.id = :id
				;
			",
			params = {
				id: { value: id, sqlType: "integer" }
			},
			options = {
				returnType: "array"
			}
		);

		return( result );

	}

</cfscript>

#coldfusion #sql