View help..

[ClowneUser]

Hi,
I'm not sure if this is the correct forum but here goes...
I’m trying to create a shipment label, I’m getting the data from an oracle 10g database via a view which i'm writing ( see code below).

I’m designing the label in a program called bartender.

I have got the information I need, but its taking over 2 minutes to bring back the data, Can anyone give me some hint’s as to how I can speed up the query ( I am relatively new to sql, so please be gentle with me )

select distinct
sh.shipment_id,
sh.shipper_address1,
sh.shipper_address2,
sh.shipper_zip_code,
sh.shipper_city,
sh.shipper_state,
sh.deliver_to_customer_no,
sh.contract,
sh.shipper_name,
ifsapp.cust_ord_customer_address_api.Get_Company_Name2(sh.deliver_to_customer_no, sh.ship_addr_no) Customer_Name,
ifsapp.customer_order_line_api.Get_Part_No(spu.order_no, spu.line_no, spu.rel_no, spu.line_item_no) Part_no,
ifsapp.sales_part_api.Get_Catalog_Desc(sh.contract, ifsapp.customer_order_line_api.Get_Part_No(spu.order_no, spu.line_no, spu.rel_no, spu.line_item_no)) Part_Desc,
shu.customer_part_no,
sh.ship_date,
spu.handling_unit_id,
spu.lot_batch_no,
tst.qty,
tst.net_weight,
tst.net_weight + tst.HU_weight gross_weight,
tst.pallet_qty
from
shipment sh,
shipment_package_unit spu,
shipment_handling_utility shu,
(select distinct handling_unit_id, sum(qty_in_package_unit) qty, sum(net_weight) net_weight, sum(handling_unit_weight) HU_Weight, count(qty_in_package_unit) pallet_qty from shipment_handling_utility

group by handling_unit_id) tst

where
sh.shipment_id = spu.shipment_id and
spu.shipment_id = shu.shipment_id and
spu.handling_unit_id = shu.handling_unit_id and
tst.handling_unit_id = spu.handling_unit_id and
shu.handling_unit_type = 'Homogenous'

If you have any idears I would appreciate it.

Regards,
Mick.

 

[Welshbird]

Mick;

DO you know which indexes you have on the tables you are querying? This would be the first thing I would look at.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[ClowneUser]

Hi Willif,

just checked the indexes and they are as follows:-
Shipment table
shipment_id

Shipment_package_unit table
index1
Shipment_id

index2
order_no
line_no
rel_no
line_item_no

index3
handling_unit_id

index4
package_unit_id

shipment_handling_unit table
index1
shipment_id

index2
handling_unit_id

Mick.

 

[Welshbird]

That implies that you have no index on shipment.shipment_id which certainly wouldn't help..
In fact, I can't see any indices on the shipment table.

Can you confirm?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[ClowneUser]

Hi,

I must also mention, there are two types of labels i need, one is a 'package label' this goes on each container on a pallet, then next is a 'transport' label, this goes on the pallet and gives the total number, total gross weight and net weight of all containers on the pallet... it's the transport label I'm having problems with, i cant get it to sum up the total quantity and weights of each seperate pallet.. hope this clears up what im looking for

Just found out its the select in the from that slows the whole thing down, Is there another way to do this?
the problem is in the shipment_handling_unit table, this had data for both the transpot and package labels, the way i have identified the difference is 'shu.handling_unit_type = 'Homogenous''

Mick.

 

[ClowneUser]

Hi Willif,

Yes there is an index on shipment.shipment_id as below.....

just checked the indexes and they are as follows:-
Shipment table
shipment_id

Cheers

Mick.

 

[ClowneUser]

OK i think i've found the problem...

just added tst.shipment_id = spu.shipment_id to the where clause and now the query is less than half a second...

Thanks,

Mick.

 

[Welshbird]

OH - just too late - I was just trying to look at your joins and see if there was one missing!

(Presumably you now get a different result too?)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]