开封
乾学院 6 号会员
611 浏览  • 
     ...

4.5 维表过滤

 
SPL性能优化基础练习(38)

4.5.1 过滤后复用索引

SQL

SELECT Suppliers.Region,sum(Amount) Amount
FROM Orders
LEFT JOIN Products ON Orders.ProductID= Products.ProductID
LEFT JOIN Suppliers ON Products.SupplierID=Suppliers.SupplierID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' and Products.CategoryID not in (1,2,3)
GROUP BY Suppliers.Region

SPL

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start= days@o(st), end=days@o(et)
2 =Products.select@i([1,2,3].pos@b(CategoryID)==null)
3 =file(“Orders_Time.ctx”).open().cursor@mx(ProductID, Amount; OrderDate>=start && OrderDate<=end, ProductID:A2)
4 =A3.groups(ProductID.SupplierID.Region;sum(Amount):Amount)

A2 对维表过滤时使用 @i 选项将复用索引
A3 把和维表的关联放到产生游标的语句中，关联不上的记录不生成，减少生成记录的时间

索引复用并不会总是更快，因为要把过滤掉的记录从索引表删除，如果过滤掉的记录数很多（剩下的较少），这个动作也不会很快。而剩下记录较少时，重建索引很可能更快。具体采用哪种方式，要根据实际情况决定。

4.5.2 过滤后重建索引

SQL

SELECT Suppliers.Region,sum(Amount) Amount
FROM Orders
LEFT JOIN Products ON Orders.ProductID= Products.ProductID
LEFT JOIN Suppliers ON Products.SupplierID=Suppliers.SupplierID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' and Products.CategoryID in (1,2,3)
GROUP BY Suppliers.Region

SPL

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2 =Products.select([1,2,3].pos@b(CategoryID)!=null).derive@o().keys@i(ProductID)
3 =file(“Orders_Time.ctx”).open().cursor@mx(ProductID,Amount;OrderDate>=start && OrderDate<=end,ProductID:A2)
4 =A3.groups(ProductID.SupplierID.Region;sum(Amount):Amount)

A2 维表过滤后返回序列，用 derive@o() 变回序表，再重建索引

本例过滤掉的记录数很多（剩下的较少），重建索引很快。

4.5.3 多层维表的过滤

SQL

SELECT Suppliers.Region,sum(Amount) Amount
FROM Orders
INNER JOIN Products ON Orders.ProductID= Products.ProductID
INNER JOIN Suppliers ON Products.SupplierID=Suppliers.SupplierID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' 
    and Suppliers.SupplierID in (1,2,3) and Products.CategoryID in (1,2,3,4,5)
GROUP BY Suppliers.Region

SPL

多层维表的过滤，需要先把维表各自过滤后再建立关联关系，关联时只保留能关联上的记录。

A
1 =Suppliers.select@i([1,2,3].pos@b(SupplierID)!=null)
2 =Products.select@i([1,2,3,4,5].pos@b(CategoryID)!=null)
3 =A2.switch@i(SupplierID, A1:SupplierID)

A1 过滤供应商维表
A2 过滤产品维表
A3 将过滤后的 Products 和 Suppliers 建立关联，@i 选项表示关联不上的记录删除

统计时直接使用：

A
… / 接上面代码
4 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
5 =file(“Orders_Time.ctx”).open().cursor@mx(ProductID, Amount; OrderDate>=start && OrderDate<=end, ProductID:A3)
6 =A5.groups(ProductID.SupplierID.Region; sum(Amount):Amount)

A5 和过滤后的维表关联，关联不上的记录不再生成

4.5.4 反向过滤

SQL

SELECT sum(Amount)
FROM Orders
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31' and EmployeeID not in (
    SELECT EmployeeID 
    FROM Employees 
    WHERE Title='Sales Representative')

SPL

A
1 >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2 >Employees=file(“Employees.btx”).import@b().select(Title==“Sales Representative”).derive().keys@i(EmployeeID)
3 =file(“Orders_Time.ctx”).open().cursor@mx(Amount;OrderDate>=start && OrderDate<=end,EmployeeID:Employees:null)
4 =A3.groups(;sum(Amount))

A3 在组表游标中和维表关联，加上:null 表示仅选出关联不上的记录

如果不是组表，使用 switch 关联的话，可以用 @d 选项，即可选出关联不上的记录

SPL性能优化基础练习(38)