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.

#coldfusion #lucee cfml 5.2.9.40 #sql