How to get a query to run faster

I have a query that takes a while to run and sometimes times out. Is there a better way to write the query below to run faster?
SELECT a.confirm_date, a.so_br_id, a.so_id, a.so_line_nbr, a.confirm_qty, a.orig_assign_qty,
           b.qty_backordered, b.qty_ordered, b.qty_shipped, a.bo_reason_code, a.part_id, c.cust_id, b.vendor_code 
  FROM CSI.SO_CARTON_DETAIL a, csi.or_line b, csi.or_header c
    WHERE a.CONFIRM_DATE >= @variable ('Start Date')
   and a.CONFIRM_DATE <= @variable ('End Date')
  and a.CONFIRM_QTY <> a.ORIG_ASSIGN_QTY
  and a.so_br_id = b.so_br_id
  and a.so_id = b.so_id
  and a.so_line_nbr = b.so_line_nbr
  and a.so_br_id = c.so_br_id
  and a.so_id = c.so_id
  and b.qty_backordered > 0 
ORDER BY a.confirm_date, c.cust_id, a.so_br_id, a.so_id

Open in new window

Jbancr1Asked:
Who is Participating?
 
dbmullenConnect With a Mentor Commented:
since you're using business-objects, that tells me you have no abiliity to change the query.

1)  make sure stats are correct on the tables
2)  make sure the query is using indexes

if that doesn't help.
re-write the query and create a view.


SELECT   a.confirm_date, a.so_br_id, a.so_id, a.so_line_nbr, a.confirm_qty,
         a.orig_assign_qty, b.qty_backordered, b.qty_ordered, b.qty_shipped,
         a.bo_reason_code, a.part_id,
         (SELECT c.cust_id
            FROM csi.or_header c
           WHERE a.so_br_id = c.so_br_id AND a.so_id = c.so_id) cust_id,
         b.vendor_code
    FROM csi.so_carton_detail a, csi.or_line b
   WHERE a.confirm_date >= :start_date
     AND a.confirm_date <= :end_date
     AND a.confirm_qty <> a.orig_assign_qty
     AND a.so_br_id = b.so_br_id
     AND a.so_id = b.so_id
     AND a.so_line_nbr = b.so_line_nbr
     AND b.qty_backordered > 0
ORDER BY a.confirm_date, 12, a.so_br_id, a.so_id

Open in new window

0
 
RgGray3Connect With a Mentor Commented:
Are the fields you are using for the where clause indexed??

0
 
RgGray3Connect With a Mentor Commented:
Especially ...

Confirm_Date and all _id or _nbr fields
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Jbancr1Author Commented:
RgGray3,
To tell you the truth my knowledge hasn't reached the point of indexing but I do know it helps with the speed which is about what I know for indexing.
0
 
Jbancr1Author Commented:
dbmullen,

I have ability to change the query, I ran your query above and got a error stating 'ORA-01008  Not all variables bound :- 1008'
0
 
dbmullenConnect With a Mentor Commented:
sorry..  I changed these
   WHERE a.confirm_date >= :start_date
     AND a.confirm_date <= :end_date

change them back to
    WHERE a.CONFIRM_DATE >= @variable ('Start Date')
   and a.CONFIRM_DATE <= @variable ('End Date')
 

0
 
RgGray3Connect With a Mentor Commented:
Indexing all fields that are used to search/filter or sort will improve performance...

take the time to learn how to do it in your database....  

nothing looked out of the ordinary with the query....  straight forward and simple.

You are not asking it to do anything that I see as a big problem

If the database is properly indexed, the db engine should be able to optomize the query properly.

Without proper indexes...  the db engine must scan the table rather than the indexes and that takes time

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.