用Interbase优化SQL

Question

我的灵感来自我以前的question中关于SQL的好答案。现在，这个SQL运行在带有Interbase 2009的DB上。它的大小约为21 GB。

SELECT DistanceAsMeters, AddrDistance.Bold_Id, AddrDistance.Created, AddressFrom.CityName_CO as FromCity, AddressTo.CityName_CO as ToCity
FROM AddrDistance
LEFT JOIN Address AddressFrom ON AddrDistance.FromAddress = AddressFrom.Bold_Id
LEFT JOIN Address AddressTo ON AddrDistance.ToAddress = AddressTo.Bold_Id
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
       and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask)
Order By Created Desc

有840000行有AddrDistance，190000行有地址，4行有DistanceQueryTask。

问题是，能否更快地做到这一点？我想，相同的查询多次运行，从DistanceQueryTask中选择bold_id。请注意，我对存储过程不感兴趣，只是普通的SQL :)

EDIT1这里是当前的执行计划：

Statement: SELECT DistanceAsMeters, AddrDistance.Bold_Id, AddrDistance.Created, AddressFrom.CityName_CO as FromCity, AddressTo.CityName_CO as ToCity
FROM AddrDistance
LEFT JOIN Address AddressFrom ON AddrDistance.FromAddress = AddressFrom.Bold_Id
LEFT JOIN Address AddressTo ON AddrDistance.ToAddress = AddressTo.Bold_Id
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
       and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask)
Order By Created Desc

PLAN (DISTANCEQUERYTASK INDEX (RDB$PRIMARY218))
PLAN SORT (JOIN (JOIN (ADDRDISTANCE NATURAL,ADDRESSFROM INDEX (RDB$PRIMARY234)),ADDRESSTO INDEX (RDB$PRIMARY234)))

是的，如果数据库中的行数很低，DistanceQueryTask就意味着要有一个较低的数目。

Answer 1

使用左联接和子查询将减缓任何查询的速度。

使用正确的索引(在Bold_id、DistanceMeters、PseudoDistanceAsCostKm上)可以得到一些改进--记住，更多的索引会增加数据库的大小

Answer 2

我认为bold_id是您的密钥，因此正确地进行了索引。

然后，将子subselect和not...in替换为join可能有助于优化器。

SELECT DistanceAsMeters, Bold_Id, Created, AddressFrom.CityName_CO as FromCity, AddressTo.CityName_CO as ToCity
FROM AddrDistance
LEFT JOIN Address AddressFrom ON AddrDistance.FromAddress = AddressFrom.Bold_Id
LEFT JOIN Address AddressTo ON AddrDistance.ToAddress = AddressTo.Bold_Id
LEFT JOIN DistanceQueryTask ON AddrDistance.bold_id = DistanceQueryTask.bold_id
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
  and DistanceQueryTask.bold_id is null
Order By Created Desc

Answer 3

为这个部分创建一个索引：(DistanceAsMeters =0和PseudoDistanceAsCostKm = 0)，因为它为它做了(坏的)表扫描: ADDRDISTANCE --自然的

并尝试使用连接，而不是如Francois所述的子选择。