CODEWITHSUNDEEP
sqlcsvcoldfusioncfquerycfhttp
espresso_coffee
espresso_coffee

Reputation: 6110

How to compare values from two separate quires but not use inner loop?

I'm working on my project where I have to compare values from DB to the values from .csv file. I have used 'cfhttp' to convert my .csv to query and then I loop over that query and another query that I used to get values from DB. Inside of those two loop I used if statements to compare my values and check if they match. Then I stored them in the lists and use those list in cfquery tags for my update. I have 14k records in DB and about the same number of records in my .csv file. My current code takes less than 2 minutes to output records from the list to the screen for testing purpose. I still did not test how long update will take. Before I run my update I would like to see if any of you would recommend any other approach for my project? Could I reduce my execution time to less than I currently have? Here is my code that I have:

<cfhttp name="records" columns="A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X" method="get" url="http://path to csv/temp.csv"/>

<cfquery name="getRecords" datasource="test">
    Select s.ID, f.URID, s.UR_NUMBER, CODE, f.FIELD
    From USERS s
    Left Outer Join MAPS f
    ON s.ID = f.URID
</cfquery>  

<cfset NumA = "">
<cfset NumB = "">
<cfset NumC = "">

<cfset NumD = "">
<cfset NumE = "">
<cfset updNumD = "">
<cfset updNumE = "">

<cfloop query="records">
    <cfloop query="getRecords">
        <cfif records.A EQ getRecords.UR_NUMBER>
            <cfif records.W NEQ getRecords.CODE>
                <cfif records.W EQ 'A'>
                    <cfset NumA = ListAppend(NumA,"#records.A#")>   
                <cfelseif records.W EQ 'B'>
                    <cfset NumB = ListAppend(NumB,"#records.A#")>
                <cfelse>
                    <cfset NumC = ListAppend(NumC,"#records.A#")>
                </cfif>
            </cfif>

            <cfif getRecords.URID EQ ''>
                <cfif records.W EQ 'D'>
                    <cfset NumD = ListAppend(NumD, "#getRecords.ID#")>
                <cfelseif records.W EQ 'E'>
                    <cfset NumE = ListAppend(NumE, "#getRecords.ID#")>
                </cfif>
            <cfelse>
                <cfif records.W EQ 'E'>
                    <cfset updNumD = ListAppend(updNumD, "#getRecords.URID#")>
                <cfelseif records.W EQ 'D'>
                    <cfset updNumE = ListAppend(updNumE, "#getRecords.URID#")>
                </cfif>
            </cfif> 
        </cfif>
    </cfloop>
</cfloop>

Here I dump my lists to the screen:

<cfdump var="#NumA#">
<cfdump var="#NumB#">
<cfdump var="#NumC#">
<br>
<cfdump var="#NumE#">
<cfdump var="#NumD#">
<br>
<cfdump var="#updNumE#">
<cfdump var="#updNumD#">

Also I was wondering if it's possible to get out with cfhttp tag just columns that I need from my .csv? Now I'm garbing all columns from my .csv even if I use only two of them column A and W. Also is it possible to join query from 'cfhttp' to my query from DB? If anyone can give me some advise on this project please let me know.

Upvotes: 1

Views: 114

Answers (1)

John Whish
John Whish

Reputation: 3036

So I think you are only interested in data where records.A is equal to getRecords.UR_NUMBER. You could use a Query of Queries to get the intersection of the two datasets. Something like:

<cfquery name="intersection" dbtype="query">
  select *
  from records, getRecords
  where records.A = getRecords.UR_NUMBER
</cfquery>

That should give you combined dataset which you can then loop through and build up your lists. As Matt Busche mentioned in the comments, list operations are slow, so arrayAppend will be faster.

If Query or Query is too slow, then you may want to look at converting one of the recordsets to a struct (of key value pairs) as look ups are faster. For example:

<cfscript>
// convert to structs as key lookups are fast
dbData = {};
for (row in getRecords) {
    dbData[row.UR_NUMBER] = row;
}

csvData = {};
for (row in records) {
    if (structKeyExists(dbData, row.A)) {
        // we have a match, so build the lists here...
        writeDump(dbData[row.A]);
    }
}
</cfscript>

Upvotes: 2

Related Questions