IDS Forum

Query performance

Posted By: JEFF POUTON
Date: Tuesday, 10 February 2015, at 9:24 p.m.

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

Subquery statistics:
--------------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 t_int

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

Thanks in advance.

Messages In This Thread

• Query performance
  JEFF POUTON -- Tuesday, 10 February 2015, at 9:24 p.m.
  • Re: Query performance
    david@smooth1.co.uk -- Tuesday, 10 February 2015, at 11:09 p.m.
  • Re: Query performance
    Art Kagel -- Tuesday, 10 February 2015, at 11:33 p.m.
    • Re: Query performance
      JEFF POUTON -- Wednesday, 11 February 2015, at 5:00 p.m.
      • Re: Query performance
        david@smooth1.co.uk -- Wednesday, 11 February 2015, at 6:52 p.m.
        • Re: Query performance
          JEFF POUTON -- Wednesday, 11 February 2015, at 7:21 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.