5 Replies Latest reply on Jan 8, 2010 11:37 AM by mgw4jc

    Performance issue: looping over queries with a query results set

    JoyRose

      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,V ARCHAR,BIT,BIT,VARCHAR,INTEGER,VARCHAR,VARCHAR,VARCHAR,VARCHAR,INTEGER,VARCHAR,VARCHAR,VAR CHAR,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[i]#'
                  </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[i]#'
                      </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[i]#
                  </cfquery>

       

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

                       </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

        • 1. Re: Performance issue: looping over queries with a query results set
          mgw4jc Level 1

          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.

          • 2. Re: Performance issue: looping over queries with a query results set
            JoyRose Level 1

            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[i]#'
                        </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[i]#'
                        and JOB_KW_LKUP.JOB_KWID = JOB_JPKW.fk_KWID
                        </cfquery>

             

            I appreciate your help with this.

            • 3. Re: Performance issue: looping over queries with a query results set
              Dan Bracuk Level 5

              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.

              • 4. Re: Performance issue: looping over queries with a query results set
                JoyRose Level 1

                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.

                • 5. Re: Performance issue: looping over queries with a query results set
                  mgw4jc Level 1

                  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