3 Replies Latest reply on Dec 28, 2009 1:00 PM by Dan Bracuk

    Slow Query Question/

    emmim44 Level 1

      I need to know the reasons why the below query is slow ..takes more than a min...I have total  4K records


      <cfquery datasource="#attr.DSN#" name="SearchRes">
           select a.*, c.comments
           from ops$new.network_event a,
           ( SELECT  MAX( record_number ), comments, event_number
             FROM ops$new.ne_comment
           GROUP BY comments, event_number ) c
            1 = 1
           <cfif attr.problem neq "" or attr.node neq "" or attr.lob neq "">
            And a.event_number in
              select distinct e.event_number 
               ops$new.network_event e
              , ops$new.ne_node n
              , ops$new.ne_problem p
              , ops$new.ne_lob l
               1 = 1 And
               e.event_number = p.event_number
               and   e.event_number = n.event_number
               and   e.event_number = l.event_number
               <cfif attr.node neq "" and trim(attr.node) neq "All">
                And n.node in (#ListQualify(attr.node, "'")#)
               <cfif attr.problem neq "">
                And p.problem_code in (#attr.problem#)
               <cfif attr.lob neq "">
                And l.lob in (#ListQualify(attr.lob, "'")#)
            And a.event_number = c.event_number(+)
            <cfif attr.tick neq "">
             And lower(TICKET_NUMBER) like lower('#attr.tick#%')
            <cfif attr.status neq "">
             And status_code = #attr.status#
            <cfif attr.start_date0 neq "" and attr.start_date1 neq "">
             And Start_date between #CreateODBCDateTime(attr.start_date0)# And #CreateODBCDateTime(attr.start_date1)#
            <cfelseif attr.start_date0 neq "">
             And Start_date <= #CreateODBCDateTime(attr.start_date0)#
            <cfelseif attr.start_date1 neq "">
             And Start_date <= #CreateODBCDateTime(attr.start_date1)#     

            <cfif attr.end_date0 neq "" and attr.end_date1 neq "">
             And end_date between #CreateODBCDateTime(attr.end_date0)# And #CreateODBCDateTime(attr.end_date1)#
            <cfelseif attr.end_date0 neq "">
             And end_date <= #CreateODBCDateTime(attr.end_date0)#
            <cfelseif attr.end_date1 neq "">
             And end_date <= #CreateODBCDateTime(attr.end_date1)#     
            <cfif attr.division neq "" and trim(attr.division) neq "All">
             And lower(trim(division)) = lower('#trim(attr.division)#')
            <cfif attr.hub neq "" and trim(attr.hub) neq "All">
             And trim(hub) = '#trim(attr.hub)#'
            <cfif attr.ack neq "" and attr.ack eq "Y">
             And ACKNOWLEDGE_EID is not null
            <cfelseif attr.ack neq "" and attr.ack eq "N">
             And ACKNOWLEDGE_EID is null

        • 1. Re: Slow Query Question/
          ilssac Level 5

          I don't know, what query actually runs?  There is a lot if logic in that code that will dynamically build a wide range of queries based on various other data.  Do all these variations take such a long time or only some of them?  What does your database tools say takes such a long time?  Are you getting any unexpected cartesian joins in all the jumble of code?


          What happend if you simplify the query to a single element?  Then if that works well, slowely add the extra logic one piece at a time.

          • 2. Re: Slow Query Question/
            emmim44 Level 1

            When I use the problem_code in (1) on CF side it takes more than a minute to pull 3782 records however using same thing in oracle db takes 433


            • 3. Re: Slow Query Question/
              Dan Bracuk Level 5

              Two things that I noticed which will slow down your query are:


              1.  Using functions in the where clause.  Specifically

              And lower(TICKET_NUMBER) like lower('#attr.tick#%')


              2.  Not sure why you have a left join to your subquery instead of an inner join.


              You also might not be joining to your subquery on enough fields.  It would be easier to tell if you did all the joins at the start of your where clause instead of scattered thoughout.