TryCF Gist

trycf-gist-1599763593144-f88a9b1b-d2a8-aedb-54ce-dbed3c2320b3.cfm

<cfscript>
    q_src = queryNew("tag,id")
	tags = getTagList();
	r = 0;
	do {
	    loop collection="#tags.cf#" item="tag" {
            r = queryAddRow(q_src);
            QuerySetCell(q_src,"tag", tag, r);
	    	QuerySetCell(q_src,"id", r, r);
		    r++;
		    //if (r gt 100) 			break;
		}
	} while (r < 100000);
</cfscript>

<cfdump var=#q_src# label="source dataset" expand=false>

<cfloop list="true,false" item="use_index">
    
<cfif use_index>
    <h3>With Index on Query</h3>
    <cftimer type="inline" label="add a index on [tag]">
        <cfquery name="q" indexName="tag" dbtype="query">
            select * from q_src
        </cfquery>
    </cftimer>
<cfelse>
    <h3>Without Index</h3>
    <cftimer type="inline" label="just select">
        <cfquery name="q" dbtype="query">
            select * from q_src
        </cfquery>
    </cftimer>
</cfif>
<hr>
<cfscript>
	timer type="inline" label="queryRowByIndex (id=1)"{
	    q_result=queryRowByIndex(query=q, index="id", defaultValue="1");
	}
	dump(var=q_result);
	
	params = {id: "7"};
	timer type="inline" label="queryRowDataByIndex #serializeJson(params)#"{
		q_result=queryRowDataByIndex(query=q, index="id", defaultValue=params);
	}
	dump(var=q_result);
	
	params = {tag: "sleep222"};
	timer type="inline" label="queryRowDataByIndex: #serializeJson(params)#"{
	    q_result = queryRowDataByIndex(query=q, index="sleep", defaultValue=params);
	}
	dump(var=q_result);
	
	timer type="inline" label="queryGetCellByIndex, tag=sleep"{
	    q_result =queryGetCellByIndex(query=q, column_name="tag", 
	        index="sleep", defaultValue="no match");
	}
	dump(var=q_result);
</cfscript>
</cfloop>
<h1>Plain QoQ</h1>
<cftimer type="inline" label="By QoQ, tag=sleep">
    <cfquery name="q_qoq" dbtype="query">
        select * from q_src where tag='sleep'
    </cfquery>
</cftimer>
<cfdump var=#q_qoq# label="By QoQ, tag=sleep" expand=false>