Home » SQL & PL/SQL » SQL & PL/SQL » Very Slow Response Time
Very Slow Response Time [message #188711] Mon, 21 August 2006 05:08 Go to next message
sachinsaxena
Messages: 1
Registered: August 2006
Location: New Delhi
Junior Member
Hi,

I am using below SQL in my application (Pro C/Oracle) but it takes a lot of time to fetch data.

I am on Unix 11i, Oracle 9i

If any one can give advice what is possibly wrong with this query and how it can be tuned.

	Select
        so.carriage__code,
        so_scheduled.carrier__code,
        so.commercial_orgzn__code,
        so.consignt_type__code,
        so.currency__code,
        so.customer__code,
        so.customer_ship_to__id,
        so.drop_shipment_flag,
        so.lc_type,
        so.sales_order_type__code,
        so.shiple_frame_type__code,
        so.shipment_break__code,
        so.ship_mode__code,
        so.code,
        so.so_status__code,
        so.so_sub_type__code ,
        to_char(so.issuing_date,'DD-MON-YYYY HH24:MI:SS'),
        so.payment_term__code,
        upper(so.customer_assocn__code),
        so_item.id,
        so_item.customer_part_nr__code,
        so_item.prescrptn_type__code,
        so_item.dollar_unit_price,
        so_item.commercial_product__code,
        so_sub_item.id,
        so_scheduled.partial_shipt__code,
        to_char(so_sub_item.required_date,'DD-MON-YYYY HH24:MI:SS'),
        so_sub_item.so_pending_cause__code,
        so_scheduled.recv_store__code,
        rtrim(so_sub_item.plant__code),
        so_scheduled.finished_good__code,
        to_char(so_scheduled.first_committed_date,'DD-MON-YYYY HH24:MI:SS'),
        so_scheduled.id,
        so_scheduled.item_nr,
        to_char(so_scheduled.last_committed_date,'DD-MON-YYYY HH24:MI:SS'),
        so_scheduled.last_committed_date_flag,
        so_scheduled.plant__code,
        decode( sign(so_scheduled.plant_prog_invd_qty),-1,0,so_scheduled.plant_prog_invd_qty ),
        so_scheduled.committed_qty,
        so_scheduled.store__code,
        so_scheduled.tba_flag,
        decode( sign(so_scheduled.plant_picked_qty),-1,0,so_scheduled.plant_picked_qty ),
        decode( sign(so_scheduled.reserved_qty),-1,0,so_scheduled.reserved_qty),
        so_scheduled.plant_so_status__code ,
        so.carrier__code ,
        so_sub_item.partial_shipt__code ,
        so_sub_item.carrier__code ,
        so_scheduled.recv_plant__code,
        so_scheduled.dd_flag,
        '0000'
        FROM so, so_item, so_sub_item, so_scheduled
        WHERE so.code = so_item.so__code
        AND so_item.id = so_sub_item.so_item__id
        AND so_sub_item.id = so_scheduled.so_sub_item__id
	AND (so_scheduled.tba_flag = 'N'
             	AND ( ( so_scheduled.last_committed_date_flag = 'Y'
                	AND so_scheduled.last_committed_date < sysdate + %ld --> (replaced with Global Variable Values)
                      )
                      OR
                      ( so_scheduled.last_committed_date_flag <> 'Y'
                        AND so_scheduled.first_committed_date < sysdate + %ld --> (replaced with Global Variable Values)
                      )
                    )
            )
        AND so_scheduled.plant_so_status__code not in ('01','40','41','90')
        AND so.sales_order_type__code not in('28','31','32','34','35','27')
        AND so.so_sub_type__code <> '27'
	ORDER BY
        so.customer_ship_to__id,
        so.code,
        so_item.id,
        so_sub_item.id,
        so_scheduled.id;
Re: Very Slow Response Time [message #188833 is a reply to message #188711] Mon, 21 August 2006 23:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That would be a bit hard without knowing the structure of your datamodel wouldn't it?
- what indexes do you have?
- how many rows in each table?
- define 'a lot of time'
- how many rows does it return?
- did it ever run fast?
- did you do an explain plan?

etc
Previous Topic: table creation
Next Topic: ORA-32162: Read/Write SQL method not registered
Goto Forum:
  


Current Time: Thu Dec 08 00:33:57 CST 2016

Total time taken to generate the page: 0.09206 seconds