Currently, I've a query inside a loop as below.
<cfloop query="Superquery">
<cfquery datasource="mysource" name="getData">
SELECT col1, Col2
FROM myData
where col1 = #Superquery.IDCol#
</cfquery>
<!--- Some Processing --->
</cfloop>
The query doesn't return much data. In some cases, it returns less than 100 rows & in some it can return around 5000 rows. But since it is inside a loop & iterations can go over 100, it makes me think on a better optimization approach.
Approach 1: Use Query of Queries
<cfquery datasource="mysource" name="getAllData">
SELECT col1, Col2
FROM myData
</cfquery>
<cfloop query="Superquery">
<cfquery dbtype="query" name="getData">
SELECT col1, Col2
FROM getAllData
where col1 = #Superquery.IDCol#
</cfquery>
<!--- Some Processing --->
</cfloop>
Approach 2: Use Structues
Outside the loop, I've the query getAllData as per Approach 1 and create a structure with Key as 'col1' and Value as 'col2'. Inside the loop, I use the sturcture that was created outside the loop to do the necessary processing.
I'm not sure if it makes a difference in performance between the two approaches. Approach 1 is easy to implement though. :) Just thought of getting some suggestions. Any other efficient approaches are welcome.
Thank you!!
Query of queries is actually pretty slow compared to an SQL query as it doesn't have any concept of indexes or execution plans so you need to be careful before going down that route as you could well end up with a slower more intensive process. Database engines are optimised to do this kind of thing fast.
You may well find that using a struct will perform better, but you do have the overhead of creating the structure. Unfortunately I can't give you a one-size fits all answer, as it depends on your data and the volumes.
Ideally you want to be able to do a join between myData and the tables that create your Superquery query. You can then iterate over that query and process as you need without hitting the database again.