Performance issue: looping over queries with a query results set

Forum|Forum|16 years ago
January 8, 2010
1 reply
760 views

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

Advanced techniques

This topic has been closed for replies.

mgw4jc

Inspiring

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.

JoyRoseAuthor

Inspiring

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.

Dan_Bracuk

Inspiring
Forum|Forum|16 years ago
January 8, 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.

Powered by Gainsight
Terms & Conditions Accessibility statement

Sign up

Already have an account? Login

To post, reply, or follow discussions, please sign in with your Adobe ID.
Sign in

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.
Sign in

Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.

Enter your e-mail address

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
OK

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.
OK

Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information AdChoices