如何将SQL从旧的Informix样式转换为ANSI样式? [英] How to convert SQL from old Informix-style to ANSI-style?

问题描述

我有一些用Informix风格的SQL编写的查询.具体来说，此查询选择客户订单中的项目. (尽管我保留了有问题的部分，但我在某种程度上简化了表的结构.)

I have some queries written in Informix-style SQL. Specifically, this query selects the items in a customer's order. (I've simplified the table structure somewhat, though I kept the part that is problematic.)

SELECT ordi.line_no, ordi.item_code, ordi.desc, ordi.price,
    shpi.location, shpi.status, shpi.ship_code,
    box.box_no, box.tracking_no, shpc.ship_co, mfr.mfr_name,
    sum(shpi.ship_qty), sum(shpi.net_cost)
FROM order_items ordi, ship_items shpi, OUTER ship_boxes box,
    shipping_companies shpc, OUTER (inventory invt, brand, manufacturer mfr)
WHERE ordi.order_id = ?
    AND shpi.order_id = ordi.order_id AND shpi.line_no = ordi.line_no
    AND box.order_id = ordi.order_id AND box.box_no = shpi.box_no
    AND shp.shipper_code = shpi.shipper_code
    AND invt.item_code = ordi.item_code
        AND brand.brand_no = invt.brand_no
        AND mfr.mfr_code = brand.mfr_code
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
ORDER BY ordi.line_no ASC;

(由于OUTER加入库存的原因是某类物品存储在不同的库存表中.ship_boxes上的OUTER用于尚未包装的物品.)

(The reason inventory is joined by OUTER is because a certain class of items is stored in a different inventory table. The OUTER on ship_boxes is for items that were not packed yet.)

我用标准的ANSI风格的JOIN重写了它.这是我得到的:

I'm rewrote it with standard, ANSI-style JOIN's. Here is what I got:

SELECT ordi.line_no, ordi.item_code, ordi.desc, ordi.price, shpi.location,
    shpi.status, shpi.ship_code, box.box_no, box.tracking_no, shpc.ship_co,
    mfr.mfr_name, sum(shpi.ship_qty), sum(shpi.net_cost)
FROM order_items ordi
    JOIN ship_items shpi ON shpi.order_id = ordi.order_id
        AND shpi.line_no = ordi.line_no
    LEFT JOIN ship_boxes box ON box.order_id = ordi.order_id
        AND box.box_no = shpi.box_no
    JOIN shipping_companies shpc ON shpc.shipper_code = shpi.shipper_code
    LEFT JOIN (inventory invt
        JOIN brand ON brand.brand_no = invt.brand_no
        JOIN manufacturer mfr ON mfr.mfr_code = brand.mfr_code
        ) ON invt.item_code = ordi.item_code
WHERE ordi.order_id = ?
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
ORDER BY ordi.line_no ASC;

结果集完全相同，但是性能影响接近2个数量级.对于包含50个项目的订单，第一个查询大约需要50毫秒，而第二个查询大约需要5秒.运行一个Explain会使第一个查询的成本为25，第二个查询的成本为14403.我能够将差异归结为复杂的库存连接:Informix风格的查询以3个INDEX PATH/NESTED LOOP JOIN的价格执行的，每个成本为1； ANSI JOIN是作为SEQUENTIAL SCAN执行的，当时的成本为383，加起来超过14000点.

The result set is exactly the same, but the performance hit is nearly 2 orders of magnitude. For an order with 50 items, the first query takes about 50 milliseconds, while the second takes about 5 seconds. Running an Explain gives a cost of 25 to the first query, and a cost of 14403 to the second. I was able to pin down the difference to the complex join of inventory: the Informix-style query performed it as 3 INDEX PATH / NESTED LOOP JOIN's, each having cost of 1; the ANSI JOIN's were performed as a SEQUENTIAL SCAN, with cost of 383 at that point, adding up to over 14K points.

似乎ANSI JOIN在整个库存/品牌/制造商表中起作用，然后对订单项进行LEFT JOIN运算. Informix OUTER (...)能够处理我要的那个表的少量选择(订单中的项目).

It seems that the ANSI JOIN's work on the entire inventory / brand / manufacturer table, which is then LEFT JOIN'ed to the order items. The Informix OUTER (...) is able to work on the small selection of that table that I asked for (the items in the order).

我做错了什么?有没有一种方法可以编写不会给我带来性能影响的ANSI风格的查询?如果需要的话，我将回到Informix风格的JOIN，但是我真的希望有另一种方法.

What am I doing wrong? Is there a way to write the query ANSI-style that won't give me that performance hit? If I must, I'll go back to the Informix-style JOIN's, but I am really hoping there is another way.

谢谢.

以下是SET EXPLAIN的结果:

1. 原始查询:估计费用:18
2. 我的重写(明确的JOIN):估计费用:15629
3. @HartCO的建议(无包装的存货部分):估计成本:18(但是数据是否相同?为什么不像OUTER inventory, brand, manufacturer那样?)

1. Original query: Estimated Cost: 18
2. My rewrite (explicit JOIN's): Estimated Cost: 15629
3. @HartCO's suggestion (unbundle inventory section): Estimated Cost: 18 (but will the data be the same? Why isn't that like OUTER inventory, brand, manufacturer?)

推荐答案

您需要取消捆绑Inventory连接部分并将其更改为LEFT JOIN:

You need to unbundle your Inventory join section and change those to LEFT JOIN:

SELECT ordi.line_no     , ordi.item_code     , ordi.DESC        , ordi.price
     , shpi.location    , shpi.STATUS        , shpi.ship_code   , box.box_no
     , box.tracking_no  , shpc.ship_co       , mfr.mfr_name     
     , sum(shpi.ship_qty)
     , sum(shpi.net_cost)
FROM order_items ordi
    JOIN ship_items shpi ON  shpi.order_id = ordi.order_id   
        AND shpi.line_no = ordi.line_no
    LEFT JOIN ship_boxes box ON box.order_id = ordi.order_id
        AND box.box_no = shpi.box_no
    LEFT JOIN shipping_companies shpc ON shpc.shipper_code = box.shipper_code
    LEFT JOIN inventory invt ON invt.item_code = ordi.item_code
    LEFT JOIN brand ON brand.brand_no = invt.brand_no
    LEFT JOIN manufacturer mfr ON mfr.mfr_code = brand.mfr_code    
WHERE ordi.order_id = ?
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
ORDER BY ordi.line_no ASC;

注意:我只有一个要测试的SQL Server实例，但是我看到执行计划有很大的不同，我的查询显示一个Nested Loops (Left Outer Join)会被执行一次，而您的查询显示Nested Loops (Inner Join)会被执行3次.当然看起来像是罪魁祸首.

Note: I only have a SQL Server instance to test on, but I see big difference in the execution plan, my query shows a Nested Loops (Left Outer Join) which gets executed once, while yours shows Nested Loops (Inner Join) that gets executed 3 times. Certainly seems like the culprit.

您的LEFT JOIN ship_boxes实际上是INNER JOIN，因为您使用JOIN shipping_companies联接到该表.如果上述查询的结果不符合要求，则应将两者都从LEFT JOIN更改为JOIN.

Your LEFT JOIN ship_boxes was effectively an INNER JOIN because you used JOIN shipping_companies to join to that table. If the results from the above query aren't as desired you should change both from LEFT JOIN to JOIN.

这篇关于如何将SQL从旧的Informix样式转换为ANSI样式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！