Performance issue: looping over queries with a query results set

Report · Jan 08, 2010

I have code that works, but I think I should be able to run the code faster. I could try a stored procedure but there are so many variables to set. I tried with wrapping cftransation around the code, but it didn't make a noticeable difference. I need to go through the data singularly to fill my query object.

Here's an ABBREVIATED sample of the code:

<cfset tot_AllActiveListing = QueryNew(
"AnnounceNum, JP_PDLoc, JP_JS_Title, JP_JS_KWID, JP_JS, JP_Open, JP_Close, JP_CloseType, JP_CloseName, JP_PosNeed, JP_DirectHire, JP_Desc, JP_Draft, JP_Archived, JP_State, JP_AreaID, JP_AreaName, JP_AreaAlias, JP_Fac_SU, JP_Fac_Facility, JP_FAC_ID, JP_Grade1, JP_Grade2, JP_Grade3, JP_Grade4, JP_Grade5, JP_Posted, JP_TypeHire, JP_HRemail",
"VARCHAR,VARCHAR,VARCHAR,INTEGER,INTEGER,TIMESTAMP,TIMESTAMP,INTEGER,VARCHAR,INTEGER,BIT,VARCHAR,BIT,BIT,VARCHAR,INTEGER,VARCHAR,VARCHAR,VARCHAR,VARCHAR,INTEGER,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,TIMESTAMP,INTEGER,VARCHAR")
/>

