Query performance
User compared two query approaches: SQL Type 1 with 300 hardcoded invoice references in an IN clause vs. Type 2 using a subquery against a temporary table. Type 1 estimated cost was 2372 with 300 separate index lookups; Type 2 estimated cost was only 405. Type 2's subquery approach proved significantly more efficient, avoiding 300 individual index filter operations in favor of a single temporary table scan.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm reviewing the SQL's that our applications are running against the database
(11.70 Running on AIX) and was wondering which of the following two SQL
statements is more efficient or less demanding on the Informix Engine.
SQL TYPE 1:
SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS
product_code, oldesc AS product_description, '' AS stock_flag,
pmserialreq AS serial_required, pmenable_tracking AS
tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
sundry_product_description, olprbasis AS selling_unit_type,
olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
AS refrigeration_kit_line_number, olstock_brcode AS
branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
encrypted_system_value, olpriceovr AS price_override_type,
olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
olovrdscrate AS trade_discount_percentage, oltaxrate AS
tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
AS settlement_discount_percentage, oldate AS line_date, oltime AS
line_time, oldoctype AS doc_type, oldocref AS doc_ref,
olprice_origin AS price_origin, olsapmsg AS sap_message
FROM olines JOIN product ON olprodno = pmprodno
WHERE olintref IN(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
or
SQL Type 2:
SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS
product_code, oldesc AS product_description, '' AS stock_flag,
pmserialreq AS serial_required, pmenable_tracking AS
tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
sundry_product_description, olprbasis AS selling_unit_type,
olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
AS refrigeration_kit_line_number, olstock_brcode AS
branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
encrypted_system_value, olpriceovr AS price_override_type,
olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
olovrdscrate AS trade_discount_percentage, oltaxrate AS
tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
AS settlement_discount_percentage, oldate AS line_date, oltime AS
line_time, oldoctype AS doc_type, oldocref AS doc_ref,
olprice_origin AS price_origin, olsapmsg AS sap_message
FROM olines JOIN product ON olprodno = pmprodno
WHERE olintref IN( select intref from t_int)
In Select Type 1 the 300 reference numbers are built into teh SQL.
In Type 2 they're inserted into a temporary table (t_int) and the temporary
table is used in the sub-query.
The SET EXPLAIN Estimayted costs are:
Type 1:
Estimated Cost: 2372
Estimated # of Rows Returned: 1553
1) reece.olines: INDEX PATH
(1) Index Name: reece.ixol11k
Index Keys: olintref ollineno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olintref = 1179364
(2) Index Name: reece.ixol11k
Index Keys: olintref ollineno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olintref = 1179462
.......
(299) Index Name: reece.ixol11k
Index Keys: olintref ollineno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olintref = 3070619
(300) Index Name: reece.ixol11k
Index Keys: olintref ollineno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olintref = 3070620
2) reece.product: INDEX PATH
(1) Index Name: reece.ixppm1k
Index Keys: pmprodno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 olines
t2 product
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1494 1553 1494 00:00.11 789
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 1494 276423 1494 00:00.03 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1494 1554 00:00.15 2373
Type 2:
Estimated Cost: 405
Estimated # of Rows Returned: 256
1) reece.olines: INDEX PATH
(1) Index Name: reece.ixol11k
Index Keys: olintref ollineno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olintref = ANY <subquery>
2) reece.product: INDEX PATH
(1) Index Name: reece.ixppm1k
Index Keys: pmprodno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 12
Estimated # of Rows Returned: 300
1) informix.t_int: SEQUENTIAL SCAN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 olines
t2 product
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1494 256 1494 00:00.02 144
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 1494 276423 1494 00:00.05 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1494 256 00:00.07 405
@@NL@
What is the query plan if you
- after populating the temporary table add an index on the join column(s)
- run update statistics high on the join column(s) in the temporary table
- convert SQL TYPE 2 to a join to the temporary table
Regards,
David.
> On 11 February 2015 at 02:24 JEFF POUTON <jeff.poulton@reece.com.au> wrote:
>
>
> I'm reviewing the SQL's that our applications are running against the
database
> (11.70 Running on AIX) and was wondering which of the following two SQL
> statements is more efficient or less demanding on the Informix Engine.
>
> SQL TYPE 1:
> SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS>
> product_code, oldesc AS product_description, '' AS stock_flag,
>
> pmserialreq AS serial_required, pmenable_tracking AS
>
> tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
>
> sundry_product_description, olprbasis AS selling_unit_type,
>
> olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
>
> AS refrigeration_kit_line_number, olstock_brcode AS
>
> branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
>
> backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
>
> unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
>
> olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
>
> encrypted_system_value, olpriceovr AS price_override_type,
>
> olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
>
> cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
>
> charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
>
> olovrdscrate AS trade_discount_percentage, oltaxrate AS
>
> tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
>
> AS settlement_discount_percentage, oldate AS line_date, oltime AS
>
> line_time, oldoctype AS doc_type, oldocref AS doc_ref,
>
> olprice_origin AS price_origin, olsapmsg AS sap_message
>
> FROM olines JOIN product ON olprodno = pmprodno
>
> WHERE olintref IN(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
>
> or
>
> SQL Type 2:
>
> SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS>
> product_code, oldesc AS product_description, '' AS stock_flag,
>
> pmserialreq AS serial_required, pmenable_tracking AS
>
> tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
>
> sundry_product_description, olprbasis AS selling_unit_type,
>
> olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
>
> AS refrigeration_kit_line_number, olstock_brcode AS
>
> branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
>
> backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
>
> unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
>
> olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
>
> encrypted_system_value, olpriceovr AS price_override_type,
>
> olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
>
> cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
>
> charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
>
> olovrdscrate AS trade_discount_percentage, oltaxrate AS
>
> tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
>
> AS settlement_discount_percentage, oldate AS line_date, oltime AS
>
> line_time, oldoctype AS doc_type, oldocref AS doc_ref,
>
> olprice_origin AS price_origin, olsapmsg AS sap_message
>
> FROM olines JOIN product ON olprodno = pmprodno
>
> WHERE olintref IN( select intref from t_int)
>
> In Select Type 1 the 300 reference numbers are built into teh SQL.
> In Type 2 they're inserted into a temporary table (t_int) and the temporary
> table is used in the sub-query.
>
> The SET EXPLAIN Estimayted costs are:
>
> Type 1:
> Estimated Cost: 2372
> Estimated # of Rows Returned: 1553
>
> 1) reece.olines: INDEX PATH
>
> (1) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = 1179364
>
> (2) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = 1179462
> ........
>
> (299) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = 3070619
>
> (300) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = 3070620
>
> 2) reece.product: INDEX PATH
>
> (1) Index Name: reece.ixppm1k
>
> Index Keys: pmprodno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
> NESTED LOOP JOIN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 olines
> t2 product
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 1494 1553 1494 00:00.11 789
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 1494 276423 1494 00:00.03 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 1494 1554 00:00.15 2373
>
> Type 2:
>
> Estimated Cost: 405
> Estimated # of Rows Returned: 256
>
> 1) reece.olines: INDEX PATH
>
> (1) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = ANY <subquery>
>
> 2) reece.product: INDEX PATH
>
> (1) Index Name: reece.ixppm1k
>
> Index Keys: pmprodno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
> NESTED LOOP JOIN
>
> Subquery:
>
> ---------
>
> Estimated Cost: 12
>
> Estimated # of Rows Returned: 300
>
>
Well, looking at the two query plans, the second one has a lower cost,
returns the same number of rows, and tuns in half the time. It is kind of
obvious which id better.
That said, i would suggest also testing a straight join to the temp table.
This MIGHT be the best alternative. You don't know unless you try it.
Art
On Feb 10, 2015 9:25 PM, "JEFF POUTON" <jeff.poulton@reece.com.au> wrote:
> I'm reviewing the SQL's that our applications are running against the
> database
> (11.70 Running on AIX) and was wondering which of the following two SQL
> statements is more efficient or less demanding on the Informix Engine.
>
> SQL TYPE 1:
> SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS>
> product_code, oldesc AS product_description, '' AS stock_flag,
>
> pmserialreq AS serial_required, pmenable_tracking AS
>
> tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
>
> sundry_product_description, olprbasis AS selling_unit_type,
>
> olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
>
> AS refrigeration_kit_line_number, olstock_brcode AS
>
> branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
>
> backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
>
> unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
>
> olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
>
> encrypted_system_value, olpriceovr AS price_override_type,
>
> olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
>
> cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
>
> charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
>
> olovrdscrate AS trade_discount_percentage, oltaxrate AS
>
> tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
>
> AS settlement_discount_percentage, oldate AS line_date, oltime AS
>
> line_time, oldoctype AS doc_type, oldocref AS doc_ref,
>
> olprice_origin AS price_origin, olsapmsg AS sap_message
>
> FROM olines JOIN product ON olprodno = pmprodno
>
> WHERE olintref IN(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
>
> or
>
> SQL Type 2:
>
> SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS>
> product_code, oldesc AS product_description, '' AS stock_flag,
>
> pmserialreq AS serial_required, pmenable_tracking AS
>
> tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
>
> sundry_product_description, olprbasis AS selling_unit_type,
>
> olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
>
> AS refrigeration_kit_line_number, olstock_brcode AS
>
> branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
>
> backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
>
> unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
>
> olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
>
> encrypted_system_value, olpriceovr AS price_override_type,
>
> olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
>
> cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
>
> charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
>
> olovrdscrate AS trade_discount_percentage, oltaxrate AS
>
> tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
>
> AS settlement_discount_percentage, oldate AS line_date, oltime AS
>
> line_time, oldoctype AS doc_type, oldocref AS doc_ref,
>
> olprice_origin AS price_origin, olsapmsg AS sap_message
>
> FROM olines JOIN product ON olprodno = pmprodno
>
> WHERE olintref IN( select intref from t_int)
>
> In Select Type 1 the 300 reference numbers are built into teh SQL.
> In Type 2 they're inserted into a temporary table (t_int) and the temporary
> table is used in the sub-query.
>
> The SET EXPLAIN Estimayted costs are:
>
> Type 1:
> Estimated Cost: 2372
> Estimated # of Rows Returned: 1553
>
> 1) reece.olines: INDEX PATH
>
> (1) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = 1179364
>
> (2) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = 1179462
> ........
>
> (299) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = 3070619
>
> (300) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = 3070620
>
> 2) reece.product: INDEX PATH
>
> (1) Index Name: reece.ixppm1k
>
> Index Keys: pmprodno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
> NESTED LOOP JOIN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 olines
> t2 product
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 1494 1553 1494 00:00.11 789
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 1494 276423 1494 00:00.03 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 1494 1554 00:00.15 2373
>
> Type 2:
>
> Estimated Cost: 405
> Estimated # of Rows Returned: 256
>
> 1) reece.olines: INDEX PATH
>
> (1) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = ANY <subquery>
>
> 2) reece.product: INDEX PATH
>
> (1) Index Name: reece.ixppm1k
>
> Index Keys: pmprodno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
> NESTED LOOP JOIN
>
> Subquery:
>
> ---------
>
> Estimated
David and Art
Thank you for your suggestions. I tried the combination of adding and index on
the temporary table and running update statistics high on the column and
joining rather than using a sub-query. It did not seem to make it less
demanding.
So it seems to me the sub-query (Query 1 below) is the least demanding. The
indexed join (Query 2 below) and the in large list (Query3) have a mush higher
estimated cost.
Is this a valid interpretation of 'Estimated Cost'. So I'm thinking I'll talk
to developers about trying to use Query 1 method.
Thanks again
Query 1 - No index on temp table and using subquery :
QUERY: (OPTIMIZATION TIMESTAMP: 02-12-2015 08:37:22)
------
SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS
product_code, oldesc AS product_description, '' AS stock_flag,
pmserialreq AS serial_required, pmenable_tracking AS
tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
sundry_product_description, olprbasis AS selling_unit_type,
olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
AS refrigeration_kit_line_number, olstock_brcode AS
branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
encrypted_system_value, olpriceovr AS price_override_type,
olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
olovrdscrate AS trade_discount_percentage, oltaxrate AS
tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
AS settlement_discount_percentage, oldate AS line_date, oltime AS
line_time, oldoctype AS doc_type, oldocref AS doc_ref,
olprice_origin AS price_origin, olsapmsg AS sap_message
FROM olines JOIN product ON olprodno = pmprodno
--JOIN t_int2 ON olintref = intref;
WHERE olintref IN( select intref from t_int2)
Estimated Cost: 405
Estimated # of Rows Returned: 256
1) reece.olines: INDEX PATH
(1) Index Name: reece.ixol11k
Index Keys: olintref ollineno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olintref = ANY <subquery>
2) reece.product: INDEX PATH
(1) Index Name: reece.ixppm1k
Index Keys: pmprodno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 12
Estimated # of Rows Returned: 300
1) informix.t_int2: SEQUENTIAL SCAN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 olines
t2 product
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1494 256 1494 00:00.02 144
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 1494 276423 1494 00:00.03 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1494 256 00:00.05 405
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t_int2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 300 300 300 00:00.00 12
type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 300 0 300 00:00.00
Query 2 - Index on temp table and running update statistics and using JOIN
QUERY: (OPTIMIZATION TIMESTAMP: 02-12-2015 08:37:25)
------
SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS
product_code, oldesc AS product_description, '' AS stock_flag,
pmserialreq AS serial_required, pmenable_tracking AS
tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
sundry_product_description, olprbasis AS selling_unit_type,
olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
AS refrigeration_kit_line_number, olstock_brcode AS
branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
encrypted_system_value, olpriceovr AS price_override_type,
olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
olovrdscrate AS trade_discount_percentage, oltaxrate AS
tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
AS settlement_discount_percentage, oldate AS line_date, oltime AS
line_time, oldoctype AS doc_type, oldocref AS doc_ref,
olprice_origin AS price_origin, olsapmsg AS sap_message
FROM olines JOIN product ON olprodno = pmprodno
JOIN t_int3 ON olintref = intref
Estimated Cost: 2378
Estimated # of Rows Returned: 1558
1) informix.t_int3: SEQUENTIAL SCAN
2) reece.olines: INDEX PATH
(1) Index Name: reece.ixol11k
Index Keys: olintref ollineno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olintref = informix.t_int3.intref
NESTED LOOP JOIN
3) reece.product: INDEX PATH
(1) Index Name: reece.ixppm1k
Index Keys: pmprodno (Serial, fragments: ALL)
Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t_int3
t2 olines
t3 product
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 300 300 300 00:00.00 12
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 1494 157374 1494 00:00.03 3
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1494 1558 00:00.04 779
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 1494 276423 1494 00:00.03 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1494 1559 00:00.07 2379
QUERY 3 Using IN Large list.
QUERY: (OPTIMIZATION TIMESTAMP: 02-12-2015 08:37:26)
------
SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS
product_code, oldesc AS product_des
Check syssesprof before and after the query runs, which query plans does the
least reads/writes to the database?
Regard,
David.
> On 11 February 2015 at 22:00 JEFF POUTON <jeff.poulton@reece.com.au> wrote:
>
>
> David and Art
>
> Thank you for your suggestions. I tried the combination of adding and index
on
> the temporary table and running update statistics high on the column and
> joining rather than using a sub-query. It did not seem to make it less
> demanding.
>
> So it seems to me the sub-query (Query 1 below) is the least demanding. The
> indexed join (Query 2 below) and the in large list (Query3) have a mush
higher
> estimated cost.
>
> Is this a valid interpretation of 'Estimated Cost'. So I'm thinking I'll talk
> to developers about trying to use Query 1 method.
>
> Thanks again
>
> Query 1 - No index on temp table and using subquery :
>
> QUERY: (OPTIMIZATION TIMESTAMP: 02-12-2015 08:37:22)
> ------
> SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS>
> product_code, oldesc AS product_description, '' AS stock_flag,
>
> pmserialreq AS serial_required, pmenable_tracking AS
>
> tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
>
> sundry_product_description, olprbasis AS selling_unit_type,
>
> olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
>
> AS refrigeration_kit_line_number, olstock_brcode AS
>
> branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
>
> backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
>
> unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
>
> olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
>
> encrypted_system_value, olpriceovr AS price_override_type,
>
> olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
>
> cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
>
> charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
>
> olovrdscrate AS trade_discount_percentage, oltaxrate AS
>
> tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
>
> AS settlement_discount_percentage, oldate AS line_date, oltime AS
>
> line_time, oldoctype AS doc_type, oldocref AS doc_ref,
>
> olprice_origin AS price_origin, olsapmsg AS sap_message
>
> FROM olines JOIN product ON olprodno = pmprodno
>
> --JOIN t_int2 ON olintref = intref;
>
> WHERE olintref IN( select intref from t_int2)
>
> Estimated Cost: 405
> Estimated # of Rows Returned: 256
>
> 1) reece.olines: INDEX PATH
>
> (1) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = ANY <subquery>
>
> 2) reece.product: INDEX PATH
>
> (1) Index Name: reece.ixppm1k
>
> Index Keys: pmprodno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
> NESTED LOOP JOIN
>
> Subquery:
>
> ---------
>
> Estimated Cost: 12
>
> Estimated # of Rows Returned: 300
>
> 1) informix.t_int2: SEQUENTIAL SCAN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 olines
> t2 product
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 1494 256 1494 00:00.02 144
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 1494 276423 1494 00:00.03 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 1494 256 00:00.05 405
>
> Subquery statistics:
> --------------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 t_int2
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 300 300 300 00:00.00 12
>
> type rows_sort est_rows rows_cons time
> -------------------------------------------------
> sort 300 0 300 00:00.00
>
> Query 2 - Index on temp table and running update statistics and using JOIN
>
> QUERY: (OPTIMIZATION TIMESTAMP: 02-12-2015 08:37:25)
> ------
> SELECT olintref AS invoice_number, ollineno AS line_number, olprodno AS>
> product_code, oldesc AS product_description, '' AS stock_flag,
>
> pmserialreq AS serial_required, pmenable_tracking AS
>
> tracking_enabled, pmrefrigerant AS refrigerant_product, '' AS
>
> sundry_product_description, olprbasis AS selling_unit_type,
>
> olkittype AS kit_type, olkitline AS kit_line_number, olxkit_groupno
>
> AS refrigeration_kit_line_number, olstock_brcode AS
>
> branch_supplying_stock, olordqty AS ordered_quantity, olborqty AS
>
> backordered_quantity, olrepqty AS replenish_quantity, olunitcost AS
>
> unit_cost, olrebcost AS rebated_cost, olinvcost AS invoice_cost,
>
> olcostorg AS cost_origin, olrebcode AS rebate_code, olsyslec AS
>
> encrypted_system_value, olpriceovr AS price_override_type,
>
> olovrprice AS selling_price, olnetflag AS net_flag, olucostchk AS
>
> cost_override_flag, olovercharge AS overcharge_flag, olchargeflag AS
>
> charge_flag, olsuppno AS supplier_number, olratio AS pack_ratio,
>
> olovrdscrate AS trade_discount_percentage, oltaxrate AS
>
> tax_percentage, olhandrate AS handling_fee_percentage, olsetdscrate
>
> AS settlement_discount_percentage, oldate AS line_date, oltime AS
>
> line_time, oldoctype AS doc_type, oldocref AS doc_ref,
>
> olprice_origin AS price_origin, olsapmsg AS sap_message
>
> FROM olines JOIN product ON olprodno = pmprodno
>
> JOIN t_int3 ON olintref = intref
>
> Estimated Cost: 2378
> Estimated # of Rows Returned: 1558
>
> 1) informix.t_int3: SEQUENTIAL SCAN
>
> 2) reece.olines: INDEX PATH
>
> (1) Index Name: reece.ixol11k
>
> Index Keys: olintref ollineno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olintref = informix.t_int3.intref
> NESTED LOOP JOIN
>
> 3) reece.product: INDEX PATH
>
> (1) Index Name: reece.ixppm1k
>
> Index Keys: pmprodno (Serial, fragments: ALL)
>
> Lower Index Filter: reece.olines.olprodno = reece.product.pmprodno
> NESTED LOOP JOIN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 t_int3
> t2 olines
> t3 product
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 300 300 300 00:00.00 12
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 1494 157374 1494 00:00.03 3
>
> type rows_prod est_rows time est_cost
> -----------
Looking at syssesprof for my sessions Query 1 isreads 3312, iswrites 600 Query 2 isreads 3019, iswrites 300 Query 3 isreads 3009, iswrites 0 So in this case Query 3. Jeff