Skip to main content
Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.

Spreading SQL And CFQueryParam Tags Across Multiple Function Calls In Lucee CFML 5.2.9.40

By Ben Nadel on
Tags: ColdFusion

I've been spending a lot of time lately trying to figure out how to optimize the SQL queries required to render a given view. Ideally, the data in question should be aggregated into some sort of materialized view; or, the User Interface (UI) should be redesigned to show less data. But, unfortunately, those aren't options that I have at my disposal. As such, I'm continuing to tweak and experiment. And, one experiment that I wanted to try was whether or not I could split a SQL query and its CFQueryParam tags up across multiple function calls. This would allow me to do things like factor-out and reuse sub-queries in Lucee CFML 5.2.9.40.

Most SQL sub-queries that I write don't require CFQueryParam tags since they generally reference a column in the outer query. However, to see how flexible this approach could be, I wanted to see if my "distributed SQL" could also contain distributed CFQueryParam tags.

In the following query, I'm fetching a user; and, for that user, I'm also fetching the number of project records that they own. Again, the sub-query in this demo could be written to reference an outer column; but, I wanted to see if a CFQueryParam tag would work:

<cfquery name="user">
	SELECT
		u.id,
		u.name,
		u.email,

		<!---
			Imagine that this is a sub-query that we want to reuse across multiple
			queries. I wanted to see if it could include both SQL and CFQueryParams.
		--->
		#getUserProjectCountSQL( 1 )# AS projectCount
	FROM
		user u
	WHERE
		u.id = <cfqueryparam value="1" sqltype="integer" />
	;
</cfquery>

<cfdump var="#user#" />

<!--- ------------------------------------------------------------------------------ --->
<!--- ------------------------------------------------------------------------------ --->

<cffunction name="getUserProjectCountSQL">

	<!--- Define arguments. --->
	<cfargument name="userID" type="numeric" required="true" />

	<!---
		Output the SQL for the project-count sub-query.
		--
		NOTE: Including a CFQueryParam only works if the Function is set to allow for
		OUTPUT. If output is suppressed, this does not work and the Lucee runtime
		complains that the "parameter index is out of range".
	--->
	(
		SELECT
			COUNT( * )
		FROM
			project p
		WHERE
			p.userID = <cfqueryparam value="#userID#" sqltype="integer" />
	)

</cffunction>

As you can see, the parent query is executing the getUserProjectCountSQL() function as part of its SQL statement. This function call outputs additional SQL, include a CFQueryParam tag. And, when we run this Lucee CFML page, we get the following output:

CFQuery content split across multiple function calls, including CFQueryParam tags, in Lucee CFML.

Holy cow! It worked! Though, it only works if the getUserProjectCountSQL() function is set to allow output. Meaning, the function does not use output="false". Setting the output to be suppressed appears to also suppress the parent-child relationship between the CFQuery and CFQueryParam tags and the Lucee CFML runtime ends up complaining:

Parameter index out of range (2 > number of parameters, which is 1).

To be clear, I don't love this approach. At best, it feels "hacky". That said, sometimes when you're working in a brown-field application with limited wiggle room, "hacky" is all you've got. And, in this case, it is pretty cool to see that the CFQuery content, including nested CFQueryParam tags can be spread across multiple function calls in Lucee CFML 5.2.9.40.



Reader Comments

Hacky is not my question. Have you run this SQL against a query analyzer to see if it performs well or not?

Reply to this Comment

@John,

I assume it would since the SQL is being assembled on the ColdFusion side before it gets to the MySQL server. That said, I can try to run an EXPLAIN on it to see what goes through.

Reply to this Comment

@John,

It seems to be the same. Though, realize that this is on my local dev database which is very small in overall size. If I run this:

<cfquery name="user">
	EXPLAIN
	SELECT
		u.id,
		u.name,
		u.email,

		#getUserProjectCountSQL( 1 )# AS projectCount
	FROM
		user u
	WHERE
		u.id = <cfqueryparam value="1" sqltype="integer" />
	;
</cfquery>

<cfoutput>
	<pre>#new QueryPrinter().toString( user )#</pre>
</cfoutput>

I get this EXPLAIN results:

+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys                          | key                                    | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+
| 1  | PRIMARY     | u     | const | PRIMARY                                | PRIMARY                                | 4       | const | 1    |             |
| 2  | SUBQUERY    | p     | ref   | userID_isSample_isArchived_isOverQuota | userID_isSample_isArchived_isOverQuota | 4       | const | 33   | Using index |
+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+

And, if I inline the sub-query:

<cfquery name="user">
	EXPLAIN
	SELECT
		u.id,
		u.name,
		u.email,
		(

			SELECT
				COUNT( * )
			FROM
				project p
			WHERE
				p.userID = <cfqueryparam value="1" sqltype="integer" />

		) AS projectCount
	FROM
		user u
	WHERE
		u.id = <cfqueryparam value="1" sqltype="integer" />
	;
</cfquery>

<cfoutput>
	<pre>#new QueryPrinter().toString( user )#</pre>
</cfoutput>

... I get the following EXPLAIN output:

+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys                          | key                                    | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+
| 1  | PRIMARY     | u     | const | PRIMARY                                | PRIMARY                                | 4       | const | 1    |             |
| 2  | SUBQUERY    | p     | ref   | userID_isSample_isArchived_isOverQuota | userID_isSample_isArchived_isOverQuota | 4       | const | 33   | Using index |
+----+-------------+-------+-------+----------------------------------------+----------------------------------------+---------+-------+------+-------------+

Same thing. Though, again, this is my local DB, very little data.

Reply to this Comment

@John D,

That's what production is for ;) ... just kidding - you're exactly right. EXPLAIN is your best friend for this kind of stuff.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Blog
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.