<cfquery name="getAllActiveListing" datasource="#request.at_datasource#">
    SELECT j.JOB_AnnounceNum, j.JOB_PDLoc, j.fk_JS_code, j.Job_JPOpen, j.Job_JPClose, j.fk_CloseType, j.JOB_JPPosNeed, j.JOB_DirectHire, j.JOB_JPDesc, j.Job_JPDraft, j.JOB_JPArchived, j.JOB_State,
    j.fk_FACID, j.Posted, j.JOB_IHSvITU, f.Fac_Area, f.Fac_ServiceUnit, f.fac_Facility, f.Fac_Addr1, f.Fac_Addr2, f.Fac_City, f.Fac_State, f.Fac_Zip
    from JOB_JP j INNER JOIN #generaldb#IHSFacility f
    ON j.fk_FACID = f.Fac_ID
    WHERE
            JOB_JPDraft = 0
            and (Job_JPClose = #Now()# or Job_JPClose > #Now()# or fk_CloseType = 2 or fk_CloseType = 3)
            and (JOB_JPArchived = 0 or JOB_JPArchived IS NULL)
             <cfif IsDefined("qAltPostID") and qAltPostID.recordcount gt "0">
            and JOB_AnnounceNum IN (<cfqueryparam list="yes" cfsqltype="CF_SQL_varchar" value="#ValueList(qAltPostID.fk_Job_AnnounceNum)#">)
            <cfelseif option is "JPPostListing" and StructKeyExists(session,"IHSUID")>
            and j.WhoCreated = #session.IHSUID#
             </cfif>
             Order by j.Job_JPOpen desc
    </cfquery>

    <cfloop from="1" to="#session.getAllActiveListing.recordcount#" index="i">
            <cfquery name="getAllActiveListingGrade" datasource="#request.at_datasource#">
                SELECT fk_Job_AnnounceNum, Grade
                from Job_JP_Grade
                Where Job_JP_Grade.fk_Job_AnnounceNum = '#session.getAllActiveListing.Job_AnnounceNum#'
            </cfquery>

            <cfif IsDefined("session.getAllActiveListing") and session.getAllActiveListing.recordcount neq "0">
                <cfquery name="getAllActiveListingIHSArea" datasource="#at_datasource#">
                SELECT JOBIHSArea_ID, JOBIHSArea_Name, JOBIHSArea_Alias
                from JOB_IHSArea_LKUP
                where JOBIHSArea_Alias = '#session.getAllActiveListing.Fac_Area#'
                </cfquery>
            </cfif>
            <cfset session.getAllActiveListingGrade = getAllActiveListingGrade />

            <cfquery name="getAllActiveListingCloseName" datasource="#at_datasource#">
            SELECT JOB_CloseName
            from JOB_CloseType_LKUP
            where JOB_CloseType_LKUP.JOB_CloseType = #session.getAllActiveListing.fk_CloseType#
            </cfquery>

                <cfscript>
                   newRow=QueryAddRow(tot_AllActiveListing);
                    QuerySetCell(tot_AllActiveListing, "AnnounceNum", "#session.getAllActiveListing.Job_AnnounceNum#");
                    QuerySetCell(tot_AllActiveListing, "JP_PDLoc", "#session.getAllActiveListing.JOB_PDLoc#");
                    QuerySetCell(tot_AllActiveListing, "JP_Draft", "#session.getAllActiveListing.Job_JPDraft#");
                    QuerySetCell(tot_AllActiveListing, "JP_Archived", "#session.getAllActiveListing.Job_JParchived#");
                    QuerySetCell(tot_AllActiveListing, "JP_Posted", "#session.getAllActiveListing.Posted#");
                    QuerySetCell(tot_AllActiveListing, "JP_PosNeed", "#session.getAllActiveListing.JOB_JPPosNeed#");
                    QuerySetCell(tot_AllActiveListing, "JP_DirectHire", "#session.getAllActiveListing.JOB_DirectHire#");

                 </cfscript>

        </cfloop>

Any ideas will be greatly appreciated. If stored procedures are the best way to handle this and will run appreciably faster, I'll try it.

Thanks.

JoyRose

Report · Jan 08, 2010

It looks to me you could get all the data with one query by joining (perhaps left join) the Job_JP_Grade and JOB_IHSArea_LKUP tables. Then you should not need the QueryNew or the cfloop.

The performance issue is because for each row of the first query you are running 2 additional queries. Expand the first query to include all three and you should see a huge performance increase.

Report · Jan 08, 2010

Thanks for your reply.

So now here is the entire code written with LEFT JOIN:

<cfquery name="getAllActiveListing" datasource="#request.at_datasource#">
    SELECT j.JOB_AnnounceNum, j.JOB_PDLoc, j.fk_JS_code, j.Job_JPOpen, j.Job_JPClose, j.fk_CloseType, j.JOB_JPPosNeed, j.JOB_DirectHire, j.JOB_JPDesc, j.Job_JPDraft, j.JOB_JPArchived, j.JOB_State,
    j.fk_FACID, j.Posted, j.JOB_IHSvITU, f.Fac_Area, f.Fac_ServiceUnit, f.fac_Facility, f.Fac_Addr1, f.Fac_Addr2, f.Fac_City, f.Fac_State, f.Fac_Zip, g.Grade, a.JOBIHSArea_ID, a.JOBIHSArea_Name, a.JOBIHSArea_Alias, c.JOB_CloseName, s.Title, p.HRContact, p.HRContactType, e.Email, k.fk_KWID, k.fk_AnnounceNum, w.JOB_KWName, w.JOB_KWID
    from JOB_JP j INNER JOIN #generaldb#IHSFacility f
    ON j.fk_FACID = f.Fac_ID
    LEFT OUTER JOIN JOB_JP_Grade g
    ON j.JOB_AnnounceNum = g.fk_Job_AnnounceNum
    LEFT OUTER JOIN JOB_IHSArea_LKUP a
    ON j.Fac_Area = a.JOBIHSArea_Alias
    LEFT OUTER JOIN JOB_CloseType_LKUP c
    ON j.fk_CloseType = c.JOB_CloseType
    LEFT OUTER JOIN JOB_Series_LKUP s
    ON j.fk_js_code = s.fk_js_code
    LEFT OUTER JOIN JOB_JPContacts p
    ON j.JOB_AnnounceNum = p.fk_Job_AnnounceNum
    LEFT OUTER JOIN #globalds#Email e
    ON p.HRContact = e.table_ID
    LEFT OUTER JOIN JOB_JPKW k
    ON j.JOB_AnnounceNum = k.fk_AnnounceNum
    LEFT OUTER JOIN JOB_KW_LKUP w
    ON k.fk_KWID = w.JOB_KWID
    WHERE
            JOB_JPDraft = 0
            and (Job_JPClose = #Now()# or Job_JPClose > #Now()# or fk_CloseType = 2 or fk_CloseType = 3)
            and (JOB_JPArchived = 0 or JOB_JPArchived IS NULL)
             <cfif IsDefined("qAltPostID") and qAltPostID.recordcount gt "0">
            and JOB_AnnounceNum IN (<cfqueryparam list="yes" cfsqltype="CF_SQL_varchar" value="#ValueList(qAltPostID.fk_Job_AnnounceNum)#">)
            <cfelseif option is "JPPostListing" and StructKeyExists(session,"IHSUID")>
            and j.WhoCreated = #session.IHSUID#
             </cfif>
             Order by j.Job_JPOpen desc
    </cfquery>

I'm concerned about the queries below that I converted to the LEFT JOIN code above..

<cfquery name="getAllActiveListingHRContact" datasource="#at_datasource#">
            SELECT HRContact, HRContactType
            from JOB_JPContacts
            where fk_Job_AnnounceNum = '#session.getAllActiveListing.JOB_AnnounceNum#'
            </cfquery>

            <cfif CompareNoCase(getAllActiveListingHRContact.HRContactType,"HRContactID") is 0>

                <cfquery name="getAllActiveListingHREmail" datasource="#globalds#">
                SELECT Email
                from Email
                where Table_ID = #getAllActiveListingHRContact.HRContact#
                </cfquery>
                <cfset session.getAllActiveListingHREmail = getAllActiveListingHREmail />

            </cfif>


            <cfquery name="getAllActiveListingMasterKey" datasource="#at_datasource#">
            SELECT fk_KWID, fk_AnnounceNum, JOB_KWName, JOB_KWID
            from JOB_JPKW, JOB_KW_LKUP
            where JOB_JPKW.fk_AnnounceNum = '#session.getAllActiveListing.JOB_AnnounceNum#'
            and JOB_KW_LKUP.JOB_KWID = JOB_JPKW.fk_KWID
            </cfquery>

I appreciate your help with this.

Report · Jan 08, 2010

I'm not sure what your concern is, but you might want to step back and reload.

The fact that you are going back to your database after your initial query means one of two things. Either you didn't get enough data with your 1st query or you are not fully utilizing the data you did bring back.

In your initial post, you expressed a concern about performance. Running db queries inside a loop is a performance killer. If you must go back to the db, use your loop to generate lists of values, and then make one query based on that list.

Report · Jan 08, 2010

Hi Dan,

I did the join as was recommended from my initial post.

Below that, I'm just showing a few of the queries that I

may have not successfully coded in the join" code. I

am not running those queries nor looping through anything

with the "join" version.

Are you saying that I shouldn't use the join, that is won't

work or that I should use a stored procedure instead?

Thanks for your response.

Report · Jan 08, 2010

At a quick glance it looks like you did your joins as you should, including the two you aren't sure about. Joins can daisy chain and don't have to join back to the primary table, just as you did with JOB_JPContacts and Email.

The best way to know if it is working is to run it and see if data comes back as expected.

-Matt

Adobe Community

Performance issue: looping over queries with a query results set