Slow Query Question/

Report · Dec 28, 2009

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
     where
      1 = 1
     <cfif attr.problem neq "" or attr.node neq "" or attr.lob neq "">
      And a.event_number in
      (
        select distinct e.event_number
        from
         ops$new.network_event e
        , ops$new.ne_node n
        , ops$new.ne_problem p
        , ops$new.ne_lob l
        where
         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>
         <cfif attr.problem neq "">
          And p.problem_code in (#attr.problem#)
         </cfif>
         <cfif attr.lob neq "">
          And l.lob in (#ListQualify(attr.lob, "'")#)
         </cfif>
      )
     </cfif>
      And a.event_number = c.event_number(+)
      <cfif attr.tick neq "">
       And lower(TICKET_NUMBER) like lower('#attr.tick#%')
      </cfif>
      <cfif attr.status neq "">
       And status_code = #attr.status#
      </cfif>
      
      <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>

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

      <cfif attr.division neq "" and trim(attr.division) neq "All">
       And lower(trim(division)) = lower('#trim(attr.division)#')
      </cfif>
      <cfif attr.hub neq "" and trim(attr.hub) neq "All">
       And trim(hub) = '#trim(attr.hub)#'
      </cfif>
      <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
      </cfif>

      </cfquery>

Report · Dec 28, 2009

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.

Report · Dec 28, 2009

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

milisecs..

Report · Dec 28, 2009

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.

Adobe Community

Slow Query Question/