IDS Forum

Re: Query performance

Posted By: Art Kagel
Date: Tuesday, 10 February 2015, at 11:33 p.m.

In Response To: Query performance (JEFF POUTON)

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 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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001a11c3507c4e464a050ec87fa0

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.