求救，BDE异构查询速度奇慢(100分)

TQuery.Sql如下，ErpX是MSSQL数据库BED别名，ERP是Orcale BDE别名，
TDatebase使用了Senssion。

SELECT
rc.customer_number,
rc.customer_name,
wsh.delivery_id,
dpt.actual_departure_date as dpt_date,
pld.picking_line_detail_id as pld_id,
pld.shipped_quantity as qty,
msi.segment1,
msi.description,
msi.primary_unit_of_measure as uom,
wsh.freight_carrier_code as carrier,
DlvVrfH.username,
DlvVrfH.odate
from
":ERP:OE.WSH_DELIVERIES" wsh
join ":ErpXlvVrfH" DlvVrfH ON (wsh.DELIVERY_ID = DlvVrfH.delivery_id)
join ":ERP:ar.ra_customers" rc on (wsh.customer_id = rc.customer_id)
join ":ERPe.wsh_departures" dpt on (wsh.actual_departure_id=dpt.departure_id)
join ":ERPe.so_picking_line_details" pld on (wsh.delivery_id=pld.delivery_id)
join ":ERPe.so_picking_lines_all" pl on (pld.picking_line_id=pl.picking_line_id)
join ":ERP:inv.mtl_system_items" msi on (pl.inventory_item_id=msi.inventory_item_id)
where
(wsh.organization_id=4) and
(msi.organization_id=4) and
(wsh.delivery_id=19394)
order by
10,1,7

数据量多少

粗略一看，你的查詢語句有些問題，即語句效率比較差。例如你可將你的Where條件放在Join on
的條件中，而且越早寫對速度提高越快，這與Join的語法執行方式有關
另你可使用後台的方式查詢或
在查詢時先將Query Prepare一下，對速度有所幫助

返回的数据只有三行

请大侠们修正写法

你看一下，這樣會不會快一些

SELECT
rc.customer_number, rc.customer_name, wsh.delivery_id, dpt.actual_departure_date as dpt_date,
pld.picking_line_detail_id as pld_id, pld.shipped_quantity as qty, msi.segment1, msi.description,
msi.primary_unit_of_measure as uom, wsh.freight_carrier_code as carrier,
DlvVrfH.username, DlvVrfH.odate
from
":ERP:OE.WSH_DELIVERIES" wsh
join ":ErpXlvVrfH" DlvVrfH
ON ( (wsh.organization_id=4) and (wsh.delivery_id=19394) and wsh.DELIVERY_ID = DlvVrfH.delivery_id )
join ":ERP:ar.ra_customers" rc on (wsh.customer_id = rc.customer_id)
join ":ERPe.wsh_departures" dpt on (wsh.actual_departure_id=dpt.departure_id)
join ":ERPe.so_picking_line_details" pld on (wsh.delivery_id=pld.delivery_id)
join ":ERPe.so_picking_lines_all" pl on (pld.picking_line_id=pl.picking_line_id)
join ":ERP:inv.mtl_system_items" msi on (pl.inventory_item_id=msi.inventory_item_id)
where
(msi.organization_id=4)
order by
10,1,7

几乎没变，还是很慢。

select *
from ( select rc.customer_number, rc.customer_name, wsh.delivery_id, dpt.actual_departure_date as dpt_date,
wsh.freight_carrier_code as carrier, DlvVrfH.username, DlvVrfH.odate
from
":ERP:OE.WSH_DELIVERIES" wsh ,
":ERP:ar.ra_customers" rc ,
":ErpXlvVrfH" DlvVrfH
":ERPe.wsh_departures" dpt
where wsh.organization_id=4 and wsh.delivery_id=19394 and wsh.DELIVERY_ID = DlvVrfH.delivery_id
and wsh.customer_id = rc.customer_id and wsh.actual_departure_id=dpt.departure_id ) a ,

( select pld.picking_line_detail_id as pld_id, pld.shipped_quantity as qty, msi.segment1, msi.description,
msi.primary_unit_of_measure as uom
from
":ERPe.so_picking_line_details" pld
":ERPe.so_picking_lines_all" pl
":ERP:inv.mtl_system_items" msi
where pld.delivery_id=19394 and pld.picking_line_id=pl.picking_line_id
and pl.inventory_item_id=msi.inventory_item_id and msi.organization_id=4 ) b
order by
10,1,7

超7分钟，还是不理想。会不会与其他有关？

你在後台命令分析器中執行一下，看速度如何?
2>將Order by 去掉試一下
3>分別執行以下語句的速度
<1>:
select rc.customer_number, rc.customer_name, wsh.delivery_id, dpt.actual_departure_date as dpt_date,
wsh.freight_carrier_code as carrier, DlvVrfH.username, DlvVrfH.odate
from
":ERP:OE.WSH_DELIVERIES" wsh ,
":ERP:ar.ra_customers" rc ,
":ErpXlvVrfH" DlvVrfH
":ERPe.wsh_departures" dpt
where wsh.organization_id=4 and wsh.delivery_id=19394 and wsh.DELIVERY_ID = DlvVrfH.delivery_id
and wsh.customer_id = rc.customer_id and wsh.actual_departure_id=dpt.departure_id
<2>:
select pld.picking_line_detail_id as pld_id, pld.shipped_quantity as qty, msi.segment1, msi.description,
msi.primary_unit_of_measure as uom
from
":ERPe.so_picking_line_details" pld
":ERPe.so_picking_lines_all" pl
":ERP:inv.mtl_system_items" msi
where pld.delivery_id=19394 and pld.picking_line_id=pl.picking_line_id
and pl.inventory_item_id=msi.inventory_item_id and msi.organization_id=4

4>創建一個過程引用，速度如何?
5>查看索引的建立是否合理?

with query do
begin
......
prepare;
Active:=True; / ExecSQL 看情況而定
end;